Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita4f7d33

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

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_trigvalues (50), (1500);
15841584
droptable parted_trig;
15851585

1586+
-- Verify that the correct triggers fire for cross-partition updates
1587+
createtableparted_trig (aint) partition by list (a);
1588+
createtableparted_trig1 partition of parted_trig forvaluesin (1);
1589+
createtableparted_trig2 partition of parted_trig forvaluesin (2);
1590+
insert into parted_trigvalues (1);
1591+
1592+
create or replacefunctiontrigger_notice() returns triggeras $$
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+
returnnull;
1603+
end;
1604+
$$ language plpgsql;
1605+
createtriggerparted_trig_before_stmt before insertorupdateordeleteon parted_trig
1606+
for each statement execute procedure trigger_notice();
1607+
createtriggerparted_trig_before_row before insertorupdateordeleteon parted_trig
1608+
for each row execute procedure trigger_notice();
1609+
createtriggerparted_trig_after_row after insertorupdateordeleteon parted_trig
1610+
for each row execute procedure trigger_notice();
1611+
createtriggerparted_trig_after_stmt after insertorupdateordeleteon parted_trig
1612+
for each statement execute procedure trigger_notice();
1613+
1614+
update parted_trigset a=2where a=1;
1615+
1616+
-- update action in merge should behave the same
1617+
merge into parted_trig using (select1)as sson true
1618+
when matchedand a=2 thenupdateset a=1;
1619+
1620+
droptable parted_trig;
1621+
15861622
-- Verify propagation of trigger arguments to partitions
15871623
createtableparted_trig (aint) partition by list (a);
15881624
createtableparted_trig1 partition of parted_trig forvaluesin (1);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp