From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | What is the best way to redefine a trigger? (lock issue) |
Date: | 2021-12-10 09:27:51 |
Message-ID: | 835deecd506545f7b61d5f69340c85b9@intershop.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
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?
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.
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();
Attachment | Content-Type | Size |
---|---|---|
sessions.PNG | image/png | 86.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2021-12-10 10:02:08 | Re: What is the best way to redefine a trigger? (lock issue) |
Previous Message | Achilleas Mantzios | 2021-12-10 09:24:03 | Postgresql + containerization possible use case |