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

Commit7e8c6d7

Browse files
committed
Fix BEFORE ROW trigger handling in cross-partition MERGE update.
Fix a bug during MERGE if a cross-partition update is attempted on apartitioned table with a BEFORE DELETE ROW trigger that returns NULL,to prevent the update. This would cause an error to be thrown, or anassert failure in an assert-enabled build.This was an oversight in9321c79, which failed to properlydistinguish a DELETE prevented by a trigger from one prevented by aconcurrent update. Fix by having ExecDelete() return the TM_Resultstatus to ExecCrossPartitionUpdate(), so that it can distinguish thetwo cases, and make ExecCrossPartitionUpdate() return the TM_Resultstatus to ExecUpdateAct(), so that it can return the correct statusfrom a concurrent update.In addition, ensure that the command tag is correctly updated byhaving ExecMergeMatched() pass canSetTag to ExecUpdateAct(), ratherthan passing false, so that it updates the command tag if it does across-partition update, making this code path in ExecMergeMatched()consistent with ExecUpdate().Per bug #18238 from Alexander Lakhin. Back-patch to v15, where MERGEwas introduced.Dean Rasheed, reviewed by Richard Guo and Jian He.Discussion:https://postgr.es/m/18238-2f2bdc7f720180b9%40postgresql.org
1 parent3e8379a commit7e8c6d7

File tree

3 files changed

+167
-8
lines changed

3 files changed

+167
-8
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 12 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1404,6 +1404,7 @@ ExecDelete(ModifyTableContext *context,
14041404
boolprocessReturning,
14051405
boolchangingPart,
14061406
boolcanSetTag,
1407+
TM_Result*tmresult,
14071408
bool*tupleDeleted,
14081409
TupleTableSlot**epqreturnslot)
14091410
{
@@ -1420,7 +1421,7 @@ ExecDelete(ModifyTableContext *context,
14201421
* done if it says we are.
14211422
*/
14221423
if (!ExecDeletePrologue(context,resultRelInfo,tupleid,oldtuple,
1423-
epqreturnslot,NULL))
1424+
epqreturnslot,tmresult))
14241425
returnNULL;
14251426

14261427
/* INSTEAD OF ROW DELETE Triggers */
@@ -1475,6 +1476,9 @@ ExecDelete(ModifyTableContext *context,
14751476
ldelete:;
14761477
result=ExecDeleteAct(context,resultRelInfo,tupleid,changingPart);
14771478

1479+
if (tmresult)
1480+
*tmresult=result;
1481+
14781482
switch (result)
14791483
{
14801484
caseTM_SelfModified:
@@ -1713,6 +1717,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
17131717
TupleTableSlot*slot,
17141718
boolcanSetTag,
17151719
UpdateContext*updateCxt,
1720+
TM_Result*tmresult,
17161721
TupleTableSlot**retry_slot,
17171722
TupleTableSlot**inserted_tuple,
17181723
ResultRelInfo**insert_destrel)
@@ -1776,7 +1781,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
17761781
false,/* processReturning */
17771782
true,/* changingPart */
17781783
false,/* canSetTag */
1779-
&tuple_deleted,&epqslot);
1784+
tmresult,&tuple_deleted,&epqslot);
17801785

17811786
/*
17821787
* For some reason if DELETE didn't happen (e.g. trigger prevented it, or
@@ -1808,7 +1813,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
18081813
* action entirely).
18091814
*/
18101815
if (context->relaction!=NULL)
1811-
returnfalse;
1816+
return*tmresult==TM_Ok;
18121817
elseif (TupIsNull(epqslot))
18131818
return true;
18141819
else
@@ -2013,6 +2018,7 @@ ExecUpdateAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
20132018
if (ExecCrossPartitionUpdate(context,resultRelInfo,
20142019
tupleid,oldtuple,slot,
20152020
canSetTag,updateCxt,
2021+
&result,
20162022
&retry_slot,
20172023
&inserted_tuple,
20182024
&insert_destrel))
@@ -2052,7 +2058,7 @@ ExecUpdateAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
20522058
* here; instead let it handle that on its own rules.
20532059
*/
20542060
if (context->relaction!=NULL)
2055-
returnTM_Updated;
2061+
returnresult;
20562062

20572063
/*
20582064
* ExecCrossPartitionUpdate installed an updated version of the new
@@ -2879,7 +2885,7 @@ lmerge_matched:;
28792885
break;/* concurrent update/delete */
28802886
}
28812887
result=ExecUpdateAct(context,resultRelInfo,tupleid,NULL,
2882-
newslot,false,&updateCxt);
2888+
newslot,canSetTag,&updateCxt);
28832889

28842890
/*
28852891
* As in ExecUpdate(), if ExecUpdateAct() reports that a
@@ -2891,8 +2897,6 @@ lmerge_matched:;
28912897
if (updateCxt.crossPartUpdate)
28922898
{
28932899
mtstate->mt_merge_updated+=1;
2894-
if (canSetTag)
2895-
(estate->es_processed)++;
28962900
return true;
28972901
}
28982902

@@ -3824,7 +3828,7 @@ ExecModifyTable(PlanState *pstate)
38243828

38253829
caseCMD_DELETE:
38263830
slot=ExecDelete(&context,resultRelInfo,tupleid,oldtuple,
3827-
true, false,node->canSetTag,NULL,NULL);
3831+
true, false,node->canSetTag,NULL,NULL,NULL);
38283832
break;
38293833

38303834
caseCMD_MERGE:

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

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1662,13 +1662,22 @@ ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;
16621662
INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
16631663
-- try simple MERGE
16641664
BEGIN;
1665+
DO $$
1666+
DECLARE
1667+
result integer;
1668+
BEGIN
16651669
MERGE INTO pa_target t
16661670
USING pa_source s
16671671
ON t.tid = s.sid
16681672
WHEN MATCHED THEN
16691673
UPDATE SET balance = balance + delta, val = val || ' updated by merge'
16701674
WHEN NOT MATCHED THEN
16711675
INSERT VALUES (sid, delta, 'inserted by merge');
1676+
GET DIAGNOSTICS result := ROW_COUNT;
1677+
RAISE NOTICE 'ROW_COUNT = %', result;
1678+
END;
1679+
$$;
1680+
NOTICE: ROW_COUNT = 14
16721681
SELECT * FROM pa_target ORDER BY tid;
16731682
tid | balance | val
16741683
-----+---------+--------------------------
@@ -1725,13 +1734,22 @@ SELECT * FROM pa_target ORDER BY tid;
17251734
ROLLBACK;
17261735
-- try updating the partition key column
17271736
BEGIN;
1737+
DO $$
1738+
DECLARE
1739+
result integer;
1740+
BEGIN
17281741
MERGE INTO pa_target t
17291742
USING pa_source s
17301743
ON t.tid = s.sid
17311744
WHEN MATCHED THEN
17321745
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
17331746
WHEN NOT MATCHED THEN
17341747
INSERT VALUES (sid, delta, 'inserted by merge');
1748+
GET DIAGNOSTICS result := ROW_COUNT;
1749+
RAISE NOTICE 'ROW_COUNT = %', result;
1750+
END;
1751+
$$;
1752+
NOTICE: ROW_COUNT = 14
17351753
SELECT * FROM pa_target ORDER BY tid;
17361754
tid | balance | val
17371755
-----+---------+--------------------------
@@ -1751,6 +1769,79 @@ SELECT * FROM pa_target ORDER BY tid;
17511769
14 | 140 | inserted by merge
17521770
(14 rows)
17531771

1772+
ROLLBACK;
1773+
-- as above, but blocked by BEFORE DELETE ROW trigger
1774+
BEGIN;
1775+
CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS
1776+
$$ BEGIN RETURN NULL; END; $$;
1777+
CREATE TRIGGER del_trig BEFORE DELETE ON pa_target
1778+
FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1779+
DO $$
1780+
DECLARE
1781+
result integer;
1782+
BEGIN
1783+
MERGE INTO pa_target t
1784+
USING pa_source s
1785+
ON t.tid = s.sid
1786+
WHEN MATCHED THEN
1787+
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1788+
WHEN NOT MATCHED THEN
1789+
INSERT VALUES (sid, delta, 'inserted by merge');
1790+
GET DIAGNOSTICS result := ROW_COUNT;
1791+
RAISE NOTICE 'ROW_COUNT = %', result;
1792+
END;
1793+
$$;
1794+
NOTICE: ROW_COUNT = 10
1795+
SELECT * FROM pa_target ORDER BY tid;
1796+
tid | balance | val
1797+
-----+---------+--------------------------
1798+
1 | 100 | initial
1799+
2 | 20 | inserted by merge
1800+
3 | 300 | initial
1801+
4 | 40 | inserted by merge
1802+
6 | 550 | initial updated by merge
1803+
6 | 60 | inserted by merge
1804+
7 | 700 | initial
1805+
8 | 80 | inserted by merge
1806+
9 | 900 | initial
1807+
10 | 100 | inserted by merge
1808+
12 | 1210 | initial updated by merge
1809+
12 | 120 | inserted by merge
1810+
14 | 1430 | initial updated by merge
1811+
14 | 140 | inserted by merge
1812+
(14 rows)
1813+
1814+
ROLLBACK;
1815+
-- as above, but blocked by BEFORE INSERT ROW trigger
1816+
BEGIN;
1817+
CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS
1818+
$$ BEGIN RETURN NULL; END; $$;
1819+
CREATE TRIGGER ins_trig BEFORE INSERT ON pa_target
1820+
FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1821+
DO $$
1822+
DECLARE
1823+
result integer;
1824+
BEGIN
1825+
MERGE INTO pa_target t
1826+
USING pa_source s
1827+
ON t.tid = s.sid
1828+
WHEN MATCHED THEN
1829+
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1830+
WHEN NOT MATCHED THEN
1831+
INSERT VALUES (sid, delta, 'inserted by merge');
1832+
GET DIAGNOSTICS result := ROW_COUNT;
1833+
RAISE NOTICE 'ROW_COUNT = %', result;
1834+
END;
1835+
$$;
1836+
NOTICE: ROW_COUNT = 3
1837+
SELECT * FROM pa_target ORDER BY tid;
1838+
tid | balance | val
1839+
-----+---------+--------------------------
1840+
6 | 550 | initial updated by merge
1841+
12 | 1210 | initial updated by merge
1842+
14 | 1430 | initial updated by merge
1843+
(3 rows)
1844+
17541845
ROLLBACK;
17551846
-- test RLS enforcement
17561847
BEGIN;

‎src/test/regress/sql/merge.sql

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1060,13 +1060,21 @@ INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2
10601060

10611061
-- try simple MERGE
10621062
BEGIN;
1063+
DO $$
1064+
DECLARE
1065+
resultinteger;
1066+
BEGIN
10631067
MERGE INTO pa_target t
10641068
USING pa_source s
10651069
ONt.tid=s.sid
10661070
WHEN MATCHED THEN
10671071
UPDATESET balance= balance+ delta, val= val||' updated by merge'
10681072
WHEN NOT MATCHED THEN
10691073
INSERTVALUES (sid, delta,'inserted by merge');
1074+
GET DIAGNOSTICS result := ROW_COUNT;
1075+
RAISE NOTICE'ROW_COUNT = %', result;
1076+
END;
1077+
$$;
10701078
SELECT*FROM pa_targetORDER BY tid;
10711079
ROLLBACK;
10721080

@@ -1085,13 +1093,69 @@ ROLLBACK;
10851093

10861094
-- try updating the partition key column
10871095
BEGIN;
1096+
DO $$
1097+
DECLARE
1098+
resultinteger;
1099+
BEGIN
1100+
MERGE INTO pa_target t
1101+
USING pa_source s
1102+
ONt.tid=s.sid
1103+
WHEN MATCHED THEN
1104+
UPDATESET tid= tid+1, balance= balance+ delta, val= val||' updated by merge'
1105+
WHEN NOT MATCHED THEN
1106+
INSERTVALUES (sid, delta,'inserted by merge');
1107+
GET DIAGNOSTICS result := ROW_COUNT;
1108+
RAISE NOTICE'ROW_COUNT = %', result;
1109+
END;
1110+
$$;
1111+
SELECT*FROM pa_targetORDER BY tid;
1112+
ROLLBACK;
1113+
1114+
-- as above, but blocked by BEFORE DELETE ROW trigger
1115+
BEGIN;
1116+
CREATEFUNCTIONtrig_fn() RETURNS trigger LANGUAGE plpgsqlAS
1117+
$$BEGIN RETURNNULL; END; $$;
1118+
CREATETRIGGERdel_trig BEFOREDELETEON pa_target
1119+
FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1120+
DO $$
1121+
DECLARE
1122+
resultinteger;
1123+
BEGIN
10881124
MERGE INTO pa_target t
10891125
USING pa_source s
10901126
ONt.tid=s.sid
10911127
WHEN MATCHED THEN
10921128
UPDATESET tid= tid+1, balance= balance+ delta, val= val||' updated by merge'
10931129
WHEN NOT MATCHED THEN
10941130
INSERTVALUES (sid, delta,'inserted by merge');
1131+
GET DIAGNOSTICS result := ROW_COUNT;
1132+
RAISE NOTICE'ROW_COUNT = %', result;
1133+
END;
1134+
$$;
1135+
SELECT*FROM pa_targetORDER BY tid;
1136+
ROLLBACK;
1137+
1138+
-- as above, but blocked by BEFORE INSERT ROW trigger
1139+
BEGIN;
1140+
CREATEFUNCTIONtrig_fn() RETURNS trigger LANGUAGE plpgsqlAS
1141+
$$BEGIN RETURNNULL; END; $$;
1142+
CREATETRIGGERins_trig BEFORE INSERTON pa_target
1143+
FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1144+
DO $$
1145+
DECLARE
1146+
resultinteger;
1147+
BEGIN
1148+
MERGE INTO pa_target t
1149+
USING pa_source s
1150+
ONt.tid=s.sid
1151+
WHEN MATCHED THEN
1152+
UPDATESET tid= tid+1, balance= balance+ delta, val= val||' updated by merge'
1153+
WHEN NOT MATCHED THEN
1154+
INSERTVALUES (sid, delta,'inserted by merge');
1155+
GET DIAGNOSTICS result := ROW_COUNT;
1156+
RAISE NOTICE'ROW_COUNT = %', result;
1157+
END;
1158+
$$;
10951159
SELECT*FROM pa_targetORDER BY tid;
10961160
ROLLBACK;
10971161

0 commit comments

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