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

On 10/12/21 12:56 μ.μ., Marc Mamin wrote:
>     >
>     >-----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
triggerfunction 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
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
levellock (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.
DBeaver is notorious for keeping transactions open.
>
>     >>     >>
>     >>     >> 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
inseparate 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();
>     >>     >>
>     >>     >>
>     >>     >
>     >>     >
>     >>     >--
>     >>     >Achilleas Mantzios
>     >>     >DBA, Analyst, IT Lead
>     >>     >IT DEPT
>     >>     >Dynacom Tankers Mgmt
>     >>     >
>     >>     >
>     >>     >
>     >>     >
>     >
>     >
>     >--
>     >Achilleas Mantzios
>     >DBA, Analyst, IT Lead
>     >IT DEPT
>     >Dynacom Tankers Mgmt
>     >
>     >
>     >
>     >


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt