RE: What is the best way to redefine a trigger? (lock issue) - Mailing list pgsql-general

   >> 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
(waitedfor 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
functionto 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
triggerwith 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?
   >A DROP TRIGGER will try to acquire an AccessExclusiveLock on the table, which conflicts with any table level lock
(e.ga select acquires an access share lock, so it would cause the DROP TRIGGER to wait.
 
   >
   >Unfortunately I don't see this in the official docs: https://www.postgresql.org/docs/11/explicit-locking.html .
   >
   >> 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.
 
   >It is very easily reproducible. begin; select .. in one session, begin; drop trigger in a second session.

You can see in the attachment, that the lock exists without any other apparent conflicting  session.

   >Do you need to drop/create the trigger or a CREATE OR REPLACE function would suffice?

There are different use cases. Sometimes I only need to drop a trigger or modify its definition (not the function)


   >>
   >> 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
separatetransactions:
 
   >> =======================================
   >> 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"
andax."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();
   >>
   >>
   >
   >
   >-- 
   >Achilleas Mantzios
   >DBA, Analyst, IT Lead
   >IT DEPT
   >Dynacom Tankers Mgmt
   >
   >
   >
   >