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

Commita0ff371

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 parente557db1 commita0ff371

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
@@ -1425,6 +1425,7 @@ ExecDelete(ModifyTableContext *context,
14251425
boolprocessReturning,
14261426
boolchangingPart,
14271427
boolcanSetTag,
1428+
TM_Result*tmresult,
14281429
bool*tupleDeleted,
14291430
TupleTableSlot**epqreturnslot)
14301431
{
@@ -1441,7 +1442,7 @@ ExecDelete(ModifyTableContext *context,
14411442
* done if it says we are.
14421443
*/
14431444
if (!ExecDeletePrologue(context,resultRelInfo,tupleid,oldtuple,
1444-
epqreturnslot,NULL))
1445+
epqreturnslot,tmresult))
14451446
returnNULL;
14461447

14471448
/* INSTEAD OF ROW DELETE Triggers */
@@ -1496,6 +1497,9 @@ ExecDelete(ModifyTableContext *context,
14961497
ldelete:
14971498
result=ExecDeleteAct(context,resultRelInfo,tupleid,changingPart);
14981499

1500+
if (tmresult)
1501+
*tmresult=result;
1502+
14991503
switch (result)
15001504
{
15011505
caseTM_SelfModified:
@@ -1734,6 +1738,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
17341738
TupleTableSlot*slot,
17351739
boolcanSetTag,
17361740
UpdateContext*updateCxt,
1741+
TM_Result*tmresult,
17371742
TupleTableSlot**retry_slot,
17381743
TupleTableSlot**inserted_tuple,
17391744
ResultRelInfo**insert_destrel)
@@ -1797,7 +1802,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
17971802
false,/* processReturning */
17981803
true,/* changingPart */
17991804
false,/* canSetTag */
1800-
&tuple_deleted,&epqslot);
1805+
tmresult,&tuple_deleted,&epqslot);
18011806

18021807
/*
18031808
* For some reason if DELETE didn't happen (e.g. trigger prevented it, or
@@ -1829,7 +1834,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
18291834
* action entirely).
18301835
*/
18311836
if (context->relaction!=NULL)
1832-
returnfalse;
1837+
return*tmresult==TM_Ok;
18331838
elseif (TupIsNull(epqslot))
18341839
return true;
18351840
else
@@ -2031,6 +2036,7 @@ ExecUpdateAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
20312036
if (ExecCrossPartitionUpdate(context,resultRelInfo,
20322037
tupleid,oldtuple,slot,
20332038
canSetTag,updateCxt,
2039+
&result,
20342040
&retry_slot,
20352041
&inserted_tuple,
20362042
&insert_destrel))
@@ -2070,7 +2076,7 @@ ExecUpdateAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
20702076
* here; instead let it handle that on its own rules.
20712077
*/
20722078
if (context->relaction!=NULL)
2073-
returnTM_Updated;
2079+
returnresult;
20742080

20752081
/*
20762082
* ExecCrossPartitionUpdate installed an updated version of the new
@@ -2898,7 +2904,7 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
28982904
break;/* concurrent update/delete */
28992905
}
29002906
result=ExecUpdateAct(context,resultRelInfo,tupleid,NULL,
2901-
newslot,false,&updateCxt);
2907+
newslot,canSetTag,&updateCxt);
29022908

29032909
/*
29042910
* As in ExecUpdate(), if ExecUpdateAct() reports that a
@@ -2910,8 +2916,6 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
29102916
if (updateCxt.crossPartUpdate)
29112917
{
29122918
mtstate->mt_merge_updated+=1;
2913-
if (canSetTag)
2914-
(estate->es_processed)++;
29152919
return true;
29162920
}
29172921

@@ -3843,7 +3847,7 @@ ExecModifyTable(PlanState *pstate)
38433847

38443848
caseCMD_DELETE:
38453849
slot=ExecDelete(&context,resultRelInfo,tupleid,oldtuple,
3846-
true, false,node->canSetTag,NULL,NULL);
3850+
true, false,node->canSetTag,NULL,NULL,NULL);
38473851
break;
38483852

38493853
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