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

>> 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
>
>
>
>