Skip to content

Commit a4f7d33

Browse files
deanrasheeddeanrasheed
committed
Fix AFTER ROW trigger execution in MERGE cross-partition update.
When executing a MERGE UPDATE action, if the UPDATE is turned into a cross-partition DELETE then INSERT, do not attempt to invoke AFTER UPDATE ROW triggers, or any of the other post-update actions in ExecUpdateEpilogue(). For consistency with a plain UPDATE command, such triggers should not be fired (and typically fail anyway), and similarly, other post-update actions, such as WCO/RLS checks should not be executed, and might also lead to unexpected failures. Therefore, as with ExecUpdate(), make ExecMergeMatched() return immediately if ExecUpdateAct() reports that a cross-partition update was done, to be sure that no further processing is done for that tuple. Back-patch to v15, where MERGE was introduced. Discussion: https://postgr.es/m/CAEZATCWjBgagyNZs02vgDF0DvASYj-iHTFtXG2-nP3orZhmtcw%40mail.gmail.com
1 parent 10d34fe commit a4f7d33

File tree

3 files changed

+97
-0
lines changed

3 files changed

+97
-0
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2899,6 +2899,22 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
28992899
}
29002900
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
29012901
newslot, false, &updateCxt);
2902+
2903+
/*
2904+
* As in ExecUpdate(), if ExecUpdateAct() reports that a
2905+
* cross-partition update was done, then there's nothing else
2906+
* for us to do --- the UPDATE has been turned into a DELETE
2907+
* and an INSERT, and we must not perform any of the usual
2908+
* post-update tasks.
2909+
*/
2910+
if (updateCxt.crossPartUpdate)
2911+
{
2912+
mtstate->mt_merge_updated += 1;
2913+
if (canSetTag)
2914+
(estate->es_processed)++;
2915+
return true;
2916+
}
2917+
29022918
if (result == TM_Ok && updateCxt.updated)
29032919
{
29042920
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo,

‎src/test/regress/expected/triggers.out

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2309,6 +2309,51 @@ NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW
23092309
NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW
23102310
NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW
23112311
drop table parted_trig;
2312+
-- Verify that the correct triggers fire for cross-partition updates
2313+
create table parted_trig (a int) partition by list (a);
2314+
create table parted_trig1 partition of parted_trig for values in (1);
2315+
create table parted_trig2 partition of parted_trig for values in (2);
2316+
insert into parted_trig values (1);
2317+
create or replace function trigger_notice() returns trigger as $$
2318+
begin
2319+
raise notice 'trigger % on % % % for %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL;
2320+
if TG_LEVEL = 'ROW' then
2321+
if TG_OP = 'DELETE' then
2322+
return OLD;
2323+
else
2324+
return NEW;
2325+
end if;
2326+
end if;
2327+
return null;
2328+
end;
2329+
$$ language plpgsql;
2330+
create trigger parted_trig_before_stmt before insert or update or delete on parted_trig
2331+
for each statement execute procedure trigger_notice();
2332+
create trigger parted_trig_before_row before insert or update or delete on parted_trig
2333+
for each row execute procedure trigger_notice();
2334+
create trigger parted_trig_after_row after insert or update or delete on parted_trig
2335+
for each row execute procedure trigger_notice();
2336+
create trigger parted_trig_after_stmt after insert or update or delete on parted_trig
2337+
for each statement execute procedure trigger_notice();
2338+
update parted_trig set a = 2 where a = 1;
2339+
NOTICE: trigger parted_trig_before_stmt on parted_trig BEFORE UPDATE for STATEMENT
2340+
NOTICE: trigger parted_trig_before_row on parted_trig1 BEFORE UPDATE for ROW
2341+
NOTICE: trigger parted_trig_before_row on parted_trig1 BEFORE DELETE for ROW
2342+
NOTICE: trigger parted_trig_before_row on parted_trig2 BEFORE INSERT for ROW
2343+
NOTICE: trigger parted_trig_after_row on parted_trig1 AFTER DELETE for ROW
2344+
NOTICE: trigger parted_trig_after_row on parted_trig2 AFTER INSERT for ROW
2345+
NOTICE: trigger parted_trig_after_stmt on parted_trig AFTER UPDATE for STATEMENT
2346+
-- update action in merge should behave the same
2347+
merge into parted_trig using (select 1) as ss on true
2348+
when matched and a = 2 then update set a = 1;
2349+
NOTICE: trigger parted_trig_before_stmt on parted_trig BEFORE UPDATE for STATEMENT
2350+
NOTICE: trigger parted_trig_before_row on parted_trig2 BEFORE UPDATE for ROW
2351+
NOTICE: trigger parted_trig_before_row on parted_trig2 BEFORE DELETE for ROW
2352+
NOTICE: trigger parted_trig_before_row on parted_trig1 BEFORE INSERT for ROW
2353+
NOTICE: trigger parted_trig_after_row on parted_trig2 AFTER DELETE for ROW
2354+
NOTICE: trigger parted_trig_after_row on parted_trig1 AFTER INSERT for ROW
2355+
NOTICE: trigger parted_trig_after_stmt on parted_trig AFTER UPDATE for STATEMENT
2356+
drop table parted_trig;
23122357
-- Verify propagation of trigger arguments to partitions
23132358
create table parted_trig (a int) partition by list (a);
23142359
create table parted_trig1 partition of parted_trig for values in (1);

‎src/test/regress/sql/triggers.sql

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1583,6 +1583,42 @@ create trigger qqq after insert on parted_trig_1_1 for each row execute procedur
15831583
insert into parted_trig values (50), (1500);
15841584
drop table parted_trig;
15851585

1586+
-- Verify that the correct triggers fire for cross-partition updates
1587+
create table parted_trig (a int) partition by list (a);
1588+
create table parted_trig1 partition of parted_trig for values in (1);
1589+
create table parted_trig2 partition of parted_trig for values in (2);
1590+
insert into parted_trig values (1);
1591+
1592+
create or replace function trigger_notice() returns trigger as $$
1593+
begin
1594+
raise notice 'trigger % on % % % for %', TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL;
1595+
if TG_LEVEL = 'ROW' then
1596+
if TG_OP = 'DELETE' then
1597+
return OLD;
1598+
else
1599+
return NEW;
1600+
end if;
1601+
end if;
1602+
return null;
1603+
end;
1604+
$$ language plpgsql;
1605+
create trigger parted_trig_before_stmt before insert or update or delete on parted_trig
1606+
for each statement execute procedure trigger_notice();
1607+
create trigger parted_trig_before_row before insert or update or delete on parted_trig
1608+
for each row execute procedure trigger_notice();
1609+
create trigger parted_trig_after_row after insert or update or delete on parted_trig
1610+
for each row execute procedure trigger_notice();
1611+
create trigger parted_trig_after_stmt after insert or update or delete on parted_trig
1612+
for each statement execute procedure trigger_notice();
1613+
1614+
update parted_trig set a = 2 where a = 1;
1615+
1616+
-- update action in merge should behave the same
1617+
merge into parted_trig using (select 1) as ss on true
1618+
when matched and a = 2 then update set a = 1;
1619+
1620+
drop table parted_trig;
1621+
15861622
-- Verify propagation of trigger arguments to partitions
15871623
create table parted_trig (a int) partition by list (a);
15881624
create table parted_trig1 partition of parted_trig for values in (1);

0 commit comments

Comments
 (0)