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

Commitda6257e

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 parent7d9a757 commitda6257e

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
@@ -2867,8 +2867,9 @@ lmerge_matched:;
28672867
if (!ExecUpdatePrologue(context,resultRelInfo,
28682868
tupleid,NULL,newslot,&result))
28692869
{
2870-
/* Blocked by trigger, or concurrent update/delete */
2871-
break;
2870+
if (result==TM_Ok)
2871+
return true;/* "do nothing" */
2872+
break;/* concurrent update/delete */
28722873
}
28732874
result=ExecUpdateAct(context,resultRelInfo,tupleid,NULL,
28742875
newslot, false,&updateCxt);
@@ -2885,8 +2886,9 @@ lmerge_matched:;
28852886
if (!ExecDeletePrologue(context,resultRelInfo,tupleid,
28862887
NULL,NULL,&result))
28872888
{
2888-
/* Blocked by trigger, or concurrent update/delete */
2889-
break;
2889+
if (result==TM_Ok)
2890+
return true;/* "do nothing" */
2891+
break;/* concurrent update/delete */
28902892
}
28912893
result=ExecDeleteAct(context,resultRelInfo,tupleid, false);
28922894
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