Tuple Lock in Postgres

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>delete from daily_shifts_tab where OBJECTID = $1;
</code>
<code>delete from daily_shifts_tab where OBJECTID = $1; </code>
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?

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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;
</code>
<code>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; </code>
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;
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>CREATE TRIGGER daily_shifts_aiudr
AFTER INSERT OR UPDATE OR DELETE
ON daily_shifts_tab
FOR EACH ROW
EXECUTE PROCEDURE daily_shifts_aiudr();
</code>
<code>CREATE TRIGGER daily_shifts_aiudr AFTER INSERT OR UPDATE OR DELETE ON daily_shifts_tab FOR EACH ROW EXECUTE PROCEDURE daily_shifts_aiudr(); </code>
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:

  1. You’re not populating the scheduletype and schedtempassignoid columns so I’m guessing both tables have the same defaults on them
  2. You seem to have an arbiter index on (id,pfid,intime,scheduletype,schedtempassignoid) apart from the PK you mentioned
  3. 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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;
</code>
<code>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; </code>
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;
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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();
</code>
<code>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(); </code>
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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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;
</code>
<code>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; </code>
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>delete from daily_shifts_tab
where (id, OBJECTID)=(2,2);
select * from daily_shifts_wfv;
</code>
<code>delete from daily_shifts_tab where (id, OBJECTID)=(2,2); select * from daily_shifts_wfv; </code>
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>update daily_shifts_tab
set intime='infinity'
where (id, OBJECTID)=(3,3);
select * from daily_shifts_wfv;
</code>
<code>update daily_shifts_tab set intime='infinity' where (id, OBJECTID)=(3,3); select * from daily_shifts_wfv; </code>
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

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa

Tuple Lock in Postgres

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>delete from daily_shifts_tab where OBJECTID = $1;
</code>
<code>delete from daily_shifts_tab where OBJECTID = $1; </code>
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?

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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;
</code>
<code>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; </code>
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;
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>CREATE TRIGGER daily_shifts_aiudr
AFTER INSERT OR UPDATE OR DELETE
ON daily_shifts_tab
FOR EACH ROW
EXECUTE PROCEDURE daily_shifts_aiudr();
</code>
<code>CREATE TRIGGER daily_shifts_aiudr AFTER INSERT OR UPDATE OR DELETE ON daily_shifts_tab FOR EACH ROW EXECUTE PROCEDURE daily_shifts_aiudr(); </code>
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:

  1. You’re not populating the scheduletype and schedtempassignoid columns so I’m guessing both tables have the same defaults on them
  2. You seem to have an arbiter index on (id,pfid,intime,scheduletype,schedtempassignoid) apart from the PK you mentioned
  3. 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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;
</code>
<code>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; </code>
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;
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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();
</code>
<code>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(); </code>
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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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;
</code>
<code>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; </code>
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>delete from daily_shifts_tab
where (id, OBJECTID)=(2,2);
select * from daily_shifts_wfv;
</code>
<code>delete from daily_shifts_tab where (id, OBJECTID)=(2,2); select * from daily_shifts_wfv; </code>
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>update daily_shifts_tab
set intime='infinity'
where (id, OBJECTID)=(3,3);
select * from daily_shifts_wfv;
</code>
<code>update daily_shifts_tab set intime='infinity' where (id, OBJECTID)=(3,3); select * from daily_shifts_wfv; </code>
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

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật