What is the best way to redefine a trigger? (lock issue)

[Marc Mamin]

What is the best way to redefine a trigger? (lock issue)


Hello,
I have deployment/migration scripts that require to be idempotent.

When (re)defining or deleting triggers,  I've lately observed locked statements 
that seemed never to release (waited for a few hours).
affected version: PG 10 (and probably PG 12 ?)

My case is similar to that old description and I wonder if the recommendation 
to first change the trigger function to a no-op function still make sense.

https://stackoverflow.com/questions/24738354/how-to-drop-a-trigger-in-a-resilient-manner-in-postgresql.


In the first observed case, with a test db, I did kill all  existing 
connections to the db and tried to drop the trigger with a fresh new connection.
This again resulted in a long lasting lock and I gave up, tipping on a db 
corruption.

What does happen in the background, that can make a trigger deletion fail? 
Are there situation where row level locks instead of table level locks are 
acquired?
Coul background processeslike vacuumplay a role here?

As I've observed this problem only a very few times, I guess it is not easily 
reproducable.

attached is an picture of pg_stat_activity during such a lock,

thanks,
Marc Mamin

here an example of a such a deployment/migration script, all of these scripts 
are applied  sequentially in separate transactions:
=======================================
SET client_min_messages=error;

CREATE OR REPLACE FUNCTION block_item_cancel()
  RETURNS TRIGGER AS
$BODY$
DECLARE
        blockedItemLevel int;
        client int;

BEGIN
        WITH RECURSIVE rec as
        (
                                SELECT s.id as clientref, s."parentRef", 
a."fruitRef" 
                                FROM "ClientDO" s LEFT JOIN "Fruit2ClientDO" a 
ON (s.id=a."clientRef" and a."fruitRef" = NEW."fruitRef")
                                WHERE s.id = (select "clientRef" from "SeenDO" 
where "id" = NEW."SeenRef")
        UNION ALL
                                SELECT s2.id as clientref, s2."parentRef", 
a2."fruitRef" 
                                FROM rec 
                                JOIN "ClientDO" s2 on (s2.id=rec."parentRef")
                                LEFT JOIN LATERAL (select"fruitRef" from 
"Fruit2ClientDO" ax WHERE rec."parentRef"=ax."clientRef" and ax."fruitRef" = 
NEW."fruitRef")  a2 
                                   ON TRUE
                                WHERE rec."parentRef" IS NOT NULL
                                --Only first matching client should be used
                                AND rec."fruitRef" IS NULL
        )
        SELECT clientref
        FROM rec 
        WHERE "fruitRef" is not null
        INTO client;

        blockedItemLevel = (NEW."quantitySeened" - NEW."quantityCanceled");
        IF blockedItemLevel > 0 THEN

                UPDATE "BlockedItemAO" SET
                                "blockedItem" = blockedItemLevel,
                                "modificationDate" = now()
                WHERE "SeenPosRef" = NEW."id";
        ELSE
                DELETE FROM "BlockedItemAO" WHERE "SeenPosRef" = NEW."id";
        END IF;
        RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql
  COST 100;


DROP TRIGGER IF EXISTS block_item_cancel ON "SeenPosDO";

CREATE TRIGGER block_item_cancel
  AFTER UPDATE OF "quantityCanceled"
  ON "SeenPosDO"
  FOR EACH ROW
  WHEN ( NEW."providerRef" <> 1
         AND 
           (
              NEW."quantityCanceled" IS DISTINCT FROM OLD."quantityCanceled"    
          
           )
       )
  EXECUTE PROCEDURE block_item_cancel();