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

Commit7b14e20

Browse files
committed
Fix MERGE command tag for actions blocked by BEFORE ROW triggers.
This ensures that the row count in the command tag for a MERGE iscorrectly computed in the case where UPDATEs or DELETEs are skippeddue to a BEFORE ROW trigger returning NULL (the INSERT case wasalready handled correctly by ExecMergeNotMatched() callingExecInsert()).Back-patch to v15, where MERGE was introduced.Discussion:https://postgr.es/m/CAEZATCU8XEmR0JWKDtyb7iZ%3DqCffxS9uyJt0iOZ4TV4RT%2Bow1w%40mail.gmail.com
1 parent9321c79 commit7b14e20

File tree

3 files changed

+34
-4
lines changed

3 files changed

+34
-4
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2883,8 +2883,9 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
28832883
if (!ExecUpdatePrologue(context,resultRelInfo,
28842884
tupleid,NULL,newslot,&result))
28852885
{
2886-
/* Blocked by trigger, or concurrent update/delete */
2887-
break;
2886+
if (result==TM_Ok)
2887+
return true;/* "do nothing" */
2888+
break;/* concurrent update/delete */
28882889
}
28892890
result=ExecUpdateAct(context,resultRelInfo,tupleid,NULL,
28902891
newslot, false,&updateCxt);
@@ -2901,8 +2902,9 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
29012902
if (!ExecDeletePrologue(context,resultRelInfo,tupleid,
29022903
NULL,NULL,&result))
29032904
{
2904-
/* Blocked by trigger, or concurrent update/delete */
2905-
break;
2905+
if (result==TM_Ok)
2906+
return true;/* "do nothing" */
2907+
break;/* concurrent update/delete */
29062908
}
29072909
result=ExecDeleteAct(context,resultRelInfo,tupleid, false);
29082910
if (result==TM_Ok)

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -942,12 +942,25 @@ SELECT * FROM target full outer join source on (sid = tid);
942942

943943
create trigger merge_skip BEFORE INSERT OR UPDATE or DELETE
944944
ON target FOR EACH ROW EXECUTE FUNCTION skip_merge_op();
945+
DO $$
946+
DECLARE
947+
result integer;
948+
BEGIN
945949
MERGE INTO target t
946950
USING source AS s
947951
ON t.tid = s.sid
948952
WHEN MATCHED AND s.sid = 3 THEN UPDATE SET balance = t.balance + s.delta
949953
WHEN MATCHED THEN DELETE
950954
WHEN NOT MATCHED THEN INSERT VALUES (sid, delta);
955+
IF FOUND THEN
956+
RAISE NOTICE 'Found';
957+
ELSE
958+
RAISE NOTICE 'Not found';
959+
END IF;
960+
GET DIAGNOSTICS result := ROW_COUNT;
961+
RAISE NOTICE 'ROW_COUNT = %', result;
962+
END;
963+
$$;
951964
NOTICE: BEFORE INSERT STATEMENT trigger
952965
NOTICE: BEFORE UPDATE STATEMENT trigger
953966
NOTICE: BEFORE DELETE STATEMENT trigger
@@ -957,6 +970,8 @@ NOTICE: BEFORE INSERT ROW trigger row: (4,40)
957970
NOTICE: AFTER DELETE STATEMENT trigger
958971
NOTICE: AFTER UPDATE STATEMENT trigger
959972
NOTICE: AFTER INSERT STATEMENT trigger
973+
NOTICE: Not found
974+
NOTICE: ROW_COUNT = 0
960975
SELECT * FROM target FULL OUTER JOIN source ON (sid = tid);
961976
tid | balance | sid | delta
962977
-----+---------+-----+-------

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -636,12 +636,25 @@ $$;
636636
SELECT*FROM target full outerjoin sourceon (sid= tid);
637637
createtriggermerge_skip BEFORE INSERTORUPDATEorDELETE
638638
ON target FOR EACH ROW EXECUTE FUNCTION skip_merge_op();
639+
DO $$
640+
DECLARE
641+
resultinteger;
642+
BEGIN
639643
MERGE INTO target t
640644
USING sourceAS s
641645
ONt.tid=s.sid
642646
WHEN MATCHEDANDs.sid=3 THENUPDATESET balance=t.balance+s.delta
643647
WHEN MATCHED THENDELETE
644648
WHEN NOT MATCHED THEN INSERTVALUES (sid, delta);
649+
IF FOUND THEN
650+
RAISE NOTICE'Found';
651+
ELSE
652+
RAISE NOTICE'Not found';
653+
END IF;
654+
GET DIAGNOSTICS result := ROW_COUNT;
655+
RAISE NOTICE'ROW_COUNT = %', result;
656+
END;
657+
$$;
645658
SELECT*FROM target FULL OUTERJOIN sourceON (sid= tid);
646659
DROPTRIGGER merge_skipON target;
647660
DROPFUNCTION skip_merge_op();

0 commit comments

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