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

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
fora 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
ano-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
triggerwith 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
aselect 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