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

   >
   >-----Original Message-----
   >From: Achilleas Mantzios <achill@matrix.gatewaynet.com> 
   >Sent: Freitag, 10. Dezember 2021 11:36
   >To: pgsql-general@lists.postgresql.org
   >Subject: Re: What is the best way to redefine a trigger? (lock issue)
   >
   >On 10/12/21 12:20 μ.μ., Marc Mamin wrote:
   >>
   >>     >> 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
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
dropthe 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?
   >>     >A DROP TRIGGER will try to acquire an AccessExclusiveLock on the table, which conflicts with any table level
lock(e.g a 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.
   >It takes two or more to tango. Next time it happens query the pg_locks view, it contains info about locks on
objects.
   >The holding lock is shown as granted, the waiting lock as not granted.
   
Yes, It looks for me like a situation which should not be possible at all.
I'll try to get more informations if the problem pop up again.
Thanks.
   
   >>     >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)
   >Are you using pgbouncer or some other means of suspending traffic into the DB?

No. In my tries to repare the situation, there were no other clients but psql and DBaever.

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