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

On 10/12/21 12:56 μ.μ., Marc Mamin wrote:
> >
> >-----Original Message-----
> >From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
> >Sent: Freitag, 10. Dezember 2021 11:36
> >To: pgsql-general(at)lists(dot)postgresql(dot)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 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?
> >> >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.
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 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();
> >> >>
> >> >>
> >> >
> >> >
> >> >--
> >> >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