What could possible go wrong if a postgres trigger will reorder rows by a "position" column?

Question

I would like to have a sequential position column partition by another column. The position should be calculated if a new row get's inserted, deleted or updated. I know it can be done in application code. However, I would like to have it in postgres. Example:

id    position   category_id
5     0          1
8     1          1
26    2          1
32    3          1
120   0          2

Related Question I looked up:

• ordering-with-postgresql-trigger-when-updating-and-inserting
• using-a-sort-order-column-in-a-database-table
• insert-record-into-table-with-position-without-updating-all-the-records-position
• eliminate-sequence-numbering-gaps-when-deleting-in-sql-server

I have already a working solution:

create table items
(
    id int primary key generated always as identity,
    category_id int NOT NULL,
    position int NOT NULL CHECK(position >= 0),
    name text not NULL CHECK (name <> ''),
    UNIQUE (category_id, position) DEFERRABLE INITIALLY IMMEDIATE
);


CREATE OR REPLACE FUNCTION items_reposition()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS '
DECLARE
    _count int;
    _current_position int;
BEGIN
  IF (TG_OP = ''DELETE'') THEN
     UPDATE items i SET position = o.position 
       FROM (SELECT ROW_NUMBER() OVER (ORDER BY position) - 1 as position, id 
             FROM items WHERE category_id = OLD.category_id ORDER BY position) o 
      WHERE i.id = o.id;
    RETURN OLD;
  END IF;
 
  
  SELECT COUNT(1)
  FROM   items
  WHERE  category_id = NEW.category_id
  INTO   _count;
   
  -- insert as last one if position is not given
  IF NEW.position IS NULL THEN
    NEW.position = _count;
  END IF;
  
  IF (OLD IS NOT NULL AND NEW.position >= _count) OR NEW.position > _count THEN
    RAISE EXCEPTION ''Tried to set row with id % to position %, but last position is %.'', NEW.id, NEW.position, _count;
  END IF;
  
  IF OLD IS NULL THEN 
    _current_position = _count;
  ELSE 
    _current_position = OLD.position;
  END IF;

  SET CONSTRAINTS ALL DEFERRED; -- so we can set the position on update
  UPDATE items
  SET position = CASE
    WHEN NEW.position < _current_position THEN position + 1 ELSE position - 1
    END
  WHERE category_id = NEW.category_id
    AND position >= LEAST(_current_position, NEW.position)::int
    AND position <= GREATEST(_current_position, NEW.position)::int 
    AND position != _current_position;

  RETURN NEW;
END;
';


CREATE TRIGGER items_position_items_reposition_trigger
    AFTER DELETE
    ON items
    FOR EACH ROW
    WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE items_reposition();

CREATE TRIGGER items_position_items_before_trigger
    BEFORE INSERT OR UPDATE OF position
    ON items 
    FOR EACH ROW
    WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE items_reposition();

db-fiddle

It looks like it works fine.

My Question is: What can possible go wrong and will be any side-effect with this trigger?