>> 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. >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 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 > > > >
Attachment:
sessions.PNG
Description: sessions.PNG
