I’ve the following trigger where daily_shifts_tab
and daily_shifts_wfv
are identical tables and trigger is synchronising the data to daily_shifts_wfv
from daily_shifts_tab
.
The issue here is delete
statements on daily_shifts_tab
are causing tuple locks when the deletes are more frequent from the application. The delete statement from application is
delete from daily_shifts_tab where OBJECTID = $1;
id,objectid
is PK on daily_shifts_tab
and daily_shifts_wfv
. This statement is causing the lock tuple. I don’t understand why tuple lock is happening.
How to solve the issue?
CREATE OR REPLACE FUNCTION daily_shifts_aiudr()
RETURNS trigger AS $body$
BEGIN
if (TG_OP = 'INSERT') then
INSERT INTO daily_shifts_wfv (
id,
OBJECTID,
PFID,
INTIME,
OUTTIME)
VALUES(new.id,
new.OBJECTID,
new.pfid,
new.INTIME,
new.OUTTIME
) ON CONFLICT (id,pfid,intime,scheduletype,schedtempassignoid)
DO NOTHING;
RETURN NEW;
elsif (TG_OP = 'UPDATE') then
update daily_shifts_wfv
set pfid = new.pfid,
intime = new.intime,
outtime = new.outtime
where id = current_setting('ctx_ng_vpd.ctx_id_fil')
and OBJECTID = old.OBJECTID;
RETURN NEW;
elsif (TG_OP = 'DELETE') then
delete from daily_shifts_wfv
where id = current_setting('ctx_ng_vpd.ctx_id_fil')
and OBJECTID = old.OBJECTID;
RETURN OLD;
end if;
END; $body$ LANGUAGE plpgsql;
CREATE TRIGGER daily_shifts_aiudr
AFTER INSERT OR UPDATE OR DELETE
ON daily_shifts_tab
FOR EACH ROW
EXECUTE PROCEDURE daily_shifts_aiudr();
2
As per the comment: my guess is you’re having problems with the fact that a single operation with a large payload of N rows on daily_shifts_tab
translates to N such operations on daily_shifts_wfv
.
Postgres does all the same stuff and locks all the same stuff on both tables, but it takes longer and locks hang longer on daily_shifts_wfv
because of the per-row split. You could try to switch your row-level trigger to a per-statement trigger that simply echoes the primary statement on your other table and see if that solves your problem.
Your schema and logic is a bit unclear:
- You’re not populating the
scheduletype
andschedtempassignoid
columns so I’m guessing both tables have the samedefault
s on them - You seem to have an arbiter index on
(id,pfid,intime,scheduletype,schedtempassignoid)
apart from the PK you mentioned - Not sure what’s the id in
current_setting('ctx_ng_vpd.ctx_id_fil')
doing but that looks like some sort of RLS attempt.
Disregarding or simplifying these away, here’s an example:
demo at db<>fiddle
CREATE OR REPLACE FUNCTION daily_shifts_aiudr()RETURNS trigger AS $body$
BEGIN case TG_OP WHEN 'INSERT' then
INSERT INTO daily_shifts_wfv (
id,
OBJECTID,
PFID,
INTIME,
OUTTIME)
SELECT newdata.id,
newdata.OBJECTID,
newdata.pfid,
newdata.INTIME,
newdata.OUTTIME
FROM newdata
ON CONFLICT (id,pfid,intime,scheduletype,schedtempassignoid)
DO NOTHING;
when 'UPDATE' then
update daily_shifts_wfv as target
set pfid = newdata.pfid,
intime = newdata.intime,
outtime = newdata.outtime
from newdata
join olddata using(OBJECTID)
where target.id = olddata.id
and target.OBJECTID = olddata.OBJECTID;
when 'DELETE' then
delete from daily_shifts_wfv as target
using olddata
where target.id = olddata.id
and target.OBJECTID = olddata.OBJECTID;
end case;
RETURN NULL;--doesn't matter in a statement-level trigger
END; $body$ LANGUAGE plpgsql;
CREATE TRIGGER daily_shifts_aiudr_insert
AFTER INSERT
ON daily_shifts_tab
REFERENCING NEW TABLE as newdata
FOR EACH STATEMENT
EXECUTE PROCEDURE daily_shifts_aiudr();
CREATE TRIGGER daily_shifts_aiudr_update
AFTER UPDATE
ON daily_shifts_tab
REFERENCING OLD TABLE as olddata NEW TABLE as newdata
FOR EACH STATEMENT
EXECUTE PROCEDURE daily_shifts_aiudr();
CREATE TRIGGER daily_shifts_aiudr_delete
AFTER DELETE
ON daily_shifts_tab
REFERENCING OLD TABLE as olddata
FOR EACH STATEMENT
EXECUTE PROCEDURE daily_shifts_aiudr();
You can see the operations on daily_shifts_tab
get repeated on daily_shifts_wfv
– the only difference is now it’s the whole thing applied all at once instead of row-by-row.
insert into daily_shifts_tab
(id, OBJECTID, PFID, INTIME, OUTTIME,scheduletype,schedtempassignoid)values
(2,2,1,now()-'1 min'::interval,now(),1,1)
,(3,3,1,now()-'1 min'::interval,now(),1,1)
,(4,4,1,now()-'1 min'::interval,now(),1,1);
select * from daily_shifts_wfv;
id | objectid | pfid | intime | outtime | scheduletype | schedtempassignoid |
---|---|---|---|---|---|---|
2 | 2 | 1 | 2024-12-13 09:39:24.253247+00 | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
3 | 3 | 1 | 2024-12-13 09:39:24.253247+00 | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
4 | 4 | 1 | 2024-12-13 09:39:24.253247+00 | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
delete from daily_shifts_tab
where (id, OBJECTID)=(2,2);
select * from daily_shifts_wfv;
id | objectid | pfid | intime | outtime | scheduletype | schedtempassignoid |
---|---|---|---|---|---|---|
3 | 3 | 1 | 2024-12-13 09:39:24.253247+00 | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
4 | 4 | 1 | 2024-12-13 09:39:24.253247+00 | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
update daily_shifts_tab
set intime='infinity'
where (id, OBJECTID)=(3,3);
select * from daily_shifts_wfv;
id | objectid | pfid | intime | outtime | scheduletype | schedtempassignoid |
---|---|---|---|---|---|---|
4 | 4 | 1 | 2024-12-13 09:39:24.253247+00 | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
3 | 3 | 1 | infinity | 2024-12-13 09:40:24.253247+00 | 1 | 1 |
1