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

On 10/12/21 11:27 π.μ., Marc Mamin wrote:
> 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.

Do you need to drop/create the trigger or a CREATE OR REPLACE function would suffice?

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