pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
7+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2023-03-13 9321c79c86 7d9a75713a Fix concurrent update issues with MERGE.
* BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-02-26 10:00 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2023-02-26 10:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 17809
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 15.2
Operating system: Ubuntu 22.04
Description:
When executing the following script:
cat << "EOF" | psql
CREATE TABLE target (tid integer, val integer);
INSERT INTO target VALUES (1, 0);
CREATE OR REPLACE FUNCTION brut_func() RETURNS trigger LANGUAGE plpgsql AS
'BEGIN RETURN NEW; END;';
CREATE TRIGGER brut BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE
brut_func();
EOF
for ((i=1;i<=10;i++)); do
echo "iteration $i"
psql -c "UPDATE TARGET SET val = 0" &
psql -c "MERGE INTO target t1 USING target t2 ON t1.tid = t2.tid WHEN
MATCHED THEN UPDATE SET val = 0" &
wait
ls tmpdb/core* && break
coredumpctl --no-pager && break
done
I get a server crash with the following stack trace:
Program terminated with signal SIGSEGV, Segmentation fault.
warning: Section `.reg-xstate/1808258' in core file too small.
#0 0x000055e66490440e in internalGetUpdateNewTuple (relinfo=0x55e66595e040,
planSlot=0x55e665998288,
oldSlot=0x55e665969978, relaction=0x0) at nodeModifyTable.c:741
741 econtext = newProj->pi_exprContext;
(gdb) bt
#0 0x000055e66490440e in internalGetUpdateNewTuple (relinfo=0x55e66595e040,
planSlot=0x55e665998288,
oldSlot=0x55e665969978, relaction=0x0) at nodeModifyTable.c:741
#1 0x000055e6649043e0 in ExecGetUpdateNewTuple (relinfo=0x55e66595e040,
planSlot=0x55e665998288,
oldSlot=0x55e665969978) at nodeModifyTable.c:726
#2 0x000055e66487e835 in ExecBRUpdateTriggers (estate=0x55e66595dba0,
epqstate=0x55e66595df10,
relinfo=0x55e66595e040, tupleid=0x7ffec9335002, fdw_trigtuple=0x0,
newslot=0x55e665969748, tmfd=0x7ffec93350b0)
at trigger.c:3037
#3 0x000055e66490622e in ExecUpdatePrologue (context=0x7ffec9335080,
resultRelInfo=0x55e66595e040,
tupleid=0x7ffec9335002, oldtuple=0x0, slot=0x55e665969748) at
nodeModifyTable.c:1912
#4 0x000055e6649078b2 in ExecMergeMatched (context=0x7ffec9335080,
resultRelInfo=0x55e66595e040,
tupleid=0x7ffec9335002, canSetTag=true) at nodeModifyTable.c:2884
#5 0x000055e6649075da in ExecMerge (context=0x7ffec9335080,
resultRelInfo=0x55e66595e040, tupleid=0x7ffec9335002,
canSetTag=true) at nodeModifyTable.c:2750
#6 0x000055e6649097f2 in ExecModifyTable (pstate=0x55e66595de28) at
nodeModifyTable.c:3866
#7 0x000055e6648c60d7 in ExecProcNodeFirst (node=0x55e66595de28) at
execProcnode.c:464
#8 0x000055e6648b9353 in ExecProcNode (node=0x55e66595de28) at
../../../src/include/executor/executor.h:259
#9 0x000055e6648bc25a in ExecutePlan (estate=0x55e66595dba0,
planstate=0x55e66595de28, use_parallel_mode=false,
operation=CMD_MERGE, sendTuples=false, numberTuples=0,
direction=ForwardScanDirection, dest=0x55e665955bf8,
execute_once=true) at execMain.c:1636
#10 0x000055e6648b9a8a in standard_ExecutorRun (queryDesc=0x55e66594c960,
direction=ForwardScanDirection, count=0,
execute_once=true) at execMain.c:363
#11 0x000055e6648b9875 in ExecutorRun (queryDesc=0x55e66594c960,
direction=ForwardScanDirection, count=0,
execute_once=true) at execMain.c:307
#12 0x000055e664b31952 in ProcessQuery (plan=0x55e665955b08,
sourceText=0x55e6658593b0 "MERGE INTO target t1 USING target t2 ON
t1.tid = t2.tid WHEN MATCHED THEN UPDATE SET val = 0", params=0x0,
queryEnv=0x0, dest=0x55e665955bf8, qc=0x7ffec93354e0) at pquery.c:160
#13 0x000055e664b33545 in PortalRunMulti (portal=0x55e6658d5190,
isTopLevel=true, setHoldSnapshot=false,
dest=0x55e665955bf8, altdest=0x55e665955bf8, qc=0x7ffec93354e0) at
pquery.c:1277
#14 0x000055e664b32a28 in PortalRun (portal=0x55e6658d5190,
count=9223372036854775807, isTopLevel=true, run_once=true,
dest=0x55e665955bf8, altdest=0x55e665955bf8, qc=0x7ffec93354e0) at
pquery.c:791
#15 0x000055e664b2b803 in exec_simple_query (
query_string=0x55e6658593b0 "MERGE INTO target t1 USING target t2 ON
t1.tid = t2.tid WHEN MATCHED THEN UPDATE SET val = 0") at postgres.c:1250
#16 0x000055e664b30722 in PostgresMain (dbname=0x55e665885938 "regression",
username=0x55e665885918 "law")
at postgres.c:4593
#17 0x000055e664a55c23 in BackendRun (port=0x55e66587d420) at
postmaster.c:4511
#18 0x000055e664a554aa in BackendStartup (port=0x55e66587d420) at
postmaster.c:4239
#19 0x000055e664a51437 in ServerLoop () at postmaster.c:1806
#20 0x000055e664a50b94 in PostmasterMain (argc=3, argv=0x55e665853610) at
postmaster.c:1478
#21 0x000055e664944ad5 in main (argc=3, argv=0x55e665853610) at main.c:202
At first sight it appeared like another manifestation of bug #17798,
but it seems that this is a distinct defect.
As I can see, ExecModifyTable() for the "case CMD_MERGE" can pass to
ExecMerge() the resultRelInfo structure with ri_projectNew == null
(but ri_projectNewInfoValid == true (set in ExecInitMergeTupleSlots)).
This is not an issue till GetTupleForTrigger() in ExecBRUpdateTriggers()
gets a tuple. But when a concurrent update occurs, GetTupleForTrigger()
fails and ExecGetUpdateNewTuple() gets called, where
internalGetUpdateNewTuple() tries to get
relinfo->ri_projectNew->pi_exprContext...
The issue appeared with the MERGE introduction (7103ebb7a).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-02-27 12:07 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: Dean Rasheed @ 2023-02-27 12:07 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 27 Feb 2023 at 08:06, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> When executing the following script I get a server crash with the following stack trace:
> Program terminated with signal SIGSEGV, Segmentation fault.
>
Confirmed. I can reliably reproduce this using 2 psql sessions as follows:
-- Setup
DROP TABLE IF EXISTS target;
CREATE TABLE target (tid integer, val integer);
INSERT INTO target VALUES (1, 0);
CREATE OR REPLACE FUNCTION brut_func() RETURNS trigger LANGUAGE plpgsql AS
$$ BEGIN RETURN NEW; END; $$;
CREATE TRIGGER brut BEFORE UPDATE ON target
FOR EACH ROW EXECUTE PROCEDURE brut_func();
-- Session 1
BEGIN;
UPDATE TARGET SET val = 0;
-- Session 2
MERGE INTO target t1 USING target t2 ON t1.tid = t2.tid
WHEN MATCHED THEN UPDATE SET val = 0;
-- Session 1
COMMIT;
What's happening is that (in session 2) the trigger code in
ExecBRUpdateTriggers() detects the concurrent update and calls
ExecGetUpdateNewTuple() to get a new tuple. However,
ExecGetUpdateNewTuple() just calls internalGetUpdateNewTuple(), when
really it ought to call mergeGetUpdateNewTuple(), since we're in a
MERGE. Unfortunately there's no way for ExecGetUpdateNewTuple() to
know that, because it doesn't have the required context information.
A quick hack that fixes the problem is to pass the current
MergeActionState to ExecGetUpdateNewTuple(), which also then requires
that it be passed to ExecBRUpdateTriggers(). Changing the signature of
2 public functions in that way in back branches doesn't seem very nice
though.
OTOH, it's difficult to see how it can be fixed any other way.
I don't really like this change to ExecGetUpdateNewTuple(), but if we
are going to change it like this, then I think we should go all the
way and get rid of the GetUpdateNewTuple callback, and the separate
internalGetUpdateNewTuple() and mergeGetUpdateNewTuple() functions,
and just do it all in ExecGetUpdateNewTuple(), so that all the code is
in one place, making it easier to follow.
Thoughts?
Regards,
Dean
Attachments:
bug-17809.patch (text/x-patch, 4.1K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index d7abde1..fb4b918
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2938,7 +2938,8 @@ ExecBRUpdateTriggers(EState *estate, EPQ
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
- TM_FailureData *tmfd)
+ TM_FailureData *tmfd,
+ MergeActionState *relaction)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
@@ -2983,7 +2984,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
TupleTableSlot *epqslot_clean;
epqslot_clean = ExecGetUpdateNewTuple(relinfo, epqslot_candidate,
- oldslot);
+ oldslot, relaction);
if (newslot != epqslot_clean)
ExecCopySlot(newslot, epqslot_clean);
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
new file mode 100644
index c484f5c..afc946b
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -486,7 +486,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
if (!ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
- tid, NULL, slot, NULL))
+ tid, NULL, slot, NULL, NULL))
skip_tuple = true; /* "do nothing" */
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 6f0543a..5852bee
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -708,14 +708,18 @@ ExecGetInsertNewTuple(ResultRelInfo *rel
TupleTableSlot *
ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot)
+ TupleTableSlot *oldSlot,
+ MergeActionState *relaction)
{
/* Use a few extra Asserts to protect against outside callers */
Assert(relinfo->ri_projectNewInfoValid);
Assert(planSlot != NULL && !TTS_EMPTY(planSlot));
Assert(oldSlot != NULL && !TTS_EMPTY(oldSlot));
- return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
+ if (relaction)
+ return mergeGetUpdateNewTuple(relinfo, planSlot, oldSlot, relaction);
+ else
+ return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
}
/*
@@ -1903,7 +1907,7 @@ ExecUpdatePrologue(ModifyTableContext *c
return ExecBRUpdateTriggers(context->estate, context->epqstate,
resultRelInfo, tupleid, oldtuple, slot,
- &context->tmfd);
+ &context->tmfd, context->relaction);
}
return true;
@@ -2395,7 +2399,7 @@ redo_act:
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
slot = ExecGetUpdateNewTuple(resultRelInfo,
- epqslot, oldSlot);
+ epqslot, oldSlot, NULL);
goto redo_act;
case TM_Deleted:
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index 39d764d..52b4ae3
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -232,7 +232,8 @@ extern bool ExecBRUpdateTriggers(EState
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
- TM_FailureData *tmfd);
+ TM_FailureData *tmfd,
+ MergeActionState *relaction);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
ResultRelInfo *src_partinfo,
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
new file mode 100644
index e7e25c0..17e57ba
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -659,7 +659,8 @@ extern void CheckSubscriptionRelkind(cha
*/
extern TupleTableSlot *ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot);
+ TupleTableSlot *oldSlot,
+ MergeActionState *relaction);
extern ResultRelInfo *ExecLookupResultRelByOid(ModifyTableState *node,
Oid resultoid,
bool missing_ok,
* Re: BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-03-07 11:50 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2023-03-07 11:50 UTC (permalink / raw)
To: Dean Rasheed <dean.a.rasheed@gmail.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On 2023-Feb-27, Dean Rasheed wrote:
> What's happening is that (in session 2) the trigger code in
> ExecBRUpdateTriggers() detects the concurrent update and calls
> ExecGetUpdateNewTuple() to get a new tuple. However,
> ExecGetUpdateNewTuple() just calls internalGetUpdateNewTuple(), when
> really it ought to call mergeGetUpdateNewTuple(), since we're in a
> MERGE. Unfortunately there's no way for ExecGetUpdateNewTuple() to
> know that, because it doesn't have the required context information.
Hmm, right.
> A quick hack that fixes the problem is to pass the current
> MergeActionState to ExecGetUpdateNewTuple(), which also then requires
> that it be passed to ExecBRUpdateTriggers(). Changing the signature of
> 2 public functions in that way in back branches doesn't seem very nice
> though.
>
> OTOH, it's difficult to see how it can be fixed any other way.
>
> I don't really like this change to ExecGetUpdateNewTuple(), but if we
> are going to change it like this, then I think we should go all the
> way and get rid of the GetUpdateNewTuple callback, and the separate
> internalGetUpdateNewTuple() and mergeGetUpdateNewTuple() functions,
> and just do it all in ExecGetUpdateNewTuple(), so that all the code is
> in one place, making it easier to follow.
I'm pretty sure that I split things this way (using a callback) because
the MERGE code was at arms-length, being in a different file, and there
was no reasonable way, without duplicating a lot of other code, to
implement the different behavior other than using a callback.
I then moved the MERGE code from a separate file into nodeModifyTable.c
and could have put it back in one piece, but didn't remember that it was
there. It even looks like doing that may even make the code simpler, so
perhaps we should just do that -- nodeModifyTable.c if fully aware of
MERGE now, so it's not a problem.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Puedes vivir sólo una vez, pero si lo haces bien, una vez es suficiente"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-03-09 13:11 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 7+ messages in thread
From: Dean Rasheed @ 2023-03-09 13:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On Tue, 7 Mar 2023 at 11:50, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> > A quick hack that fixes the problem is to pass the current
> > MergeActionState to ExecGetUpdateNewTuple(), which also then requires
> > that it be passed to ExecBRUpdateTriggers(). Changing the signature of
> > 2 public functions in that way in back branches doesn't seem very nice
> > though.
> >
> > OTOH, it's difficult to see how it can be fixed any other way.
> >
> > I don't really like this change to ExecGetUpdateNewTuple(), but if we
> > are going to change it like this, then I think we should go all the
> > way and get rid of the GetUpdateNewTuple callback, and the separate
> > internalGetUpdateNewTuple() and mergeGetUpdateNewTuple() functions,
> > and just do it all in ExecGetUpdateNewTuple(), so that all the code is
> > in one place, making it easier to follow.
>
> I'm pretty sure that I split things this way (using a callback) because
> the MERGE code was at arms-length, being in a different file, and there
> was no reasonable way, without duplicating a lot of other code, to
> implement the different behavior other than using a callback.
>
> I then moved the MERGE code from a separate file into nodeModifyTable.c
> and could have put it back in one piece, but didn't remember that it was
> there. It even looks like doing that may even make the code simpler, so
> perhaps we should just do that -- nodeModifyTable.c if fully aware of
> MERGE now, so it's not a problem.
>
So I wrote a patch doing that, and added some isolation tests
confirming that it fixes the reported issue.
Unfortunately, I think this exposes a different issue -- if the target
table has a BEFORE ROW trigger, and the target tuple is concurrently
updated, the trigger code will lock the updated tuple, and the merge
code won't see it as a concurrent update, and so it won't re-check the
WHEN condition. That leads to some quite surprising results, as shown
in the new isolation test cases.
This makes me wonder if perhaps the merge code ought to lock the
target tuple before checking WHEN conditions, in the same way that the
trigger code does before checking trigger WHEN clauses. If it did
that, the trigger code would never have to re-compute the new tuple,
so this change to ExecBRUpdateTriggers() wouldn't be necessary. It
would also probably resolve the issue with cross-partition updates and
concurrently deleted tuples over on [1] and I think the requirement
for cpUpdateRetrySlot would go away. That seems like a pretty invasive
set of changes though.
[1] https://www.postgresql.org/message-id/20230215194224.egfyt3j5ewy4c6m3%40alvherre.pgsql
Regards,
Dean
Attachments:
bug-17809-v2.patch (text/x-patch, 16.8K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index 66401f2..c2a49b9
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2943,7 +2943,8 @@ ExecBRUpdateTriggers(EState *estate, EPQ
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
- TM_FailureData *tmfd)
+ TM_FailureData *tmfd,
+ MergeActionState *relaction)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
@@ -2988,7 +2989,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
TupleTableSlot *epqslot_clean;
epqslot_clean = ExecGetUpdateNewTuple(relinfo, epqslot_candidate,
- oldslot);
+ oldslot, relaction);
if (newslot != epqslot_clean)
ExecCopySlot(newslot, epqslot_clean);
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
new file mode 100644
index c484f5c..afc946b
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -486,7 +486,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
if (!ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
- tid, NULL, slot, NULL))
+ tid, NULL, slot, NULL, NULL))
skip_tuple = true; /* "do nothing" */
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 6f44d71..7660c57
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -88,15 +88,6 @@ typedef struct ModifyTableContext
*/
TupleTableSlot *planSlot;
- /*
- * During EvalPlanQual, project and return the new version of the new
- * tuple
- */
- TupleTableSlot *(*GetUpdateNewTuple) (ResultRelInfo *resultRelInfo,
- TupleTableSlot *epqslot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
-
/* MERGE specific */
MergeActionState *relaction; /* MERGE action in progress */
@@ -162,10 +153,6 @@ static TupleTableSlot *ExecPrepareTupleR
ResultRelInfo *targetRelInfo,
TupleTableSlot *slot,
ResultRelInfo **partRelInfo);
-static TupleTableSlot *internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
static TupleTableSlot *ExecMerge(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
@@ -179,10 +166,6 @@ static bool ExecMergeMatched(ModifyTable
static void ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
-static TupleTableSlot *mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
/*
@@ -736,30 +719,34 @@ ExecGetInsertNewTuple(ResultRelInfo *rel
TupleTableSlot *
ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot)
+ TupleTableSlot *oldSlot,
+ MergeActionState *relaction)
{
+ ProjectionInfo *newProj;
+ ExprContext *econtext;
+
/* Use a few extra Asserts to protect against outside callers */
Assert(relinfo->ri_projectNewInfoValid);
Assert(planSlot != NULL && !TTS_EMPTY(planSlot));
Assert(oldSlot != NULL && !TTS_EMPTY(oldSlot));
- return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
-}
-
-/*
- * Callback for ModifyTableState->GetUpdateNewTuple for use by regular UPDATE.
- */
-static TupleTableSlot *
-internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ProjectionInfo *newProj = relinfo->ri_projectNew;
- ExprContext *econtext;
-
- econtext = newProj->pi_exprContext;
- econtext->ecxt_outertuple = planSlot;
+ /*
+ * For MERGE, targetlist Vars in UPDATE actions use INNER_VAR to refer to
+ * the source relation (see setrefs.c), so we must use ecxt_innertuple for
+ * tuples from the subplan. Otherwise, we use ecxt_outertuple.
+ */
+ if (relaction)
+ {
+ newProj = relaction->mas_proj;
+ econtext = newProj->pi_exprContext;
+ econtext->ecxt_innertuple = planSlot;
+ }
+ else
+ {
+ newProj = relinfo->ri_projectNew;
+ econtext = newProj->pi_exprContext;
+ econtext->ecxt_outertuple = planSlot;
+ }
econtext->ecxt_scantuple = oldSlot;
return ExecProject(newProj);
}
@@ -1864,9 +1851,10 @@ ExecCrossPartitionUpdate(ModifyTableCont
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
/* and project the new tuple to retry the UPDATE with */
- context->cpUpdateRetrySlot =
- context->GetUpdateNewTuple(resultRelInfo, epqslot, oldSlot,
- context->relaction);
+ context->cpUpdateRetrySlot = ExecGetUpdateNewTuple(resultRelInfo,
+ epqslot,
+ oldSlot,
+ context->relaction);
return false;
}
}
@@ -1931,7 +1919,7 @@ ExecUpdatePrologue(ModifyTableContext *c
return ExecBRUpdateTriggers(context->estate, context->epqstate,
resultRelInfo, tupleid, oldtuple, slot,
- &context->tmfd);
+ &context->tmfd, context->relaction);
}
return true;
@@ -2430,7 +2418,7 @@ redo_act:
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
slot = ExecGetUpdateNewTuple(resultRelInfo,
- epqslot, oldSlot);
+ epqslot, oldSlot, NULL);
goto redo_act;
case TM_Deleted:
@@ -2902,7 +2890,6 @@ lmerge_matched:
newslot = ExecProject(relaction->mas_proj);
context->relaction = relaction;
- context->GetUpdateNewTuple = mergeGetUpdateNewTuple;
context->cpUpdateRetrySlot = NULL;
if (!ExecUpdatePrologue(context, resultRelInfo,
@@ -3397,25 +3384,6 @@ ExecInitMergeTupleSlots(ModifyTableState
}
/*
- * Callback for ModifyTableContext->GetUpdateNewTuple for use by MERGE. It
- * computes the updated tuple by projecting from the current merge action's
- * projection.
- */
-static TupleTableSlot *
-mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ExprContext *econtext = relaction->mas_proj->pi_exprContext;
-
- econtext->ecxt_scantuple = oldSlot;
- econtext->ecxt_innertuple = planSlot;
-
- return ExecProject(relaction->mas_proj);
-}
-
-/*
* Process BEFORE EACH STATEMENT triggers
*/
static void
@@ -3870,9 +3838,8 @@ ExecModifyTable(PlanState *pstate)
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
}
- slot = internalGetUpdateNewTuple(resultRelInfo, context.planSlot,
- oldSlot, NULL);
- context.GetUpdateNewTuple = internalGetUpdateNewTuple;
+ slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot,
+ oldSlot, NULL);
context.relaction = NULL;
/* Now apply the update. */
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index 67496d8..bad6818
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -232,7 +232,8 @@ extern bool ExecBRUpdateTriggers(EState
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
- TM_FailureData *tmfd);
+ TM_FailureData *tmfd,
+ MergeActionState *relaction);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
ResultRelInfo *src_partinfo,
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
new file mode 100644
index 946abc0..4ad3b6e
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -659,7 +659,8 @@ extern void CheckSubscriptionRelkind(cha
*/
extern TupleTableSlot *ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot);
+ TupleTableSlot *oldSlot,
+ MergeActionState *relaction);
extern ResultRelInfo *ExecLookupResultRelByOid(ModifyTableState *node,
Oid resultoid,
bool missing_ok,
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 8183f52..63e614e
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -1,4 +1,4 @@
-Parsed test spec with 2 sessions
+Parsed test spec with 3 sessions
starting permutation: update1 merge_status c2 select1 c1
step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
@@ -22,6 +22,44 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+s3: NOTICE: trigger "target_upd_trig" for relation "target" does not exist, skipping
+s3: NOTICE: function target_upd_trig_fn() does not exist, skipping
+
+starting permutation: trigger update1 merge_status c2 select1 c1
+step trigger:
+ CREATE FUNCTION target_upd_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ RAISE NOTICE 'Update target % -> %', OLD, NEW;
+ RETURN NEW;
+ END
+ $$;
+ CREATE TRIGGER target_upd_trig BEFORE UPDATE ON target
+ FOR EACH ROW EXECUTE FUNCTION target_upd_trig_fn();
+
+s2: NOTICE: Update target (1,160,s1,setup) -> (1,170,s1,"setup updated by update1")
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_status:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update target (1,170,s1,"setup updated by update1") -> (1,170,s2,"setup updated by update1 when1")
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+------------------------------
+ 1| 170|s2 |setup updated by update1 when1
+(1 row)
+
+step c1: COMMIT;
starting permutation: update2 merge_status c2 select1 c1
step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
@@ -45,6 +83,44 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+s3: NOTICE: trigger "target_upd_trig" for relation "target" does not exist, skipping
+s3: NOTICE: function target_upd_trig_fn() does not exist, skipping
+
+starting permutation: trigger update2 merge_status c2 select1 c1
+step trigger:
+ CREATE FUNCTION target_upd_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ RAISE NOTICE 'Update target % -> %', OLD, NEW;
+ RETURN NEW;
+ END
+ $$;
+ CREATE TRIGGER target_upd_trig BEFORE UPDATE ON target
+ FOR EACH ROW EXECUTE FUNCTION target_upd_trig_fn();
+
+s2: NOTICE: Update target (1,160,s1,setup) -> (1,160,s2,"setup updated by update2")
+step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
+step merge_status:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update target (1,160,s2,"setup updated by update2") -> (1,160,s2,"setup updated by update2 when1")
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+------------------------------
+ 1| 160|s2 |setup updated by update2 when1
+(1 row)
+
+step c1: COMMIT;
starting permutation: update3 merge_status c2 select1 c1
step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
@@ -68,6 +144,8 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+s3: NOTICE: trigger "target_upd_trig" for relation "target" does not exist, skipping
+s3: NOTICE: function target_upd_trig_fn() does not exist, skipping
starting permutation: update5 merge_status c2 select1 c1
step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
@@ -91,6 +169,8 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+s3: NOTICE: trigger "target_upd_trig" for relation "target" does not exist, skipping
+s3: NOTICE: function target_upd_trig_fn() does not exist, skipping
starting permutation: update_bal1 merge_bal c2 select1 c1
step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
@@ -114,3 +194,41 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+s3: NOTICE: trigger "target_upd_trig" for relation "target" does not exist, skipping
+s3: NOTICE: function target_upd_trig_fn() does not exist, skipping
+
+starting permutation: trigger update_bal1 merge_bal c2 select1 c1
+step trigger:
+ CREATE FUNCTION target_upd_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ RAISE NOTICE 'Update target % -> %', OLD, NEW;
+ RETURN NEW;
+ END
+ $$;
+ CREATE TRIGGER target_upd_trig BEFORE UPDATE ON target
+ FOR EACH ROW EXECUTE FUNCTION target_upd_trig_fn();
+
+s2: NOTICE: Update target (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1")
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_bal:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update target (1,50,s1,"setup updated by update_bal1") -> (1,200,s1,"setup updated by update_bal1 when2")
+step merge_bal: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+----------------------------------
+ 1| 200|s1 |setup updated by update_bal1 when2
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index d56400a..181a0a4
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -61,11 +61,38 @@ step "update5" { UPDATE target t SET sta
step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
step "c2" { COMMIT; }
+session "s3"
+step "trigger"
+{
+ CREATE FUNCTION target_upd_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ RAISE NOTICE 'Update target % -> %', OLD, NEW;
+ RETURN NEW;
+ END
+ $$;
+ CREATE TRIGGER target_upd_trig BEFORE UPDATE ON target
+ FOR EACH ROW EXECUTE FUNCTION target_upd_trig_fn();
+}
+
+teardown
+{
+ DROP TRIGGER IF EXISTS target_upd_trig ON target;
+ DROP FUNCTION IF EXISTS target_upd_trig_fn;
+}
+
# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
permutation "update1" "merge_status" "c2" "select1" "c1"
+# adding a before row trigger doesn't change result
+permutation "trigger" "update1" "merge_status" "c2" "select1" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
permutation "update2" "merge_status" "c2" "select1" "c1"
+# XXX: merge_status fires before row trigger which locks the concurrently
+# updated row after checking WHEN condition, so MERGE doesn't notice
+# the concurrent update and doesn't recheck the WHEN condition, so
+# final status = 's2'
+permutation "trigger" "update2" "merge_status" "c2" "select1" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
permutation "update3" "merge_status" "c2" "select1" "c1"
@@ -75,3 +102,9 @@ permutation "update5" "merge_status" "c2
# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
+
+# XXX: merge_bal fires before row trigger which locks the concurrently
+# updated row after checking WHEN condition, so MERGE doesn't notice
+# the concurrent update and doesn't recheck the WHEN condition, so
+# final balance = 200
+permutation "trigger" "update_bal1" "merge_bal" "c2" "select1" "c1"
* Re: BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-03-11 18:12 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dean Rasheed @ 2023-03-11 18:12 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On Thu, 9 Mar 2023 at 13:11, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> So I wrote a patch doing that, and added some isolation tests
> confirming that it fixes the reported issue.
>
> Unfortunately, I think this exposes a different issue -- if the target
> table has a BEFORE ROW trigger, and the target tuple is concurrently
> updated, the trigger code will lock the updated tuple, and the merge
> code won't see it as a concurrent update, and so it won't re-check the
> WHEN condition. That leads to some quite surprising results, as shown
> in the new isolation test cases.
>
I have been thinking about this some more, and I added a bunch of
additional isolation tests to test MERGE UPDATE/DELETE against
concurrent UPDATE/DELETE for normal tables, tables with BEFORE ROW
triggers, and partitioned tables where the MERGE does a
cross-partition update. Various of those tests currently fail, either
with the crash reported in this thread, or because they execute the
wrong merge action, or no action at all.
To fix this, it's necessary to pass the TM_Result and TM_FailureData
results from the trigger code's attempt to lock the old tuple, and
from the cross-partition-update code's attempt to delete the old
tuple, up to the merge code. This allows the merge code to handle
those cases in the same way as it does for non-partitioned tables
without triggers.
Overall, this leads to a few simplifications in nodeModifyTable.c:
1). ModifyTableContext->GetUpdateNewTuple() is now never called for
MERGE, so it and mergeGetUpdateNewTuple() can be deleted, and
ExecGetUpdateNewTuple() can be restored to how it was in v14.
2). ModifyTableContext->cpUpdateRetrySlot is no longer used by MERGE,
so it can also be deleted, and ExecCrossPartitionUpdate() can just
return retry_slot directly to ExecUpdateAct(), as it used to do.
3). ExecMergeMatched() becomes a bit simpler, since it no longer needs
special-case code for cross-partition updates.
Overall, this removes around 65 lines from nodeModifyTable.c, but more
importantly, it seems to fix the concurrent update issues.
Attached is an updated patch that I'm more happy with now, and a
slightly modified one for v15, keeping the trigger API
backwards-compatible for extensions.
Regards,
Dean
Attachments: download all (2 files, .tar.gz)
bug-17809-v3.patch (text/x-patch, 49.5K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index 66401f2..0f2de7e
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -87,6 +87,7 @@ static bool GetTupleForTrigger(EState *e
LockTupleMode lockmode,
TupleTableSlot *oldslot,
TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
TM_FailureData *tmfdp);
static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
Trigger *trigger, TriggerEvent event,
@@ -2694,7 +2695,9 @@ ExecBRDeleteTriggers(EState *estate, EPQ
ResultRelInfo *relinfo,
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
- TupleTableSlot **epqslot)
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd)
{
TupleTableSlot *slot = ExecGetTriggerOldSlot(estate, relinfo);
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2711,7 +2714,7 @@ ExecBRDeleteTriggers(EState *estate, EPQ
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
LockTupleExclusive, slot, &epqslot_candidate,
- NULL))
+ tmresult, tmfd))
return false;
/*
@@ -2802,6 +2805,7 @@ ExecARDeleteTriggers(EState *estate,
LockTupleExclusive,
slot,
NULL,
+ NULL,
NULL);
else
ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
@@ -2943,6 +2947,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
+ TM_Result *tmresult,
TM_FailureData *tmfd)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2967,7 +2972,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
/* get a copy of the on-disk tuple we are planning to update */
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
lockmode, oldslot, &epqslot_candidate,
- tmfd))
+ tmresult, tmfd))
return false; /* cancel the update action */
/*
@@ -3122,6 +3127,7 @@ ExecARUpdateTriggers(EState *estate, Res
LockTupleExclusive,
oldslot,
NULL,
+ NULL,
NULL);
else if (fdw_trigtuple != NULL)
ExecForceStoreHeapTuple(fdw_trigtuple, oldslot, false);
@@ -3277,6 +3283,7 @@ GetTupleForTrigger(EState *estate,
LockTupleMode lockmode,
TupleTableSlot *oldslot,
TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
TM_FailureData *tmfdp)
{
Relation relation = relinfo->ri_RelationDesc;
@@ -3304,6 +3311,8 @@ GetTupleForTrigger(EState *estate,
&tmfd);
/* Let the caller know about the status of this operation */
+ if (tmresultp)
+ *tmresultp = test;
if (tmfdp)
*tmfdp = tmfd;
@@ -3331,6 +3340,18 @@ GetTupleForTrigger(EState *estate,
case TM_Ok:
if (tmfd.traversed)
{
+ /*
+ * Recheck the tuple using EPQ. For MERGE, we leave this
+ * to the caller (it must do additional rechecking, and
+ * might end up executing a different action entirely).
+ */
+ if (estate->es_plannedstmt->commandType == CMD_MERGE)
+ {
+ if (tmresultp)
+ *tmresultp = TM_Updated;
+ return false;
+ }
+
*epqslot = EvalPlanQual(epqstate,
relation,
relinfo->ri_RangeTableIndex,
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
new file mode 100644
index c484f5c..4f5083a
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -486,7 +486,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
if (!ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
- tid, NULL, slot, NULL))
+ tid, NULL, slot, NULL, NULL))
skip_tuple = true; /* "do nothing" */
}
@@ -547,7 +547,7 @@ ExecSimpleRelationDelete(ResultRelInfo *
resultRelInfo->ri_TrigDesc->trig_delete_before_row)
{
skip_tuple = !ExecBRDeleteTriggers(estate, epqstate, resultRelInfo,
- tid, NULL, NULL);
+ tid, NULL, NULL, NULL, NULL);
}
if (!skip_tuple)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 6f44d71..b8201a0
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -88,15 +88,6 @@ typedef struct ModifyTableContext
*/
TupleTableSlot *planSlot;
- /*
- * During EvalPlanQual, project and return the new version of the new
- * tuple
- */
- TupleTableSlot *(*GetUpdateNewTuple) (ResultRelInfo *resultRelInfo,
- TupleTableSlot *epqslot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
-
/* MERGE specific */
MergeActionState *relaction; /* MERGE action in progress */
@@ -107,12 +98,6 @@ typedef struct ModifyTableContext
TM_FailureData tmfd;
/*
- * The tuple produced by EvalPlanQual to retry from, if a cross-partition
- * UPDATE requires it
- */
- TupleTableSlot *cpUpdateRetrySlot;
-
- /*
* The tuple projected by the INSERT's RETURNING clause, when doing a
* cross-partition UPDATE
*/
@@ -162,10 +147,6 @@ static TupleTableSlot *ExecPrepareTupleR
ResultRelInfo *targetRelInfo,
TupleTableSlot *slot,
ResultRelInfo **partRelInfo);
-static TupleTableSlot *internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
static TupleTableSlot *ExecMerge(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
@@ -179,10 +160,6 @@ static bool ExecMergeMatched(ModifyTable
static void ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
-static TupleTableSlot *mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
/*
@@ -738,26 +715,14 @@ ExecGetUpdateNewTuple(ResultRelInfo *rel
TupleTableSlot *planSlot,
TupleTableSlot *oldSlot)
{
+ ProjectionInfo *newProj = relinfo->ri_projectNew;
+ ExprContext *econtext;
+
/* Use a few extra Asserts to protect against outside callers */
Assert(relinfo->ri_projectNewInfoValid);
Assert(planSlot != NULL && !TTS_EMPTY(planSlot));
Assert(oldSlot != NULL && !TTS_EMPTY(oldSlot));
- return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
-}
-
-/*
- * Callback for ModifyTableState->GetUpdateNewTuple for use by regular UPDATE.
- */
-static TupleTableSlot *
-internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ProjectionInfo *newProj = relinfo->ri_projectNew;
- ExprContext *econtext;
-
econtext = newProj->pi_exprContext;
econtext->ecxt_outertuple = planSlot;
econtext->ecxt_scantuple = oldSlot;
@@ -1336,8 +1301,11 @@ ExecPendingInserts(EState *estate)
static bool
ExecDeletePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple,
- TupleTableSlot **epqreturnslot)
+ TupleTableSlot **epqreturnslot, TM_Result *result)
{
+ if (result)
+ *result = TM_Ok;
+
/* BEFORE ROW DELETE triggers */
if (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_delete_before_row)
@@ -1348,7 +1316,7 @@ ExecDeletePrologue(ModifyTableContext *c
return ExecBRDeleteTriggers(context->estate, context->epqstate,
resultRelInfo, tupleid, oldtuple,
- epqreturnslot);
+ epqreturnslot, result, &context->tmfd);
}
return true;
@@ -1465,7 +1433,7 @@ ExecDelete(ModifyTableContext *context,
* done if it says we are.
*/
if (!ExecDeletePrologue(context, resultRelInfo, tupleid, oldtuple,
- epqreturnslot))
+ epqreturnslot, NULL))
return NULL;
/* INSTEAD OF ROW DELETE Triggers */
@@ -1746,8 +1714,10 @@ ldelete:
*
* False is returned if the tuple we're trying to move is found to have been
* concurrently updated. In that case, the caller must check if the updated
- * tuple (in updateCxt->cpUpdateRetrySlot) still needs to be re-routed, and
- * call this function again or perform a regular update accordingly.
+ * tuple that's returned in *retry_slot still needs to be re-routed, and call
+ * this function again or perform a regular update accordingly. For MERGE,
+ * the updated tuple is not returned in *retry_slot; it has its own retry
+ * logic.
*/
static bool
ExecCrossPartitionUpdate(ModifyTableContext *context,
@@ -1756,6 +1726,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *slot,
bool canSetTag,
UpdateContext *updateCxt,
+ TupleTableSlot **retry_slot,
TupleTableSlot **inserted_tuple,
ResultRelInfo **insert_destrel)
{
@@ -1766,7 +1737,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *epqslot = NULL;
context->cpUpdateReturningSlot = NULL;
- context->cpUpdateRetrySlot = NULL;
+ *retry_slot = NULL;
/*
* Disallow an INSERT ON CONFLICT DO UPDATE that causes the original row
@@ -1845,9 +1816,13 @@ ExecCrossPartitionUpdate(ModifyTableCont
* another transaction has concurrently updated the same row, it
* re-fetches the row, skips the delete, and epqslot is set to the
* re-fetched tuple slot. In that case, we need to do all the checks
- * again.
+ * again. For MERGE, we leave everything to the caller (it must do
+ * additional rechecking, and might end up executing a different
+ * action entirely).
*/
- if (TupIsNull(epqslot))
+ if (context->relaction != NULL)
+ return false;
+ else if (TupIsNull(epqslot))
return true;
else
{
@@ -1864,9 +1839,8 @@ ExecCrossPartitionUpdate(ModifyTableCont
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
/* and project the new tuple to retry the UPDATE with */
- context->cpUpdateRetrySlot =
- context->GetUpdateNewTuple(resultRelInfo, epqslot, oldSlot,
- context->relaction);
+ *retry_slot = ExecGetUpdateNewTuple(resultRelInfo, epqslot,
+ oldSlot);
return false;
}
}
@@ -1907,10 +1881,14 @@ ExecCrossPartitionUpdate(ModifyTableCont
*/
static bool
ExecUpdatePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot)
+ ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
+ TM_Result *result)
{
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
+ if (result)
+ *result = TM_Ok;
+
ExecMaterializeSlot(slot);
/*
@@ -1931,7 +1909,7 @@ ExecUpdatePrologue(ModifyTableContext *c
return ExecBRUpdateTriggers(context->estate, context->epqstate,
resultRelInfo, tupleid, oldtuple, slot,
- &context->tmfd);
+ result, &context->tmfd);
}
return true;
@@ -2032,7 +2010,8 @@ lreplace:
*/
if (partition_constraint_failed)
{
- TupleTableSlot *inserted_tuple;
+ TupleTableSlot *inserted_tuple,
+ *retry_slot;
ResultRelInfo *insert_destrel = NULL;
/*
@@ -2044,6 +2023,7 @@ lreplace:
if (ExecCrossPartitionUpdate(context, resultRelInfo,
tupleid, oldtuple, slot,
canSetTag, updateCxt,
+ &retry_slot,
&inserted_tuple,
&insert_destrel))
{
@@ -2088,7 +2068,7 @@ lreplace:
* ExecCrossPartitionUpdate installed an updated version of the new
* tuple in the retry slot; start over.
*/
- slot = context->cpUpdateRetrySlot;
+ slot = retry_slot;
goto lreplace;
}
@@ -2285,7 +2265,7 @@ ExecUpdate(ModifyTableContext *context,
* Prepare for the update. This includes BEFORE ROW triggers, so we're
* done if it says we are.
*/
- if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot))
+ if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
return NULL;
/* INSTEAD OF ROW UPDATE Triggers */
@@ -2902,13 +2882,10 @@ lmerge_matched:
newslot = ExecProject(relaction->mas_proj);
context->relaction = relaction;
- context->GetUpdateNewTuple = mergeGetUpdateNewTuple;
- context->cpUpdateRetrySlot = NULL;
-
if (!ExecUpdatePrologue(context, resultRelInfo,
- tupleid, NULL, newslot))
+ tupleid, NULL, newslot, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
@@ -2919,15 +2896,14 @@ lmerge_matched:
tupleid, NULL, newslot, recheckIndexes);
mtstate->mt_merge_updated += 1;
}
-
break;
case CMD_DELETE:
context->relaction = relaction;
if (!ExecDeletePrologue(context, resultRelInfo, tupleid,
- NULL, NULL))
+ NULL, NULL, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecDeleteAct(context, resultRelInfo, tupleid, false);
@@ -2994,34 +2970,13 @@ lmerge_matched:
/*
* The target tuple was concurrently updated by some other
- * transaction.
- */
-
- /*
- * During an UPDATE, if cpUpdateRetrySlot is set, then
- * ExecCrossPartitionUpdate() must have detected that the
- * tuple was concurrently updated, so we restart the
- * search for an appropriate WHEN MATCHED clause to
- * process the updated tuple.
- *
- * In this case, ExecDelete() would already have performed
- * EvalPlanQual() on the latest version of the tuple,
- * which in turn would already have been loaded into
- * ri_oldTupleSlot, so no need to do either of those
- * things.
- */
- if (commandType == CMD_UPDATE &&
- !TupIsNull(context->cpUpdateRetrySlot))
- goto lmerge_matched;
-
- /*
- * Otherwise, we run the EvalPlanQual() with the new
- * version of the tuple. If EvalPlanQual() does not return
- * a tuple, then we switch to the NOT MATCHED list of
- * actions. If it does return a tuple and the join qual is
- * still satisfied, then we just need to recheck the
- * MATCHED actions, starting from the top, and execute the
- * first qualifying action.
+ * transaction. Run EvalPlanQual() with the new version of
+ * the tuple. If it does not return a tuple, then we
+ * switch to the NOT MATCHED list of actions. If it does
+ * return a tuple and the join qual is still satisfied,
+ * then we just need to recheck the MATCHED actions,
+ * starting from the top, and execute the first qualifying
+ * action.
*/
resultRelationDesc = resultRelInfo->ri_RelationDesc;
lockmode = ExecUpdateLockMode(estate, resultRelInfo);
@@ -3397,25 +3352,6 @@ ExecInitMergeTupleSlots(ModifyTableState
}
/*
- * Callback for ModifyTableContext->GetUpdateNewTuple for use by MERGE. It
- * computes the updated tuple by projecting from the current merge action's
- * projection.
- */
-static TupleTableSlot *
-mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ExprContext *econtext = relaction->mas_proj->pi_exprContext;
-
- econtext->ecxt_scantuple = oldSlot;
- econtext->ecxt_innertuple = planSlot;
-
- return ExecProject(relaction->mas_proj);
-}
-
-/*
* Process BEFORE EACH STATEMENT triggers
*/
static void
@@ -3870,9 +3806,8 @@ ExecModifyTable(PlanState *pstate)
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
}
- slot = internalGetUpdateNewTuple(resultRelInfo, context.planSlot,
- oldSlot, NULL);
- context.GetUpdateNewTuple = internalGetUpdateNewTuple;
+ slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot,
+ oldSlot);
context.relaction = NULL;
/* Now apply the update. */
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index 67496d8..430e3ca
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -211,7 +211,9 @@ extern bool ExecBRDeleteTriggers(EState
ResultRelInfo *relinfo,
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
- TupleTableSlot **epqslot);
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd);
extern void ExecARDeleteTriggers(EState *estate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
@@ -232,6 +234,7 @@ extern bool ExecBRUpdateTriggers(EState
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
+ TM_Result *tmresult,
TM_FailureData *tmfd);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
new file mode 100644
index b2befa8..897b935
--- a/src/test/isolation/expected/merge-delete.out
+++ b/src/test/isolation/expected/merge-delete.out
@@ -10,20 +10,31 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step select2: SELECT * FROM target;
+step select2_pa: SELECT * FROM target_pa;
key|val
---+---
(0 rows)
step c2: COMMIT;
-starting permutation: delete c1 update1 select2 c2
+starting permutation: delete_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 update2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1;
step select2: SELECT * FROM target;
key|val
---+---
@@ -31,11 +42,23 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 update1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 update2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
-step select2: SELECT * FROM target;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 update2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -45,32 +68,72 @@ step c2: COMMIT;
starting permutation: delete c1 merge2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete c1 merge2 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 merge2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 merge2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 merge_delete2 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
(1 row)
step c2: COMMIT;
-starting permutation: delete update1 c1 select2 c2
+starting permutation: delete_tg c1 merge_delete2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete update2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
+step update2: <... completed>
step select2: SELECT * FROM target;
key|val
---+---
@@ -78,12 +141,25 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete update1 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+starting permutation: delete_pa update2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
-step select2: SELECT * FROM target;
+step update2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg update2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -92,26 +168,69 @@ step c2: COMMIT;
starting permutation: delete merge2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
step merge2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete merge2 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+starting permutation: delete_pa merge2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
-step merge2: <... completed>
+step merge2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete merge_delete2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+step merge_delete2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge_delete2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
(1 row)
step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 8183f52..9a44a59
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -23,6 +23,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update1_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,170,s1,"setup updated by update1_tg") -> (1,170,s2,"setup updated by update1_tg when1")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 170|s2 |setup updated by update1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update2 merge_status c2 select1 c1
step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
step merge_status:
@@ -46,6 +71,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update2_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s2,"setup updated by update2_tg")
+step update2_tg: UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s2,"setup updated by update2_tg") -> (1,160,s3,"setup updated by update2_tg when2")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s3 |setup updated by update2_tg when2
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update3 merge_status c2 select1 c1
step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
step merge_status:
@@ -69,6 +119,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update3_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s3,"setup updated by update3_tg")
+step update3_tg: UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s3,"setup updated by update3_tg") -> (1,160,s4,"setup updated by update3_tg when3")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s4 |setup updated by update3_tg when3
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update5 merge_status c2 select1 c1
step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
step merge_status:
@@ -92,6 +167,30 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update5_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s5,"setup updated by update5_tg")
+step update5_tg: UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------
+ 1| 160|s5 |setup updated by update5_tg
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update_bal1 merge_bal c2 select1 c1
step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
step merge_bal:
@@ -114,3 +213,137 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+
+starting permutation: update_bal1_pa merge_bal_pa c2 select1_pa c1
+step update_bal1_pa: UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1;
+step merge_bal_pa:
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal_pa: <... completed>
+step select1_pa: SELECT * FROM target_pa;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_pa when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_bal_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_bal_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_bal_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update1 merge_delete c2 select1 c1
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Delete: (1,170,s1,"setup updated by update1_tg")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1 merge_delete c2 select1 c1
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+----------------------------------
+ 1| 100|s1 |setup updated by update_bal1 when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
new file mode 100644
index 0e70532..ba5f70e
--- a/src/test/isolation/specs/merge-delete.spec
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -7,11 +7,39 @@ setup
{
CREATE TABLE target (key int primary key, val text);
INSERT INTO target VALUES (1, 'setup1');
+
+ CREATE TABLE target_pa (key int primary key, val text) PARTITION BY LIST (key);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES IN (1);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES IN (2);
+ INSERT INTO target_pa VALUES (1, 'setup1');
+
+ CREATE TABLE target_tg (key int primary key, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 'setup1');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -20,7 +48,8 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "delete" { DELETE FROM target t WHERE t.key = 1; }
-step "merge_delete" { MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE; }
+step "delete_pa" { DELETE FROM target_pa t WHERE t.key = 1; }
+step "delete_tg" { DELETE FROM target_tg t WHERE t.key = 1; }
step "c1" { COMMIT; }
session "s2"
@@ -28,23 +57,40 @@ setup
{
BEGIN ISOLATION LEVEL READ COMMITTED;
}
-step "update1" { UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; }
-step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "update2" { UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_pa" { UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_pa" { MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge_delete2" { MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
+step "merge_delete2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
step "select2" { SELECT * FROM target; }
+step "select2_pa" { SELECT * FROM target_pa; }
+step "select2_tg" { SELECT * FROM target_tg; }
step "c2" { COMMIT; }
# Basic effects
permutation "delete" "c1" "select2" "c2"
-permutation "merge_delete" "c1" "select2" "c2"
+permutation "delete_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "c1" "select2_tg" "c2"
# One after the other, no concurrency
-permutation "delete" "c1" "update1" "select2" "c2"
-permutation "merge_delete" "c1" "update1" "select2" "c2"
+permutation "delete" "c1" "update2" "select2" "c2"
+permutation "delete_pa" "c1" "update2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "update2_tg" "select2_tg" "c2"
permutation "delete" "c1" "merge2" "select2" "c2"
-permutation "merge_delete" "c1" "merge2" "select2" "c2"
+permutation "delete_pa" "c1" "merge2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "merge2_tg" "select2_tg" "c2"
+permutation "delete" "c1" "merge_delete2" "select2" "c2"
+permutation "delete_tg" "c1" "merge_delete2_tg" "select2_tg" "c2"
# Now with concurrency
-permutation "delete" "update1" "c1" "select2" "c2"
-permutation "merge_delete" "update1" "c1" "select2" "c2"
+permutation "delete" "update2" "c1" "select2" "c2"
+permutation "delete_pa" "update2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "update2_tg" "c1" "select2_tg" "c2"
permutation "delete" "merge2" "c1" "select2" "c2"
-permutation "merge_delete" "merge2" "c1" "select2" "c2"
+permutation "delete_pa" "merge2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "merge2_tg" "c1" "select2_tg" "c2"
+permutation "delete" "merge_delete2" "c1" "select2" "c2"
+permutation "delete_tg" "merge_delete2_tg" "c1" "select2_tg" "c2"
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index d56400a..298b2bf
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -8,11 +8,39 @@ setup
{
CREATE TABLE target (key int primary key, balance integer, status text, val text);
INSERT INTO target VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_pa (key int, balance integer, status text, val text) PARTITION BY RANGE (balance);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES FROM (0) TO (200);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES FROM (200) TO (1000);
+ INSERT INTO target_pa VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_tg (key int primary key, balance integer, status text, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 160, 's1', 'setup');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -32,6 +60,18 @@ step "merge_status"
WHEN MATCHED AND status = 's3' THEN
UPDATE SET status = 's4', val = t.val || ' when3';
}
+step "merge_status_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+}
step "merge_bal"
{
@@ -45,8 +85,55 @@ step "merge_bal"
WHEN MATCHED AND balance < 300 THEN
UPDATE SET balance = balance * 8, val = t.val || ' when3';
}
+step "merge_bal_pa"
+{
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+step "merge_bal_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+
+step "merge_delete"
+{
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
+step "merge_delete_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
step "select1" { SELECT * FROM target; }
+step "select1_pa" { SELECT * FROM target_pa; }
+step "select1_tg" { SELECT * FROM target_tg; }
step "c1" { COMMIT; }
session "s2"
@@ -55,23 +142,43 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "update1" { UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "update1_tg" { UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1; }
step "update2" { UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
step "update3" { UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1; }
+step "update3_tg" { UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1; }
step "update5" { UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1; }
+step "update5_tg" { UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1; }
step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
+step "update_bal1_pa" { UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1; }
+step "update_bal1_tg" { UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1; }
step "c2" { COMMIT; }
# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
permutation "update1" "merge_status" "c2" "select1" "c1"
+permutation "update1_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
permutation "update2" "merge_status" "c2" "select1" "c1"
+permutation "update2_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
permutation "update3" "merge_status" "c2" "select1" "c1"
+permutation "update3_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, but we skip update and MERGE does nothing
permutation "update5" "merge_status" "c2" "select1" "c1"
+permutation "update5_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
+permutation "update_bal1_pa" "merge_bal_pa" "c2" "select1_pa" "c1"
+permutation "update_bal1_tg" "merge_bal_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, but recheck passes and row is deleted
+permutation "update1" "merge_delete" "c2" "select1" "c1"
+permutation "update1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance is 100
+permutation "update_bal1" "merge_delete" "c2" "select1" "c1"
+permutation "update_bal1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
bug-17809-v3-15.patch (text/x-patch, 51.7K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index d9d9201..7326ece
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -84,8 +84,9 @@ static bool GetTupleForTrigger(EState *e
ItemPointer tid,
LockTupleMode lockmode,
TupleTableSlot *oldslot,
- TupleTableSlot **newSlot,
- TM_FailureData *tmfpd);
+ TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
+ TM_FailureData *tmfdp);
static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
Trigger *trigger, TriggerEvent event,
Bitmapset *modifiedCols,
@@ -2753,11 +2754,13 @@ ExecASDeleteTriggers(EState *estate, Res
* back the concurrently updated tuple if any.
*/
bool
-ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
- ResultRelInfo *relinfo,
- ItemPointer tupleid,
- HeapTuple fdw_trigtuple,
- TupleTableSlot **epqslot)
+ExecBRDeleteTriggersNew(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd)
{
TupleTableSlot *slot = ExecGetTriggerOldSlot(estate, relinfo);
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2774,7 +2777,7 @@ ExecBRDeleteTriggers(EState *estate, EPQ
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
LockTupleExclusive, slot, &epqslot_candidate,
- NULL))
+ tmresult, tmfd))
return false;
/*
@@ -2838,6 +2841,21 @@ ExecBRDeleteTriggers(EState *estate, EPQ
}
/*
+ * ABI-compatible wrapper to emulate old version of the above function.
+ * Do not call this version in new code.
+ */
+bool
+ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot **epqslot)
+{
+ return ExecBRDeleteTriggersNew(estate, epqstate, relinfo, tupleid,
+ fdw_trigtuple, epqslot, NULL, NULL);
+}
+
+/*
* Note: is_crosspart_update must be true if the DELETE is being performed
* as part of a cross-partition update.
*/
@@ -2865,6 +2883,7 @@ ExecARDeleteTriggers(EState *estate,
LockTupleExclusive,
slot,
NULL,
+ NULL,
NULL);
else
ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
@@ -3001,12 +3020,13 @@ ExecASUpdateTriggers(EState *estate, Res
}
bool
-ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
- ResultRelInfo *relinfo,
- ItemPointer tupleid,
- HeapTuple fdw_trigtuple,
- TupleTableSlot *newslot,
- TM_FailureData *tmfd)
+ExecBRUpdateTriggersNew(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot *newslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
@@ -3030,7 +3050,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
/* get a copy of the on-disk tuple we are planning to update */
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
lockmode, oldslot, &epqslot_candidate,
- tmfd))
+ tmresult, tmfd))
return false; /* cancel the update action */
/*
@@ -3135,6 +3155,22 @@ ExecBRUpdateTriggers(EState *estate, EPQ
}
/*
+ * ABI-compatible wrapper to emulate old version of the above function.
+ * Do not call this version in new code.
+ */
+bool
+ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot *newslot,
+ TM_FailureData *tmfd)
+{
+ return ExecBRUpdateTriggersNew(estate, epqstate, relinfo, tupleid,
+ fdw_trigtuple, newslot, NULL, tmfd);
+}
+
+/*
* Note: 'src_partinfo' and 'dst_partinfo', when non-NULL, refer to the source
* and destination partitions, respectively, of a cross-partition update of
* the root partitioned table mentioned in the query, given by 'relinfo'.
@@ -3185,6 +3221,7 @@ ExecARUpdateTriggers(EState *estate, Res
LockTupleExclusive,
oldslot,
NULL,
+ NULL,
NULL);
else if (fdw_trigtuple != NULL)
ExecForceStoreHeapTuple(fdw_trigtuple, oldslot, false);
@@ -3340,6 +3377,7 @@ GetTupleForTrigger(EState *estate,
LockTupleMode lockmode,
TupleTableSlot *oldslot,
TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
TM_FailureData *tmfdp)
{
Relation relation = relinfo->ri_RelationDesc;
@@ -3367,6 +3405,8 @@ GetTupleForTrigger(EState *estate,
&tmfd);
/* Let the caller know about the status of this operation */
+ if (tmresultp)
+ *tmresultp = test;
if (tmfdp)
*tmfdp = tmfd;
@@ -3394,6 +3434,18 @@ GetTupleForTrigger(EState *estate,
case TM_Ok:
if (tmfd.traversed)
{
+ /*
+ * Recheck the tuple using EPQ. For MERGE, we leave this
+ * to the caller (it must do additional rechecking, and
+ * might end up executing a different action entirely).
+ */
+ if (estate->es_plannedstmt->commandType == CMD_MERGE)
+ {
+ if (tmresultp)
+ *tmresultp = TM_Updated;
+ return false;
+ }
+
*epqslot = EvalPlanQual(epqstate,
relation,
relinfo->ri_RangeTableIndex,
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 802bea0..151a3e6
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -88,15 +88,6 @@ typedef struct ModifyTableContext
*/
TupleTableSlot *planSlot;
- /*
- * During EvalPlanQual, project and return the new version of the new
- * tuple
- */
- TupleTableSlot *(*GetUpdateNewTuple) (ResultRelInfo *resultRelInfo,
- TupleTableSlot *epqslot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
-
/* MERGE specific */
MergeActionState *relaction; /* MERGE action in progress */
@@ -107,12 +98,6 @@ typedef struct ModifyTableContext
TM_FailureData tmfd;
/*
- * The tuple produced by EvalPlanQual to retry from, if a cross-partition
- * UPDATE requires it
- */
- TupleTableSlot *cpUpdateRetrySlot;
-
- /*
* The tuple projected by the INSERT's RETURNING clause, when doing a
* cross-partition UPDATE
*/
@@ -162,10 +147,6 @@ static TupleTableSlot *ExecPrepareTupleR
ResultRelInfo *targetRelInfo,
TupleTableSlot *slot,
ResultRelInfo **partRelInfo);
-static TupleTableSlot *internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
static TupleTableSlot *ExecMerge(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
@@ -179,10 +160,6 @@ static bool ExecMergeMatched(ModifyTable
static void ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
-static TupleTableSlot *mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
/*
@@ -719,26 +696,14 @@ ExecGetUpdateNewTuple(ResultRelInfo *rel
TupleTableSlot *planSlot,
TupleTableSlot *oldSlot)
{
+ ProjectionInfo *newProj = relinfo->ri_projectNew;
+ ExprContext *econtext;
+
/* Use a few extra Asserts to protect against outside callers */
Assert(relinfo->ri_projectNewInfoValid);
Assert(planSlot != NULL && !TTS_EMPTY(planSlot));
Assert(oldSlot != NULL && !TTS_EMPTY(oldSlot));
- return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
-}
-
-/*
- * Callback for ModifyTableState->GetUpdateNewTuple for use by regular UPDATE.
- */
-static TupleTableSlot *
-internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ProjectionInfo *newProj = relinfo->ri_projectNew;
- ExprContext *econtext;
-
econtext = newProj->pi_exprContext;
econtext->ecxt_outertuple = planSlot;
econtext->ecxt_scantuple = oldSlot;
@@ -1317,8 +1282,11 @@ ExecPendingInserts(EState *estate)
static bool
ExecDeletePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple,
- TupleTableSlot **epqreturnslot)
+ TupleTableSlot **epqreturnslot, TM_Result *result)
{
+ if (result)
+ *result = TM_Ok;
+
/* BEFORE ROW DELETE triggers */
if (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_delete_before_row)
@@ -1327,9 +1295,9 @@ ExecDeletePrologue(ModifyTableContext *c
if (context->estate->es_insert_pending_result_relations != NIL)
ExecPendingInserts(context->estate);
- return ExecBRDeleteTriggers(context->estate, context->epqstate,
- resultRelInfo, tupleid, oldtuple,
- epqreturnslot);
+ return ExecBRDeleteTriggersNew(context->estate, context->epqstate,
+ resultRelInfo, tupleid, oldtuple,
+ epqreturnslot, result, &context->tmfd);
}
return true;
@@ -1446,7 +1414,7 @@ ExecDelete(ModifyTableContext *context,
* done if it says we are.
*/
if (!ExecDeletePrologue(context, resultRelInfo, tupleid, oldtuple,
- epqreturnslot))
+ epqreturnslot, NULL))
return NULL;
/* INSTEAD OF ROW DELETE Triggers */
@@ -1727,8 +1695,10 @@ ldelete:;
*
* False is returned if the tuple we're trying to move is found to have been
* concurrently updated. In that case, the caller must check if the updated
- * tuple (in updateCxt->cpUpdateRetrySlot) still needs to be re-routed, and
- * call this function again or perform a regular update accordingly.
+ * tuple that's returned in *retry_slot still needs to be re-routed, and call
+ * this function again or perform a regular update accordingly. For MERGE,
+ * the updated tuple is not returned in *retry_slot; it has its own retry
+ * logic.
*/
static bool
ExecCrossPartitionUpdate(ModifyTableContext *context,
@@ -1737,6 +1707,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *slot,
bool canSetTag,
UpdateContext *updateCxt,
+ TupleTableSlot **retry_slot,
TupleTableSlot **inserted_tuple,
ResultRelInfo **insert_destrel)
{
@@ -1747,7 +1718,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *epqslot = NULL;
context->cpUpdateReturningSlot = NULL;
- context->cpUpdateRetrySlot = NULL;
+ *retry_slot = NULL;
/*
* Disallow an INSERT ON CONFLICT DO UPDATE that causes the original row
@@ -1826,9 +1797,13 @@ ExecCrossPartitionUpdate(ModifyTableCont
* another transaction has concurrently updated the same row, it
* re-fetches the row, skips the delete, and epqslot is set to the
* re-fetched tuple slot. In that case, we need to do all the checks
- * again.
+ * again. For MERGE, we leave everything to the caller (it must do
+ * additional rechecking, and might end up executing a different
+ * action entirely).
*/
- if (TupIsNull(epqslot))
+ if (context->relaction != NULL)
+ return false;
+ else if (TupIsNull(epqslot))
return true;
else
{
@@ -1845,9 +1820,8 @@ ExecCrossPartitionUpdate(ModifyTableCont
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
/* and project the new tuple to retry the UPDATE with */
- context->cpUpdateRetrySlot =
- context->GetUpdateNewTuple(resultRelInfo, epqslot, oldSlot,
- context->relaction);
+ *retry_slot = ExecGetUpdateNewTuple(resultRelInfo, epqslot,
+ oldSlot);
return false;
}
}
@@ -1888,10 +1862,14 @@ ExecCrossPartitionUpdate(ModifyTableCont
*/
static bool
ExecUpdatePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot)
+ ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
+ TM_Result *result)
{
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
+ if (result)
+ *result = TM_Ok;
+
ExecMaterializeSlot(slot);
/*
@@ -1910,9 +1888,9 @@ ExecUpdatePrologue(ModifyTableContext *c
if (context->estate->es_insert_pending_result_relations != NIL)
ExecPendingInserts(context->estate);
- return ExecBRUpdateTriggers(context->estate, context->epqstate,
- resultRelInfo, tupleid, oldtuple, slot,
- &context->tmfd);
+ return ExecBRUpdateTriggersNew(context->estate, context->epqstate,
+ resultRelInfo, tupleid, oldtuple, slot,
+ result, &context->tmfd);
}
return true;
@@ -2016,7 +1994,8 @@ lreplace:
*/
if (partition_constraint_failed)
{
- TupleTableSlot *inserted_tuple;
+ TupleTableSlot *inserted_tuple,
+ *retry_slot;
ResultRelInfo *insert_destrel = NULL;
/*
@@ -2028,6 +2007,7 @@ lreplace:
if (ExecCrossPartitionUpdate(context, resultRelInfo,
tupleid, oldtuple, slot,
canSetTag, updateCxt,
+ &retry_slot,
&inserted_tuple,
&insert_destrel))
{
@@ -2072,7 +2052,7 @@ lreplace:
* ExecCrossPartitionUpdate installed an updated version of the new
* tuple in the retry slot; start over.
*/
- slot = context->cpUpdateRetrySlot;
+ slot = retry_slot;
goto lreplace;
}
@@ -2269,7 +2249,7 @@ ExecUpdate(ModifyTableContext *context,
* Prepare for the update. This includes BEFORE ROW triggers, so we're
* done if it says we are.
*/
- if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot))
+ if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
return NULL;
/* INSTEAD OF ROW UPDATE Triggers */
@@ -2886,13 +2866,10 @@ lmerge_matched:;
newslot = ExecProject(relaction->mas_proj);
context->relaction = relaction;
- context->GetUpdateNewTuple = mergeGetUpdateNewTuple;
- context->cpUpdateRetrySlot = NULL;
-
if (!ExecUpdatePrologue(context, resultRelInfo,
- tupleid, NULL, newslot))
+ tupleid, NULL, newslot, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
@@ -2903,15 +2880,14 @@ lmerge_matched:;
tupleid, NULL, newslot, recheckIndexes);
mtstate->mt_merge_updated += 1;
}
-
break;
case CMD_DELETE:
context->relaction = relaction;
if (!ExecDeletePrologue(context, resultRelInfo, tupleid,
- NULL, NULL))
+ NULL, NULL, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecDeleteAct(context, resultRelInfo, tupleid, false);
@@ -2978,34 +2954,13 @@ lmerge_matched:;
/*
* The target tuple was concurrently updated by some other
- * transaction.
- */
-
- /*
- * During an UPDATE, if cpUpdateRetrySlot is set, then
- * ExecCrossPartitionUpdate() must have detected that the
- * tuple was concurrently updated, so we restart the search
- * for an appropriate WHEN MATCHED clause to process the
- * updated tuple.
- *
- * In this case, ExecDelete() would already have performed
- * EvalPlanQual() on the latest version of the tuple,
- * which in turn would already have been loaded into
- * ri_oldTupleSlot, so no need to do either of those
- * things.
- */
- if (commandType == CMD_UPDATE &&
- !TupIsNull(context->cpUpdateRetrySlot))
- goto lmerge_matched;
-
- /*
- * Otherwise, we run the EvalPlanQual() with the new
- * version of the tuple. If EvalPlanQual() does not return
- * a tuple, then we switch to the NOT MATCHED list of
- * actions. If it does return a tuple and the join qual is
- * still satisfied, then we just need to recheck the
- * MATCHED actions, starting from the top, and execute the
- * first qualifying action.
+ * transaction. Run EvalPlanQual() with the new version of
+ * the tuple. If it does not return a tuple, then we
+ * switch to the NOT MATCHED list of actions. If it does
+ * return a tuple and the join qual is still satisfied,
+ * then we just need to recheck the MATCHED actions,
+ * starting from the top, and execute the first qualifying
+ * action.
*/
resultRelationDesc = resultRelInfo->ri_RelationDesc;
lockmode = ExecUpdateLockMode(estate, resultRelInfo);
@@ -3381,25 +3336,6 @@ ExecInitMergeTupleSlots(ModifyTableState
}
/*
- * Callback for ModifyTableContext->GetUpdateNewTuple for use by MERGE. It
- * computes the updated tuple by projecting from the current merge action's
- * projection.
- */
-static TupleTableSlot *
-mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ExprContext *econtext = relaction->mas_proj->pi_exprContext;
-
- econtext->ecxt_scantuple = oldSlot;
- econtext->ecxt_innertuple = planSlot;
-
- return ExecProject(relaction->mas_proj);
-}
-
-/*
* Process BEFORE EACH STATEMENT triggers
*/
static void
@@ -3854,9 +3790,8 @@ ExecModifyTable(PlanState *pstate)
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
}
- slot = internalGetUpdateNewTuple(resultRelInfo, context.planSlot,
- oldSlot, NULL);
- context.GetUpdateNewTuple = internalGetUpdateNewTuple;
+ slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot,
+ oldSlot);
context.relaction = NULL;
/* Now apply the update. */
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index 0d7558e..fff8d16
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -211,6 +211,14 @@ extern void ExecBSDeleteTriggers(EState
extern void ExecASDeleteTriggers(EState *estate,
ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture);
+extern bool ExecBRDeleteTriggersNew(EState *estate,
+ EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd);
extern bool ExecBRDeleteTriggers(EState *estate,
EPQState *epqstate,
ResultRelInfo *relinfo,
@@ -231,6 +239,14 @@ extern void ExecBSUpdateTriggers(EState
extern void ExecASUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture);
+extern bool ExecBRUpdateTriggersNew(EState *estate,
+ EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot *newslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd);
extern bool ExecBRUpdateTriggers(EState *estate,
EPQState *epqstate,
ResultRelInfo *relinfo,
diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
new file mode 100644
index b2befa8..897b935
--- a/src/test/isolation/expected/merge-delete.out
+++ b/src/test/isolation/expected/merge-delete.out
@@ -10,20 +10,31 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step select2: SELECT * FROM target;
+step select2_pa: SELECT * FROM target_pa;
key|val
---+---
(0 rows)
step c2: COMMIT;
-starting permutation: delete c1 update1 select2 c2
+starting permutation: delete_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 update2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1;
step select2: SELECT * FROM target;
key|val
---+---
@@ -31,11 +42,23 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 update1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 update2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
-step select2: SELECT * FROM target;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 update2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -45,32 +68,72 @@ step c2: COMMIT;
starting permutation: delete c1 merge2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete c1 merge2 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 merge2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 merge2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 merge_delete2 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
(1 row)
step c2: COMMIT;
-starting permutation: delete update1 c1 select2 c2
+starting permutation: delete_tg c1 merge_delete2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete update2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
+step update2: <... completed>
step select2: SELECT * FROM target;
key|val
---+---
@@ -78,12 +141,25 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete update1 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+starting permutation: delete_pa update2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
-step select2: SELECT * FROM target;
+step update2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg update2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -92,26 +168,69 @@ step c2: COMMIT;
starting permutation: delete merge2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
step merge2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete merge2 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+starting permutation: delete_pa merge2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
-step merge2: <... completed>
+step merge2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete merge_delete2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+step merge_delete2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge_delete2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
(1 row)
step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 8183f52..9a44a59
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -23,6 +23,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update1_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,170,s1,"setup updated by update1_tg") -> (1,170,s2,"setup updated by update1_tg when1")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 170|s2 |setup updated by update1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update2 merge_status c2 select1 c1
step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
step merge_status:
@@ -46,6 +71,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update2_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s2,"setup updated by update2_tg")
+step update2_tg: UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s2,"setup updated by update2_tg") -> (1,160,s3,"setup updated by update2_tg when2")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s3 |setup updated by update2_tg when2
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update3 merge_status c2 select1 c1
step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
step merge_status:
@@ -69,6 +119,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update3_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s3,"setup updated by update3_tg")
+step update3_tg: UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s3,"setup updated by update3_tg") -> (1,160,s4,"setup updated by update3_tg when3")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s4 |setup updated by update3_tg when3
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update5 merge_status c2 select1 c1
step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
step merge_status:
@@ -92,6 +167,30 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update5_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s5,"setup updated by update5_tg")
+step update5_tg: UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------
+ 1| 160|s5 |setup updated by update5_tg
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update_bal1 merge_bal c2 select1 c1
step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
step merge_bal:
@@ -114,3 +213,137 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+
+starting permutation: update_bal1_pa merge_bal_pa c2 select1_pa c1
+step update_bal1_pa: UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1;
+step merge_bal_pa:
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal_pa: <... completed>
+step select1_pa: SELECT * FROM target_pa;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_pa when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_bal_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_bal_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_bal_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update1 merge_delete c2 select1 c1
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Delete: (1,170,s1,"setup updated by update1_tg")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1 merge_delete c2 select1 c1
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+----------------------------------
+ 1| 100|s1 |setup updated by update_bal1 when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
new file mode 100644
index 0e70532..ba5f70e
--- a/src/test/isolation/specs/merge-delete.spec
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -7,11 +7,39 @@ setup
{
CREATE TABLE target (key int primary key, val text);
INSERT INTO target VALUES (1, 'setup1');
+
+ CREATE TABLE target_pa (key int primary key, val text) PARTITION BY LIST (key);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES IN (1);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES IN (2);
+ INSERT INTO target_pa VALUES (1, 'setup1');
+
+ CREATE TABLE target_tg (key int primary key, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 'setup1');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -20,7 +48,8 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "delete" { DELETE FROM target t WHERE t.key = 1; }
-step "merge_delete" { MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE; }
+step "delete_pa" { DELETE FROM target_pa t WHERE t.key = 1; }
+step "delete_tg" { DELETE FROM target_tg t WHERE t.key = 1; }
step "c1" { COMMIT; }
session "s2"
@@ -28,23 +57,40 @@ setup
{
BEGIN ISOLATION LEVEL READ COMMITTED;
}
-step "update1" { UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; }
-step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "update2" { UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_pa" { UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_pa" { MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge_delete2" { MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
+step "merge_delete2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
step "select2" { SELECT * FROM target; }
+step "select2_pa" { SELECT * FROM target_pa; }
+step "select2_tg" { SELECT * FROM target_tg; }
step "c2" { COMMIT; }
# Basic effects
permutation "delete" "c1" "select2" "c2"
-permutation "merge_delete" "c1" "select2" "c2"
+permutation "delete_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "c1" "select2_tg" "c2"
# One after the other, no concurrency
-permutation "delete" "c1" "update1" "select2" "c2"
-permutation "merge_delete" "c1" "update1" "select2" "c2"
+permutation "delete" "c1" "update2" "select2" "c2"
+permutation "delete_pa" "c1" "update2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "update2_tg" "select2_tg" "c2"
permutation "delete" "c1" "merge2" "select2" "c2"
-permutation "merge_delete" "c1" "merge2" "select2" "c2"
+permutation "delete_pa" "c1" "merge2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "merge2_tg" "select2_tg" "c2"
+permutation "delete" "c1" "merge_delete2" "select2" "c2"
+permutation "delete_tg" "c1" "merge_delete2_tg" "select2_tg" "c2"
# Now with concurrency
-permutation "delete" "update1" "c1" "select2" "c2"
-permutation "merge_delete" "update1" "c1" "select2" "c2"
+permutation "delete" "update2" "c1" "select2" "c2"
+permutation "delete_pa" "update2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "update2_tg" "c1" "select2_tg" "c2"
permutation "delete" "merge2" "c1" "select2" "c2"
-permutation "merge_delete" "merge2" "c1" "select2" "c2"
+permutation "delete_pa" "merge2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "merge2_tg" "c1" "select2_tg" "c2"
+permutation "delete" "merge_delete2" "c1" "select2" "c2"
+permutation "delete_tg" "merge_delete2_tg" "c1" "select2_tg" "c2"
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index d56400a..298b2bf
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -8,11 +8,39 @@ setup
{
CREATE TABLE target (key int primary key, balance integer, status text, val text);
INSERT INTO target VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_pa (key int, balance integer, status text, val text) PARTITION BY RANGE (balance);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES FROM (0) TO (200);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES FROM (200) TO (1000);
+ INSERT INTO target_pa VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_tg (key int primary key, balance integer, status text, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 160, 's1', 'setup');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -32,6 +60,18 @@ step "merge_status"
WHEN MATCHED AND status = 's3' THEN
UPDATE SET status = 's4', val = t.val || ' when3';
}
+step "merge_status_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+}
step "merge_bal"
{
@@ -45,8 +85,55 @@ step "merge_bal"
WHEN MATCHED AND balance < 300 THEN
UPDATE SET balance = balance * 8, val = t.val || ' when3';
}
+step "merge_bal_pa"
+{
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+step "merge_bal_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+
+step "merge_delete"
+{
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
+step "merge_delete_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
step "select1" { SELECT * FROM target; }
+step "select1_pa" { SELECT * FROM target_pa; }
+step "select1_tg" { SELECT * FROM target_tg; }
step "c1" { COMMIT; }
session "s2"
@@ -55,23 +142,43 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "update1" { UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "update1_tg" { UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1; }
step "update2" { UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
step "update3" { UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1; }
+step "update3_tg" { UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1; }
step "update5" { UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1; }
+step "update5_tg" { UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1; }
step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
+step "update_bal1_pa" { UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1; }
+step "update_bal1_tg" { UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1; }
step "c2" { COMMIT; }
# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
permutation "update1" "merge_status" "c2" "select1" "c1"
+permutation "update1_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
permutation "update2" "merge_status" "c2" "select1" "c1"
+permutation "update2_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
permutation "update3" "merge_status" "c2" "select1" "c1"
+permutation "update3_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, but we skip update and MERGE does nothing
permutation "update5" "merge_status" "c2" "select1" "c1"
+permutation "update5_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
+permutation "update_bal1_pa" "merge_bal_pa" "c2" "select1_pa" "c1"
+permutation "update_bal1_tg" "merge_bal_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, but recheck passes and row is deleted
+permutation "update1" "merge_delete" "c2" "select1" "c1"
+permutation "update1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance is 100
+permutation "update_bal1" "merge_delete" "c2" "select1" "c1"
+permutation "update_bal1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
* Re: BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-03-12 11:14 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dean Rasheed @ 2023-03-12 11:14 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, 11 Mar 2023 at 18:12, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> Attached is an updated patch that I'm more happy with now, and a
> slightly modified one for v15, keeping the trigger API
> backwards-compatible for extensions.
>
Going over this again, I spotted another couple of things:
Passing recheckIndexes to ExecUpdateEpilogue() is pointless, since
that's something that's only ever computed and used locally within
ExecUpdateEpilogue(). It's also pointless for the caller to
list_free() it, since the caller only has a pointer to the empty list,
not the list built in ExecUpdateEpilogue(), which it should free. This
is pretty harmless, but I might as well tidy it up while hacking on
this.
If a BEFORE ROW trigger returns NULL to skip an update or delete,
ExecMergeMatched() will still update estate->es_processed, and so the
final row count in the command tag will be wrong. I'll tackle that in
a follow-on patch, since it's really a separate bug.
Regards,
Dean
Attachments: download all (2 files, .tar.gz)
bug-17809-v4.patch (text/x-patch, 51.2K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index 66401f2..0f2de7e
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -87,6 +87,7 @@ static bool GetTupleForTrigger(EState *e
LockTupleMode lockmode,
TupleTableSlot *oldslot,
TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
TM_FailureData *tmfdp);
static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
Trigger *trigger, TriggerEvent event,
@@ -2694,7 +2695,9 @@ ExecBRDeleteTriggers(EState *estate, EPQ
ResultRelInfo *relinfo,
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
- TupleTableSlot **epqslot)
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd)
{
TupleTableSlot *slot = ExecGetTriggerOldSlot(estate, relinfo);
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2711,7 +2714,7 @@ ExecBRDeleteTriggers(EState *estate, EPQ
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
LockTupleExclusive, slot, &epqslot_candidate,
- NULL))
+ tmresult, tmfd))
return false;
/*
@@ -2802,6 +2805,7 @@ ExecARDeleteTriggers(EState *estate,
LockTupleExclusive,
slot,
NULL,
+ NULL,
NULL);
else
ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
@@ -2943,6 +2947,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
+ TM_Result *tmresult,
TM_FailureData *tmfd)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2967,7 +2972,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
/* get a copy of the on-disk tuple we are planning to update */
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
lockmode, oldslot, &epqslot_candidate,
- tmfd))
+ tmresult, tmfd))
return false; /* cancel the update action */
/*
@@ -3122,6 +3127,7 @@ ExecARUpdateTriggers(EState *estate, Res
LockTupleExclusive,
oldslot,
NULL,
+ NULL,
NULL);
else if (fdw_trigtuple != NULL)
ExecForceStoreHeapTuple(fdw_trigtuple, oldslot, false);
@@ -3277,6 +3283,7 @@ GetTupleForTrigger(EState *estate,
LockTupleMode lockmode,
TupleTableSlot *oldslot,
TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
TM_FailureData *tmfdp)
{
Relation relation = relinfo->ri_RelationDesc;
@@ -3304,6 +3311,8 @@ GetTupleForTrigger(EState *estate,
&tmfd);
/* Let the caller know about the status of this operation */
+ if (tmresultp)
+ *tmresultp = test;
if (tmfdp)
*tmfdp = tmfd;
@@ -3331,6 +3340,18 @@ GetTupleForTrigger(EState *estate,
case TM_Ok:
if (tmfd.traversed)
{
+ /*
+ * Recheck the tuple using EPQ. For MERGE, we leave this
+ * to the caller (it must do additional rechecking, and
+ * might end up executing a different action entirely).
+ */
+ if (estate->es_plannedstmt->commandType == CMD_MERGE)
+ {
+ if (tmresultp)
+ *tmresultp = TM_Updated;
+ return false;
+ }
+
*epqslot = EvalPlanQual(epqstate,
relation,
relinfo->ri_RangeTableIndex,
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
new file mode 100644
index c484f5c..4f5083a
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -486,7 +486,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
if (!ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
- tid, NULL, slot, NULL))
+ tid, NULL, slot, NULL, NULL))
skip_tuple = true; /* "do nothing" */
}
@@ -547,7 +547,7 @@ ExecSimpleRelationDelete(ResultRelInfo *
resultRelInfo->ri_TrigDesc->trig_delete_before_row)
{
skip_tuple = !ExecBRDeleteTriggers(estate, epqstate, resultRelInfo,
- tid, NULL, NULL);
+ tid, NULL, NULL, NULL, NULL);
}
if (!skip_tuple)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 6f44d71..e8d6558
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -88,15 +88,6 @@ typedef struct ModifyTableContext
*/
TupleTableSlot *planSlot;
- /*
- * During EvalPlanQual, project and return the new version of the new
- * tuple
- */
- TupleTableSlot *(*GetUpdateNewTuple) (ResultRelInfo *resultRelInfo,
- TupleTableSlot *epqslot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
-
/* MERGE specific */
MergeActionState *relaction; /* MERGE action in progress */
@@ -107,12 +98,6 @@ typedef struct ModifyTableContext
TM_FailureData tmfd;
/*
- * The tuple produced by EvalPlanQual to retry from, if a cross-partition
- * UPDATE requires it
- */
- TupleTableSlot *cpUpdateRetrySlot;
-
- /*
* The tuple projected by the INSERT's RETURNING clause, when doing a
* cross-partition UPDATE
*/
@@ -162,10 +147,6 @@ static TupleTableSlot *ExecPrepareTupleR
ResultRelInfo *targetRelInfo,
TupleTableSlot *slot,
ResultRelInfo **partRelInfo);
-static TupleTableSlot *internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
static TupleTableSlot *ExecMerge(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
@@ -179,10 +160,6 @@ static bool ExecMergeMatched(ModifyTable
static void ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
-static TupleTableSlot *mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
/*
@@ -738,26 +715,14 @@ ExecGetUpdateNewTuple(ResultRelInfo *rel
TupleTableSlot *planSlot,
TupleTableSlot *oldSlot)
{
+ ProjectionInfo *newProj = relinfo->ri_projectNew;
+ ExprContext *econtext;
+
/* Use a few extra Asserts to protect against outside callers */
Assert(relinfo->ri_projectNewInfoValid);
Assert(planSlot != NULL && !TTS_EMPTY(planSlot));
Assert(oldSlot != NULL && !TTS_EMPTY(oldSlot));
- return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
-}
-
-/*
- * Callback for ModifyTableState->GetUpdateNewTuple for use by regular UPDATE.
- */
-static TupleTableSlot *
-internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ProjectionInfo *newProj = relinfo->ri_projectNew;
- ExprContext *econtext;
-
econtext = newProj->pi_exprContext;
econtext->ecxt_outertuple = planSlot;
econtext->ecxt_scantuple = oldSlot;
@@ -1336,8 +1301,11 @@ ExecPendingInserts(EState *estate)
static bool
ExecDeletePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple,
- TupleTableSlot **epqreturnslot)
+ TupleTableSlot **epqreturnslot, TM_Result *result)
{
+ if (result)
+ *result = TM_Ok;
+
/* BEFORE ROW DELETE triggers */
if (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_delete_before_row)
@@ -1348,7 +1316,7 @@ ExecDeletePrologue(ModifyTableContext *c
return ExecBRDeleteTriggers(context->estate, context->epqstate,
resultRelInfo, tupleid, oldtuple,
- epqreturnslot);
+ epqreturnslot, result, &context->tmfd);
}
return true;
@@ -1465,7 +1433,7 @@ ExecDelete(ModifyTableContext *context,
* done if it says we are.
*/
if (!ExecDeletePrologue(context, resultRelInfo, tupleid, oldtuple,
- epqreturnslot))
+ epqreturnslot, NULL))
return NULL;
/* INSTEAD OF ROW DELETE Triggers */
@@ -1746,8 +1714,10 @@ ldelete:
*
* False is returned if the tuple we're trying to move is found to have been
* concurrently updated. In that case, the caller must check if the updated
- * tuple (in updateCxt->cpUpdateRetrySlot) still needs to be re-routed, and
- * call this function again or perform a regular update accordingly.
+ * tuple that's returned in *retry_slot still needs to be re-routed, and call
+ * this function again or perform a regular update accordingly. For MERGE,
+ * the updated tuple is not returned in *retry_slot; it has its own retry
+ * logic.
*/
static bool
ExecCrossPartitionUpdate(ModifyTableContext *context,
@@ -1756,6 +1726,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *slot,
bool canSetTag,
UpdateContext *updateCxt,
+ TupleTableSlot **retry_slot,
TupleTableSlot **inserted_tuple,
ResultRelInfo **insert_destrel)
{
@@ -1766,7 +1737,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *epqslot = NULL;
context->cpUpdateReturningSlot = NULL;
- context->cpUpdateRetrySlot = NULL;
+ *retry_slot = NULL;
/*
* Disallow an INSERT ON CONFLICT DO UPDATE that causes the original row
@@ -1845,9 +1816,13 @@ ExecCrossPartitionUpdate(ModifyTableCont
* another transaction has concurrently updated the same row, it
* re-fetches the row, skips the delete, and epqslot is set to the
* re-fetched tuple slot. In that case, we need to do all the checks
- * again.
+ * again. For MERGE, we leave everything to the caller (it must do
+ * additional rechecking, and might end up executing a different
+ * action entirely).
*/
- if (TupIsNull(epqslot))
+ if (context->relaction != NULL)
+ return false;
+ else if (TupIsNull(epqslot))
return true;
else
{
@@ -1864,9 +1839,8 @@ ExecCrossPartitionUpdate(ModifyTableCont
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
/* and project the new tuple to retry the UPDATE with */
- context->cpUpdateRetrySlot =
- context->GetUpdateNewTuple(resultRelInfo, epqslot, oldSlot,
- context->relaction);
+ *retry_slot = ExecGetUpdateNewTuple(resultRelInfo, epqslot,
+ oldSlot);
return false;
}
}
@@ -1907,10 +1881,14 @@ ExecCrossPartitionUpdate(ModifyTableCont
*/
static bool
ExecUpdatePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot)
+ ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
+ TM_Result *result)
{
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
+ if (result)
+ *result = TM_Ok;
+
ExecMaterializeSlot(slot);
/*
@@ -1931,7 +1909,7 @@ ExecUpdatePrologue(ModifyTableContext *c
return ExecBRUpdateTriggers(context->estate, context->epqstate,
resultRelInfo, tupleid, oldtuple, slot,
- &context->tmfd);
+ result, &context->tmfd);
}
return true;
@@ -2032,7 +2010,8 @@ lreplace:
*/
if (partition_constraint_failed)
{
- TupleTableSlot *inserted_tuple;
+ TupleTableSlot *inserted_tuple,
+ *retry_slot;
ResultRelInfo *insert_destrel = NULL;
/*
@@ -2044,6 +2023,7 @@ lreplace:
if (ExecCrossPartitionUpdate(context, resultRelInfo,
tupleid, oldtuple, slot,
canSetTag, updateCxt,
+ &retry_slot,
&inserted_tuple,
&insert_destrel))
{
@@ -2088,7 +2068,7 @@ lreplace:
* ExecCrossPartitionUpdate installed an updated version of the new
* tuple in the retry slot; start over.
*/
- slot = context->cpUpdateRetrySlot;
+ slot = retry_slot;
goto lreplace;
}
@@ -2132,10 +2112,10 @@ lreplace:
static void
ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
ResultRelInfo *resultRelInfo, ItemPointer tupleid,
- HeapTuple oldtuple, TupleTableSlot *slot,
- List *recheckIndexes)
+ HeapTuple oldtuple, TupleTableSlot *slot)
{
ModifyTableState *mtstate = context->mtstate;
+ List *recheckIndexes = NIL;
/* insert index entries for tuple if necessary */
if (resultRelInfo->ri_NumIndices > 0 && updateCxt->updateIndexes)
@@ -2154,6 +2134,8 @@ ExecUpdateEpilogue(ModifyTableContext *c
mtstate->mt_transition_capture,
false);
+ list_free(recheckIndexes);
+
/*
* Check any WITH CHECK OPTION constraints from parent views. We are
* required to do this after testing all constraints and uniqueness
@@ -2272,7 +2254,6 @@ ExecUpdate(ModifyTableContext *context,
EState *estate = context->estate;
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
UpdateContext updateCxt = {0};
- List *recheckIndexes = NIL;
TM_Result result;
/*
@@ -2285,7 +2266,7 @@ ExecUpdate(ModifyTableContext *context,
* Prepare for the update. This includes BEFORE ROW triggers, so we're
* done if it says we are.
*/
- if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot))
+ if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
return NULL;
/* INSTEAD OF ROW UPDATE Triggers */
@@ -2486,9 +2467,7 @@ redo_act:
(estate->es_processed)++;
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo, tupleid, oldtuple,
- slot, recheckIndexes);
-
- list_free(recheckIndexes);
+ slot);
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
@@ -2855,7 +2834,6 @@ lmerge_matched:
{
MergeActionState *relaction = (MergeActionState *) lfirst(l);
CmdType commandType = relaction->mas_action->commandType;
- List *recheckIndexes = NIL;
TM_Result result;
UpdateContext updateCxt = {0};
@@ -2902,13 +2880,10 @@ lmerge_matched:
newslot = ExecProject(relaction->mas_proj);
context->relaction = relaction;
- context->GetUpdateNewTuple = mergeGetUpdateNewTuple;
- context->cpUpdateRetrySlot = NULL;
-
if (!ExecUpdatePrologue(context, resultRelInfo,
- tupleid, NULL, newslot))
+ tupleid, NULL, newslot, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
@@ -2916,18 +2891,17 @@ lmerge_matched:
if (result == TM_Ok && updateCxt.updated)
{
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo,
- tupleid, NULL, newslot, recheckIndexes);
+ tupleid, NULL, newslot);
mtstate->mt_merge_updated += 1;
}
-
break;
case CMD_DELETE:
context->relaction = relaction;
if (!ExecDeletePrologue(context, resultRelInfo, tupleid,
- NULL, NULL))
+ NULL, NULL, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecDeleteAct(context, resultRelInfo, tupleid, false);
@@ -2994,34 +2968,13 @@ lmerge_matched:
/*
* The target tuple was concurrently updated by some other
- * transaction.
- */
-
- /*
- * During an UPDATE, if cpUpdateRetrySlot is set, then
- * ExecCrossPartitionUpdate() must have detected that the
- * tuple was concurrently updated, so we restart the
- * search for an appropriate WHEN MATCHED clause to
- * process the updated tuple.
- *
- * In this case, ExecDelete() would already have performed
- * EvalPlanQual() on the latest version of the tuple,
- * which in turn would already have been loaded into
- * ri_oldTupleSlot, so no need to do either of those
- * things.
- */
- if (commandType == CMD_UPDATE &&
- !TupIsNull(context->cpUpdateRetrySlot))
- goto lmerge_matched;
-
- /*
- * Otherwise, we run the EvalPlanQual() with the new
- * version of the tuple. If EvalPlanQual() does not return
- * a tuple, then we switch to the NOT MATCHED list of
- * actions. If it does return a tuple and the join qual is
- * still satisfied, then we just need to recheck the
- * MATCHED actions, starting from the top, and execute the
- * first qualifying action.
+ * transaction. Run EvalPlanQual() with the new version of
+ * the tuple. If it does not return a tuple, then we
+ * switch to the NOT MATCHED list of actions. If it does
+ * return a tuple and the join qual is still satisfied,
+ * then we just need to recheck the MATCHED actions,
+ * starting from the top, and execute the first qualifying
+ * action.
*/
resultRelationDesc = resultRelInfo->ri_RelationDesc;
lockmode = ExecUpdateLockMode(estate, resultRelInfo);
@@ -3397,25 +3350,6 @@ ExecInitMergeTupleSlots(ModifyTableState
}
/*
- * Callback for ModifyTableContext->GetUpdateNewTuple for use by MERGE. It
- * computes the updated tuple by projecting from the current merge action's
- * projection.
- */
-static TupleTableSlot *
-mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ExprContext *econtext = relaction->mas_proj->pi_exprContext;
-
- econtext->ecxt_scantuple = oldSlot;
- econtext->ecxt_innertuple = planSlot;
-
- return ExecProject(relaction->mas_proj);
-}
-
-/*
* Process BEFORE EACH STATEMENT triggers
*/
static void
@@ -3870,9 +3804,8 @@ ExecModifyTable(PlanState *pstate)
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
}
- slot = internalGetUpdateNewTuple(resultRelInfo, context.planSlot,
- oldSlot, NULL);
- context.GetUpdateNewTuple = internalGetUpdateNewTuple;
+ slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot,
+ oldSlot);
context.relaction = NULL;
/* Now apply the update. */
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index 67496d8..430e3ca
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -211,7 +211,9 @@ extern bool ExecBRDeleteTriggers(EState
ResultRelInfo *relinfo,
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
- TupleTableSlot **epqslot);
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd);
extern void ExecARDeleteTriggers(EState *estate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
@@ -232,6 +234,7 @@ extern bool ExecBRUpdateTriggers(EState
ItemPointer tupleid,
HeapTuple fdw_trigtuple,
TupleTableSlot *newslot,
+ TM_Result *tmresult,
TM_FailureData *tmfd);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
new file mode 100644
index b2befa8..897b935
--- a/src/test/isolation/expected/merge-delete.out
+++ b/src/test/isolation/expected/merge-delete.out
@@ -10,20 +10,31 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step select2: SELECT * FROM target;
+step select2_pa: SELECT * FROM target_pa;
key|val
---+---
(0 rows)
step c2: COMMIT;
-starting permutation: delete c1 update1 select2 c2
+starting permutation: delete_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 update2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1;
step select2: SELECT * FROM target;
key|val
---+---
@@ -31,11 +42,23 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 update1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 update2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
-step select2: SELECT * FROM target;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 update2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -45,32 +68,72 @@ step c2: COMMIT;
starting permutation: delete c1 merge2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete c1 merge2 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 merge2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 merge2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 merge_delete2 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
(1 row)
step c2: COMMIT;
-starting permutation: delete update1 c1 select2 c2
+starting permutation: delete_tg c1 merge_delete2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete update2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
+step update2: <... completed>
step select2: SELECT * FROM target;
key|val
---+---
@@ -78,12 +141,25 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete update1 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+starting permutation: delete_pa update2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
-step select2: SELECT * FROM target;
+step update2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg update2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -92,26 +168,69 @@ step c2: COMMIT;
starting permutation: delete merge2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
step merge2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete merge2 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+starting permutation: delete_pa merge2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
-step merge2: <... completed>
+step merge2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete merge_delete2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+step merge_delete2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge_delete2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
(1 row)
step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 8183f52..9a44a59
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -23,6 +23,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update1_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,170,s1,"setup updated by update1_tg") -> (1,170,s2,"setup updated by update1_tg when1")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 170|s2 |setup updated by update1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update2 merge_status c2 select1 c1
step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
step merge_status:
@@ -46,6 +71,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update2_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s2,"setup updated by update2_tg")
+step update2_tg: UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s2,"setup updated by update2_tg") -> (1,160,s3,"setup updated by update2_tg when2")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s3 |setup updated by update2_tg when2
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update3 merge_status c2 select1 c1
step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
step merge_status:
@@ -69,6 +119,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update3_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s3,"setup updated by update3_tg")
+step update3_tg: UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s3,"setup updated by update3_tg") -> (1,160,s4,"setup updated by update3_tg when3")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s4 |setup updated by update3_tg when3
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update5 merge_status c2 select1 c1
step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
step merge_status:
@@ -92,6 +167,30 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update5_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s5,"setup updated by update5_tg")
+step update5_tg: UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------
+ 1| 160|s5 |setup updated by update5_tg
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update_bal1 merge_bal c2 select1 c1
step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
step merge_bal:
@@ -114,3 +213,137 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+
+starting permutation: update_bal1_pa merge_bal_pa c2 select1_pa c1
+step update_bal1_pa: UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1;
+step merge_bal_pa:
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal_pa: <... completed>
+step select1_pa: SELECT * FROM target_pa;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_pa when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_bal_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_bal_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_bal_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update1 merge_delete c2 select1 c1
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Delete: (1,170,s1,"setup updated by update1_tg")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1 merge_delete c2 select1 c1
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+----------------------------------
+ 1| 100|s1 |setup updated by update_bal1 when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
new file mode 100644
index 0e70532..ba5f70e
--- a/src/test/isolation/specs/merge-delete.spec
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -7,11 +7,39 @@ setup
{
CREATE TABLE target (key int primary key, val text);
INSERT INTO target VALUES (1, 'setup1');
+
+ CREATE TABLE target_pa (key int primary key, val text) PARTITION BY LIST (key);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES IN (1);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES IN (2);
+ INSERT INTO target_pa VALUES (1, 'setup1');
+
+ CREATE TABLE target_tg (key int primary key, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 'setup1');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -20,7 +48,8 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "delete" { DELETE FROM target t WHERE t.key = 1; }
-step "merge_delete" { MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE; }
+step "delete_pa" { DELETE FROM target_pa t WHERE t.key = 1; }
+step "delete_tg" { DELETE FROM target_tg t WHERE t.key = 1; }
step "c1" { COMMIT; }
session "s2"
@@ -28,23 +57,40 @@ setup
{
BEGIN ISOLATION LEVEL READ COMMITTED;
}
-step "update1" { UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; }
-step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "update2" { UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_pa" { UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_pa" { MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge_delete2" { MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
+step "merge_delete2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
step "select2" { SELECT * FROM target; }
+step "select2_pa" { SELECT * FROM target_pa; }
+step "select2_tg" { SELECT * FROM target_tg; }
step "c2" { COMMIT; }
# Basic effects
permutation "delete" "c1" "select2" "c2"
-permutation "merge_delete" "c1" "select2" "c2"
+permutation "delete_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "c1" "select2_tg" "c2"
# One after the other, no concurrency
-permutation "delete" "c1" "update1" "select2" "c2"
-permutation "merge_delete" "c1" "update1" "select2" "c2"
+permutation "delete" "c1" "update2" "select2" "c2"
+permutation "delete_pa" "c1" "update2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "update2_tg" "select2_tg" "c2"
permutation "delete" "c1" "merge2" "select2" "c2"
-permutation "merge_delete" "c1" "merge2" "select2" "c2"
+permutation "delete_pa" "c1" "merge2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "merge2_tg" "select2_tg" "c2"
+permutation "delete" "c1" "merge_delete2" "select2" "c2"
+permutation "delete_tg" "c1" "merge_delete2_tg" "select2_tg" "c2"
# Now with concurrency
-permutation "delete" "update1" "c1" "select2" "c2"
-permutation "merge_delete" "update1" "c1" "select2" "c2"
+permutation "delete" "update2" "c1" "select2" "c2"
+permutation "delete_pa" "update2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "update2_tg" "c1" "select2_tg" "c2"
permutation "delete" "merge2" "c1" "select2" "c2"
-permutation "merge_delete" "merge2" "c1" "select2" "c2"
+permutation "delete_pa" "merge2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "merge2_tg" "c1" "select2_tg" "c2"
+permutation "delete" "merge_delete2" "c1" "select2" "c2"
+permutation "delete_tg" "merge_delete2_tg" "c1" "select2_tg" "c2"
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index d56400a..298b2bf
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -8,11 +8,39 @@ setup
{
CREATE TABLE target (key int primary key, balance integer, status text, val text);
INSERT INTO target VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_pa (key int, balance integer, status text, val text) PARTITION BY RANGE (balance);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES FROM (0) TO (200);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES FROM (200) TO (1000);
+ INSERT INTO target_pa VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_tg (key int primary key, balance integer, status text, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 160, 's1', 'setup');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -32,6 +60,18 @@ step "merge_status"
WHEN MATCHED AND status = 's3' THEN
UPDATE SET status = 's4', val = t.val || ' when3';
}
+step "merge_status_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+}
step "merge_bal"
{
@@ -45,8 +85,55 @@ step "merge_bal"
WHEN MATCHED AND balance < 300 THEN
UPDATE SET balance = balance * 8, val = t.val || ' when3';
}
+step "merge_bal_pa"
+{
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+step "merge_bal_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+
+step "merge_delete"
+{
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
+step "merge_delete_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
step "select1" { SELECT * FROM target; }
+step "select1_pa" { SELECT * FROM target_pa; }
+step "select1_tg" { SELECT * FROM target_tg; }
step "c1" { COMMIT; }
session "s2"
@@ -55,23 +142,43 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "update1" { UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "update1_tg" { UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1; }
step "update2" { UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
step "update3" { UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1; }
+step "update3_tg" { UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1; }
step "update5" { UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1; }
+step "update5_tg" { UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1; }
step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
+step "update_bal1_pa" { UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1; }
+step "update_bal1_tg" { UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1; }
step "c2" { COMMIT; }
# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
permutation "update1" "merge_status" "c2" "select1" "c1"
+permutation "update1_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
permutation "update2" "merge_status" "c2" "select1" "c1"
+permutation "update2_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
permutation "update3" "merge_status" "c2" "select1" "c1"
+permutation "update3_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, but we skip update and MERGE does nothing
permutation "update5" "merge_status" "c2" "select1" "c1"
+permutation "update5_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
+permutation "update_bal1_pa" "merge_bal_pa" "c2" "select1_pa" "c1"
+permutation "update_bal1_tg" "merge_bal_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, but recheck passes and row is deleted
+permutation "update1" "merge_delete" "c2" "select1" "c1"
+permutation "update1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance is 100
+permutation "update_bal1" "merge_delete" "c2" "select1" "c1"
+permutation "update_bal1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
bug-17809-v4-15.patch (text/x-patch, 53.5K) download
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
new file mode 100644
index d9d9201..7326ece
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -84,8 +84,9 @@ static bool GetTupleForTrigger(EState *e
ItemPointer tid,
LockTupleMode lockmode,
TupleTableSlot *oldslot,
- TupleTableSlot **newSlot,
- TM_FailureData *tmfpd);
+ TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
+ TM_FailureData *tmfdp);
static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
Trigger *trigger, TriggerEvent event,
Bitmapset *modifiedCols,
@@ -2753,11 +2754,13 @@ ExecASDeleteTriggers(EState *estate, Res
* back the concurrently updated tuple if any.
*/
bool
-ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
- ResultRelInfo *relinfo,
- ItemPointer tupleid,
- HeapTuple fdw_trigtuple,
- TupleTableSlot **epqslot)
+ExecBRDeleteTriggersNew(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd)
{
TupleTableSlot *slot = ExecGetTriggerOldSlot(estate, relinfo);
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
@@ -2774,7 +2777,7 @@ ExecBRDeleteTriggers(EState *estate, EPQ
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
LockTupleExclusive, slot, &epqslot_candidate,
- NULL))
+ tmresult, tmfd))
return false;
/*
@@ -2838,6 +2841,21 @@ ExecBRDeleteTriggers(EState *estate, EPQ
}
/*
+ * ABI-compatible wrapper to emulate old version of the above function.
+ * Do not call this version in new code.
+ */
+bool
+ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot **epqslot)
+{
+ return ExecBRDeleteTriggersNew(estate, epqstate, relinfo, tupleid,
+ fdw_trigtuple, epqslot, NULL, NULL);
+}
+
+/*
* Note: is_crosspart_update must be true if the DELETE is being performed
* as part of a cross-partition update.
*/
@@ -2865,6 +2883,7 @@ ExecARDeleteTriggers(EState *estate,
LockTupleExclusive,
slot,
NULL,
+ NULL,
NULL);
else
ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
@@ -3001,12 +3020,13 @@ ExecASUpdateTriggers(EState *estate, Res
}
bool
-ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
- ResultRelInfo *relinfo,
- ItemPointer tupleid,
- HeapTuple fdw_trigtuple,
- TupleTableSlot *newslot,
- TM_FailureData *tmfd)
+ExecBRUpdateTriggersNew(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot *newslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
@@ -3030,7 +3050,7 @@ ExecBRUpdateTriggers(EState *estate, EPQ
/* get a copy of the on-disk tuple we are planning to update */
if (!GetTupleForTrigger(estate, epqstate, relinfo, tupleid,
lockmode, oldslot, &epqslot_candidate,
- tmfd))
+ tmresult, tmfd))
return false; /* cancel the update action */
/*
@@ -3135,6 +3155,22 @@ ExecBRUpdateTriggers(EState *estate, EPQ
}
/*
+ * ABI-compatible wrapper to emulate old version of the above function.
+ * Do not call this version in new code.
+ */
+bool
+ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot *newslot,
+ TM_FailureData *tmfd)
+{
+ return ExecBRUpdateTriggersNew(estate, epqstate, relinfo, tupleid,
+ fdw_trigtuple, newslot, NULL, tmfd);
+}
+
+/*
* Note: 'src_partinfo' and 'dst_partinfo', when non-NULL, refer to the source
* and destination partitions, respectively, of a cross-partition update of
* the root partitioned table mentioned in the query, given by 'relinfo'.
@@ -3185,6 +3221,7 @@ ExecARUpdateTriggers(EState *estate, Res
LockTupleExclusive,
oldslot,
NULL,
+ NULL,
NULL);
else if (fdw_trigtuple != NULL)
ExecForceStoreHeapTuple(fdw_trigtuple, oldslot, false);
@@ -3340,6 +3377,7 @@ GetTupleForTrigger(EState *estate,
LockTupleMode lockmode,
TupleTableSlot *oldslot,
TupleTableSlot **epqslot,
+ TM_Result *tmresultp,
TM_FailureData *tmfdp)
{
Relation relation = relinfo->ri_RelationDesc;
@@ -3367,6 +3405,8 @@ GetTupleForTrigger(EState *estate,
&tmfd);
/* Let the caller know about the status of this operation */
+ if (tmresultp)
+ *tmresultp = test;
if (tmfdp)
*tmfdp = tmfd;
@@ -3394,6 +3434,18 @@ GetTupleForTrigger(EState *estate,
case TM_Ok:
if (tmfd.traversed)
{
+ /*
+ * Recheck the tuple using EPQ. For MERGE, we leave this
+ * to the caller (it must do additional rechecking, and
+ * might end up executing a different action entirely).
+ */
+ if (estate->es_plannedstmt->commandType == CMD_MERGE)
+ {
+ if (tmresultp)
+ *tmresultp = TM_Updated;
+ return false;
+ }
+
*epqslot = EvalPlanQual(epqstate,
relation,
relinfo->ri_RangeTableIndex,
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 802bea0..e1fc8ff
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -88,15 +88,6 @@ typedef struct ModifyTableContext
*/
TupleTableSlot *planSlot;
- /*
- * During EvalPlanQual, project and return the new version of the new
- * tuple
- */
- TupleTableSlot *(*GetUpdateNewTuple) (ResultRelInfo *resultRelInfo,
- TupleTableSlot *epqslot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
-
/* MERGE specific */
MergeActionState *relaction; /* MERGE action in progress */
@@ -107,12 +98,6 @@ typedef struct ModifyTableContext
TM_FailureData tmfd;
/*
- * The tuple produced by EvalPlanQual to retry from, if a cross-partition
- * UPDATE requires it
- */
- TupleTableSlot *cpUpdateRetrySlot;
-
- /*
* The tuple projected by the INSERT's RETURNING clause, when doing a
* cross-partition UPDATE
*/
@@ -162,10 +147,6 @@ static TupleTableSlot *ExecPrepareTupleR
ResultRelInfo *targetRelInfo,
TupleTableSlot *slot,
ResultRelInfo **partRelInfo);
-static TupleTableSlot *internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
static TupleTableSlot *ExecMerge(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
@@ -179,10 +160,6 @@ static bool ExecMergeMatched(ModifyTable
static void ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
-static TupleTableSlot *mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction);
/*
@@ -719,26 +696,14 @@ ExecGetUpdateNewTuple(ResultRelInfo *rel
TupleTableSlot *planSlot,
TupleTableSlot *oldSlot)
{
+ ProjectionInfo *newProj = relinfo->ri_projectNew;
+ ExprContext *econtext;
+
/* Use a few extra Asserts to protect against outside callers */
Assert(relinfo->ri_projectNewInfoValid);
Assert(planSlot != NULL && !TTS_EMPTY(planSlot));
Assert(oldSlot != NULL && !TTS_EMPTY(oldSlot));
- return internalGetUpdateNewTuple(relinfo, planSlot, oldSlot, NULL);
-}
-
-/*
- * Callback for ModifyTableState->GetUpdateNewTuple for use by regular UPDATE.
- */
-static TupleTableSlot *
-internalGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ProjectionInfo *newProj = relinfo->ri_projectNew;
- ExprContext *econtext;
-
econtext = newProj->pi_exprContext;
econtext->ecxt_outertuple = planSlot;
econtext->ecxt_scantuple = oldSlot;
@@ -1317,8 +1282,11 @@ ExecPendingInserts(EState *estate)
static bool
ExecDeletePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple,
- TupleTableSlot **epqreturnslot)
+ TupleTableSlot **epqreturnslot, TM_Result *result)
{
+ if (result)
+ *result = TM_Ok;
+
/* BEFORE ROW DELETE triggers */
if (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_delete_before_row)
@@ -1327,9 +1295,9 @@ ExecDeletePrologue(ModifyTableContext *c
if (context->estate->es_insert_pending_result_relations != NIL)
ExecPendingInserts(context->estate);
- return ExecBRDeleteTriggers(context->estate, context->epqstate,
- resultRelInfo, tupleid, oldtuple,
- epqreturnslot);
+ return ExecBRDeleteTriggersNew(context->estate, context->epqstate,
+ resultRelInfo, tupleid, oldtuple,
+ epqreturnslot, result, &context->tmfd);
}
return true;
@@ -1446,7 +1414,7 @@ ExecDelete(ModifyTableContext *context,
* done if it says we are.
*/
if (!ExecDeletePrologue(context, resultRelInfo, tupleid, oldtuple,
- epqreturnslot))
+ epqreturnslot, NULL))
return NULL;
/* INSTEAD OF ROW DELETE Triggers */
@@ -1727,8 +1695,10 @@ ldelete:;
*
* False is returned if the tuple we're trying to move is found to have been
* concurrently updated. In that case, the caller must check if the updated
- * tuple (in updateCxt->cpUpdateRetrySlot) still needs to be re-routed, and
- * call this function again or perform a regular update accordingly.
+ * tuple that's returned in *retry_slot still needs to be re-routed, and call
+ * this function again or perform a regular update accordingly. For MERGE,
+ * the updated tuple is not returned in *retry_slot; it has its own retry
+ * logic.
*/
static bool
ExecCrossPartitionUpdate(ModifyTableContext *context,
@@ -1737,6 +1707,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *slot,
bool canSetTag,
UpdateContext *updateCxt,
+ TupleTableSlot **retry_slot,
TupleTableSlot **inserted_tuple,
ResultRelInfo **insert_destrel)
{
@@ -1747,7 +1718,7 @@ ExecCrossPartitionUpdate(ModifyTableCont
TupleTableSlot *epqslot = NULL;
context->cpUpdateReturningSlot = NULL;
- context->cpUpdateRetrySlot = NULL;
+ *retry_slot = NULL;
/*
* Disallow an INSERT ON CONFLICT DO UPDATE that causes the original row
@@ -1826,9 +1797,13 @@ ExecCrossPartitionUpdate(ModifyTableCont
* another transaction has concurrently updated the same row, it
* re-fetches the row, skips the delete, and epqslot is set to the
* re-fetched tuple slot. In that case, we need to do all the checks
- * again.
+ * again. For MERGE, we leave everything to the caller (it must do
+ * additional rechecking, and might end up executing a different
+ * action entirely).
*/
- if (TupIsNull(epqslot))
+ if (context->relaction != NULL)
+ return false;
+ else if (TupIsNull(epqslot))
return true;
else
{
@@ -1845,9 +1820,8 @@ ExecCrossPartitionUpdate(ModifyTableCont
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
/* and project the new tuple to retry the UPDATE with */
- context->cpUpdateRetrySlot =
- context->GetUpdateNewTuple(resultRelInfo, epqslot, oldSlot,
- context->relaction);
+ *retry_slot = ExecGetUpdateNewTuple(resultRelInfo, epqslot,
+ oldSlot);
return false;
}
}
@@ -1888,10 +1862,14 @@ ExecCrossPartitionUpdate(ModifyTableCont
*/
static bool
ExecUpdatePrologue(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot)
+ ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
+ TM_Result *result)
{
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
+ if (result)
+ *result = TM_Ok;
+
ExecMaterializeSlot(slot);
/*
@@ -1910,9 +1888,9 @@ ExecUpdatePrologue(ModifyTableContext *c
if (context->estate->es_insert_pending_result_relations != NIL)
ExecPendingInserts(context->estate);
- return ExecBRUpdateTriggers(context->estate, context->epqstate,
- resultRelInfo, tupleid, oldtuple, slot,
- &context->tmfd);
+ return ExecBRUpdateTriggersNew(context->estate, context->epqstate,
+ resultRelInfo, tupleid, oldtuple, slot,
+ result, &context->tmfd);
}
return true;
@@ -2016,7 +1994,8 @@ lreplace:
*/
if (partition_constraint_failed)
{
- TupleTableSlot *inserted_tuple;
+ TupleTableSlot *inserted_tuple,
+ *retry_slot;
ResultRelInfo *insert_destrel = NULL;
/*
@@ -2028,6 +2007,7 @@ lreplace:
if (ExecCrossPartitionUpdate(context, resultRelInfo,
tupleid, oldtuple, slot,
canSetTag, updateCxt,
+ &retry_slot,
&inserted_tuple,
&insert_destrel))
{
@@ -2072,7 +2052,7 @@ lreplace:
* ExecCrossPartitionUpdate installed an updated version of the new
* tuple in the retry slot; start over.
*/
- slot = context->cpUpdateRetrySlot;
+ slot = retry_slot;
goto lreplace;
}
@@ -2116,10 +2096,10 @@ lreplace:
static void
ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt,
ResultRelInfo *resultRelInfo, ItemPointer tupleid,
- HeapTuple oldtuple, TupleTableSlot *slot,
- List *recheckIndexes)
+ HeapTuple oldtuple, TupleTableSlot *slot)
{
ModifyTableState *mtstate = context->mtstate;
+ List *recheckIndexes = NIL;
/* insert index entries for tuple if necessary */
if (resultRelInfo->ri_NumIndices > 0 && updateCxt->updateIndexes)
@@ -2138,6 +2118,8 @@ ExecUpdateEpilogue(ModifyTableContext *c
mtstate->mt_transition_capture,
false);
+ list_free(recheckIndexes);
+
/*
* Check any WITH CHECK OPTION constraints from parent views. We are
* required to do this after testing all constraints and uniqueness
@@ -2256,7 +2238,6 @@ ExecUpdate(ModifyTableContext *context,
EState *estate = context->estate;
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
UpdateContext updateCxt = {0};
- List *recheckIndexes = NIL;
TM_Result result;
/*
@@ -2269,7 +2250,7 @@ ExecUpdate(ModifyTableContext *context,
* Prepare for the update. This includes BEFORE ROW triggers, so we're
* done if it says we are.
*/
- if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot))
+ if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
return NULL;
/* INSTEAD OF ROW UPDATE Triggers */
@@ -2470,9 +2451,7 @@ redo_act:
(estate->es_processed)++;
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo, tupleid, oldtuple,
- slot, recheckIndexes);
-
- list_free(recheckIndexes);
+ slot);
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
@@ -2839,7 +2818,6 @@ lmerge_matched:;
{
MergeActionState *relaction = (MergeActionState *) lfirst(l);
CmdType commandType = relaction->mas_action->commandType;
- List *recheckIndexes = NIL;
TM_Result result;
UpdateContext updateCxt = {0};
@@ -2886,13 +2864,10 @@ lmerge_matched:;
newslot = ExecProject(relaction->mas_proj);
context->relaction = relaction;
- context->GetUpdateNewTuple = mergeGetUpdateNewTuple;
- context->cpUpdateRetrySlot = NULL;
-
if (!ExecUpdatePrologue(context, resultRelInfo,
- tupleid, NULL, newslot))
+ tupleid, NULL, newslot, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
@@ -2900,18 +2875,17 @@ lmerge_matched:;
if (result == TM_Ok && updateCxt.updated)
{
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo,
- tupleid, NULL, newslot, recheckIndexes);
+ tupleid, NULL, newslot);
mtstate->mt_merge_updated += 1;
}
-
break;
case CMD_DELETE:
context->relaction = relaction;
if (!ExecDeletePrologue(context, resultRelInfo, tupleid,
- NULL, NULL))
+ NULL, NULL, &result))
{
- result = TM_Ok;
+ /* Blocked by trigger, or concurrent update/delete */
break;
}
result = ExecDeleteAct(context, resultRelInfo, tupleid, false);
@@ -2978,34 +2952,13 @@ lmerge_matched:;
/*
* The target tuple was concurrently updated by some other
- * transaction.
- */
-
- /*
- * During an UPDATE, if cpUpdateRetrySlot is set, then
- * ExecCrossPartitionUpdate() must have detected that the
- * tuple was concurrently updated, so we restart the search
- * for an appropriate WHEN MATCHED clause to process the
- * updated tuple.
- *
- * In this case, ExecDelete() would already have performed
- * EvalPlanQual() on the latest version of the tuple,
- * which in turn would already have been loaded into
- * ri_oldTupleSlot, so no need to do either of those
- * things.
- */
- if (commandType == CMD_UPDATE &&
- !TupIsNull(context->cpUpdateRetrySlot))
- goto lmerge_matched;
-
- /*
- * Otherwise, we run the EvalPlanQual() with the new
- * version of the tuple. If EvalPlanQual() does not return
- * a tuple, then we switch to the NOT MATCHED list of
- * actions. If it does return a tuple and the join qual is
- * still satisfied, then we just need to recheck the
- * MATCHED actions, starting from the top, and execute the
- * first qualifying action.
+ * transaction. Run EvalPlanQual() with the new version of
+ * the tuple. If it does not return a tuple, then we
+ * switch to the NOT MATCHED list of actions. If it does
+ * return a tuple and the join qual is still satisfied,
+ * then we just need to recheck the MATCHED actions,
+ * starting from the top, and execute the first qualifying
+ * action.
*/
resultRelationDesc = resultRelInfo->ri_RelationDesc;
lockmode = ExecUpdateLockMode(estate, resultRelInfo);
@@ -3381,25 +3334,6 @@ ExecInitMergeTupleSlots(ModifyTableState
}
/*
- * Callback for ModifyTableContext->GetUpdateNewTuple for use by MERGE. It
- * computes the updated tuple by projecting from the current merge action's
- * projection.
- */
-static TupleTableSlot *
-mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
- TupleTableSlot *planSlot,
- TupleTableSlot *oldSlot,
- MergeActionState *relaction)
-{
- ExprContext *econtext = relaction->mas_proj->pi_exprContext;
-
- econtext->ecxt_scantuple = oldSlot;
- econtext->ecxt_innertuple = planSlot;
-
- return ExecProject(relaction->mas_proj);
-}
-
-/*
* Process BEFORE EACH STATEMENT triggers
*/
static void
@@ -3854,9 +3788,8 @@ ExecModifyTable(PlanState *pstate)
oldSlot))
elog(ERROR, "failed to fetch tuple being updated");
}
- slot = internalGetUpdateNewTuple(resultRelInfo, context.planSlot,
- oldSlot, NULL);
- context.GetUpdateNewTuple = internalGetUpdateNewTuple;
+ slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot,
+ oldSlot);
context.relaction = NULL;
/* Now apply the update. */
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
new file mode 100644
index 0d7558e..fff8d16
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -211,6 +211,14 @@ extern void ExecBSDeleteTriggers(EState
extern void ExecASDeleteTriggers(EState *estate,
ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture);
+extern bool ExecBRDeleteTriggersNew(EState *estate,
+ EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot **epqslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd);
extern bool ExecBRDeleteTriggers(EState *estate,
EPQState *epqstate,
ResultRelInfo *relinfo,
@@ -231,6 +239,14 @@ extern void ExecBSUpdateTriggers(EState
extern void ExecASUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture);
+extern bool ExecBRUpdateTriggersNew(EState *estate,
+ EPQState *epqstate,
+ ResultRelInfo *relinfo,
+ ItemPointer tupleid,
+ HeapTuple fdw_trigtuple,
+ TupleTableSlot *newslot,
+ TM_Result *tmresult,
+ TM_FailureData *tmfd);
extern bool ExecBRUpdateTriggers(EState *estate,
EPQState *epqstate,
ResultRelInfo *relinfo,
diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
new file mode 100644
index b2befa8..897b935
--- a/src/test/isolation/expected/merge-delete.out
+++ b/src/test/isolation/expected/merge-delete.out
@@ -10,20 +10,31 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step select2: SELECT * FROM target;
+step select2_pa: SELECT * FROM target_pa;
key|val
---+---
(0 rows)
step c2: COMMIT;
-starting permutation: delete c1 update1 select2 c2
+starting permutation: delete_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 update2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1;
step select2: SELECT * FROM target;
key|val
---+---
@@ -31,11 +42,23 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete c1 update1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 update2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
-step select2: SELECT * FROM target;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 update2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -45,32 +68,72 @@ step c2: COMMIT;
starting permutation: delete c1 merge2 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete c1 merge2 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+starting permutation: delete_pa c1 merge2_pa select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
step c1: COMMIT;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg c1 merge2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 merge_delete2 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
(1 row)
step c2: COMMIT;
-starting permutation: delete update1 c1 select2 c2
+starting permutation: delete_tg c1 merge_delete2_tg select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE;
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete update2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step update2: UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
+step update2: <... completed>
step select2: SELECT * FROM target;
key|val
---+---
@@ -78,12 +141,25 @@ key|val
step c2: COMMIT;
-starting permutation: merge_delete update1 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+starting permutation: delete_pa update2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step update2_pa: UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; <waiting ...>
step c1: COMMIT;
-step update1: <... completed>
-step select2: SELECT * FROM target;
+step update2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg update2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step update2_tg: UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
key|val
---+---
(0 rows)
@@ -92,26 +168,69 @@ step c2: COMMIT;
starting permutation: delete merge2 c1 select2 c2
step delete: DELETE FROM target t WHERE t.key = 1;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
step merge2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+------
+ 1|merge2
(1 row)
step c2: COMMIT;
-starting permutation: merge_delete merge2 c1 select2 c2
-step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
-step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+starting permutation: delete_pa merge2_pa c1 select2_pa c2
+step delete_pa: DELETE FROM target_pa t WHERE t.key = 1;
+step merge2_pa: MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
step c1: COMMIT;
-step merge2: <... completed>
+step merge2_pa: <... completed>
+step select2_pa: SELECT * FROM target_pa;
+key|val
+---+---------
+ 1|merge2_pa
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge2_tg)
+step merge2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+---------
+ 1|merge2_tg
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete merge_delete2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge_delete2: MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+step merge_delete2: <... completed>
step select2: SELECT * FROM target;
-key|val
----+-------
- 1|merge2a
+key|val
+---+-------------
+ 1|merge_delete2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete_tg merge_delete2_tg c1 select2_tg c2
+s1: NOTICE: Delete: (1,setup1)
+step delete_tg: DELETE FROM target_tg t WHERE t.key = 1;
+step merge_delete2_tg: MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: Insert: (1,merge_delete2_tg)
+step merge_delete2_tg: <... completed>
+step select2_tg: SELECT * FROM target_tg;
+key|val
+---+----------------
+ 1|merge_delete2_tg
(1 row)
step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 8183f52..9a44a59
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -23,6 +23,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update1_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,170,s1,"setup updated by update1_tg") -> (1,170,s2,"setup updated by update1_tg when1")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 170|s2 |setup updated by update1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update2 merge_status c2 select1 c1
step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
step merge_status:
@@ -46,6 +71,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update2_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s2,"setup updated by update2_tg")
+step update2_tg: UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s2,"setup updated by update2_tg") -> (1,160,s3,"setup updated by update2_tg when2")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s3 |setup updated by update2_tg when2
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update3 merge_status c2 select1 c1
step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
step merge_status:
@@ -69,6 +119,31 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update3_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s3,"setup updated by update3_tg")
+step update3_tg: UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,160,s3,"setup updated by update3_tg") -> (1,160,s4,"setup updated by update3_tg when3")
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------------
+ 1| 160|s4 |setup updated by update3_tg when3
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update5 merge_status c2 select1 c1
step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
step merge_status:
@@ -92,6 +167,30 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update5_tg merge_status_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,160,s5,"setup updated by update5_tg")
+step update5_tg: UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1;
+step merge_status_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---------------------------
+ 1| 160|s5 |setup updated by update5_tg
+(1 row)
+
+step c1: COMMIT;
+
starting permutation: update_bal1 merge_bal c2 select1 c1
step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
step merge_bal:
@@ -114,3 +213,137 @@ key|balance|status|val
(1 row)
step c1: COMMIT;
+
+starting permutation: update_bal1_pa merge_bal_pa c2 select1_pa c1
+step update_bal1_pa: UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1;
+step merge_bal_pa:
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal_pa: <... completed>
+step select1_pa: SELECT * FROM target_pa;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_pa when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_bal_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_bal_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_bal_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update1 merge_delete c2 select1 c1
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,170,s1,"setup updated by update1_tg")
+step update1_tg: UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Delete: (1,170,s1,"setup updated by update1_tg")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1 merge_delete c2 select1 c1
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_delete:
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+step merge_delete: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+----------------------------------
+ 1| 100|s1 |setup updated by update_bal1 when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1_tg merge_delete_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,50,s1,"setup updated by update_bal1_tg")
+step update_bal1_tg: UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1;
+step merge_delete_tg:
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Update: (1,50,s1,"setup updated by update_bal1_tg") -> (1,100,s1,"setup updated by update_bal1_tg when1")
+step merge_delete_tg: <... completed>
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+-------------------------------------
+ 1| 100|s1 |setup updated by update_bal1_tg when1
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
new file mode 100644
index 0e70532..ba5f70e
--- a/src/test/isolation/specs/merge-delete.spec
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -7,11 +7,39 @@ setup
{
CREATE TABLE target (key int primary key, val text);
INSERT INTO target VALUES (1, 'setup1');
+
+ CREATE TABLE target_pa (key int primary key, val text) PARTITION BY LIST (key);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES IN (1);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES IN (2);
+ INSERT INTO target_pa VALUES (1, 'setup1');
+
+ CREATE TABLE target_tg (key int primary key, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 'setup1');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -20,7 +48,8 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "delete" { DELETE FROM target t WHERE t.key = 1; }
-step "merge_delete" { MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE; }
+step "delete_pa" { DELETE FROM target_pa t WHERE t.key = 1; }
+step "delete_tg" { DELETE FROM target_tg t WHERE t.key = 1; }
step "c1" { COMMIT; }
session "s2"
@@ -28,23 +57,40 @@ setup
{
BEGIN ISOLATION LEVEL READ COMMITTED;
}
-step "update1" { UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; }
-step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "update2" { UPDATE target t SET val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_pa" { UPDATE target_pa t SET val = t.val || ' updated by update2_pa' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_pa" { MERGE INTO target_pa t USING (SELECT 1 as key, 'merge2_pa' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "merge_delete2" { MERGE INTO target t USING (SELECT 1 as key, 'merge_delete2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
+step "merge_delete2_tg" { MERGE INTO target_tg t USING (SELECT 1 as key, 'merge_delete2_tg' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN DELETE; }
step "select2" { SELECT * FROM target; }
+step "select2_pa" { SELECT * FROM target_pa; }
+step "select2_tg" { SELECT * FROM target_tg; }
step "c2" { COMMIT; }
# Basic effects
permutation "delete" "c1" "select2" "c2"
-permutation "merge_delete" "c1" "select2" "c2"
+permutation "delete_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "c1" "select2_tg" "c2"
# One after the other, no concurrency
-permutation "delete" "c1" "update1" "select2" "c2"
-permutation "merge_delete" "c1" "update1" "select2" "c2"
+permutation "delete" "c1" "update2" "select2" "c2"
+permutation "delete_pa" "c1" "update2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "update2_tg" "select2_tg" "c2"
permutation "delete" "c1" "merge2" "select2" "c2"
-permutation "merge_delete" "c1" "merge2" "select2" "c2"
+permutation "delete_pa" "c1" "merge2_pa" "select2_pa" "c2"
+permutation "delete_tg" "c1" "merge2_tg" "select2_tg" "c2"
+permutation "delete" "c1" "merge_delete2" "select2" "c2"
+permutation "delete_tg" "c1" "merge_delete2_tg" "select2_tg" "c2"
# Now with concurrency
-permutation "delete" "update1" "c1" "select2" "c2"
-permutation "merge_delete" "update1" "c1" "select2" "c2"
+permutation "delete" "update2" "c1" "select2" "c2"
+permutation "delete_pa" "update2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "update2_tg" "c1" "select2_tg" "c2"
permutation "delete" "merge2" "c1" "select2" "c2"
-permutation "merge_delete" "merge2" "c1" "select2" "c2"
+permutation "delete_pa" "merge2_pa" "c1" "select2_pa" "c2"
+permutation "delete_tg" "merge2_tg" "c1" "select2_tg" "c2"
+permutation "delete" "merge_delete2" "c1" "select2" "c2"
+permutation "delete_tg" "merge_delete2_tg" "c1" "select2_tg" "c2"
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index d56400a..298b2bf
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -8,11 +8,39 @@ setup
{
CREATE TABLE target (key int primary key, balance integer, status text, val text);
INSERT INTO target VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_pa (key int, balance integer, status text, val text) PARTITION BY RANGE (balance);
+ CREATE TABLE target_pa1 PARTITION OF target_pa FOR VALUES FROM (0) TO (200);
+ CREATE TABLE target_pa2 PARTITION OF target_pa FOR VALUES FROM (200) TO (1000);
+ INSERT INTO target_pa VALUES (1, 160, 's1', 'setup');
+
+ CREATE TABLE target_tg (key int primary key, balance integer, status text, val text);
+ CREATE FUNCTION target_tg_trig_fn() RETURNS trigger LANGUAGE plpgsql AS
+ $$
+ BEGIN
+ IF tg_op = 'INSERT' THEN
+ RAISE NOTICE 'Insert: %', NEW;
+ RETURN NEW;
+ ELSIF tg_op = 'UPDATE' THEN
+ RAISE NOTICE 'Update: % -> %', OLD, NEW;
+ RETURN NEW;
+ ELSE
+ RAISE NOTICE 'Delete: %', OLD;
+ RETURN OLD;
+ END IF;
+ END
+ $$;
+ CREATE TRIGGER target_tg_trig BEFORE INSERT OR UPDATE OR DELETE ON target_tg
+ FOR EACH ROW EXECUTE FUNCTION target_tg_trig_fn();
+ INSERT INTO target_tg VALUES (1, 160, 's1', 'setup');
}
teardown
{
DROP TABLE target;
+ DROP TABLE target_pa;
+ DROP TABLE target_tg;
+ DROP FUNCTION target_tg_trig_fn;
}
session "s1"
@@ -32,6 +60,18 @@ step "merge_status"
WHEN MATCHED AND status = 's3' THEN
UPDATE SET status = 's4', val = t.val || ' when3';
}
+step "merge_status_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND status = 's1' THEN
+ UPDATE SET status = 's2', val = t.val || ' when1'
+ WHEN MATCHED AND status = 's2' THEN
+ UPDATE SET status = 's3', val = t.val || ' when2'
+ WHEN MATCHED AND status = 's3' THEN
+ UPDATE SET status = 's4', val = t.val || ' when3';
+}
step "merge_bal"
{
@@ -45,8 +85,55 @@ step "merge_bal"
WHEN MATCHED AND balance < 300 THEN
UPDATE SET balance = balance * 8, val = t.val || ' when3';
}
+step "merge_bal_pa"
+{
+ MERGE INTO target_pa t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+step "merge_bal_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ UPDATE SET balance = balance * 4, val = t.val || ' when2'
+ WHEN MATCHED AND balance < 300 THEN
+ UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+
+step "merge_delete"
+{
+ MERGE INTO target t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
+step "merge_delete_tg"
+{
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 100 THEN
+ UPDATE SET balance = balance * 2, val = t.val || ' when1'
+ WHEN MATCHED AND balance < 200 THEN
+ DELETE;
+}
step "select1" { SELECT * FROM target; }
+step "select1_pa" { SELECT * FROM target_pa; }
+step "select1_tg" { SELECT * FROM target_tg; }
step "c1" { COMMIT; }
session "s2"
@@ -55,23 +142,43 @@ setup
BEGIN ISOLATION LEVEL READ COMMITTED;
}
step "update1" { UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "update1_tg" { UPDATE target_tg t SET balance = balance + 10, val = t.val || ' updated by update1_tg' WHERE t.key = 1; }
step "update2" { UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update2_tg" { UPDATE target_tg t SET status = 's2', val = t.val || ' updated by update2_tg' WHERE t.key = 1; }
step "update3" { UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1; }
+step "update3_tg" { UPDATE target_tg t SET status = 's3', val = t.val || ' updated by update3_tg' WHERE t.key = 1; }
step "update5" { UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1; }
+step "update5_tg" { UPDATE target_tg t SET status = 's5', val = t.val || ' updated by update5_tg' WHERE t.key = 1; }
step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
+step "update_bal1_pa" { UPDATE target_pa t SET balance = 50, val = t.val || ' updated by update_bal1_pa' WHERE t.key = 1; }
+step "update_bal1_tg" { UPDATE target_tg t SET balance = 50, val = t.val || ' updated by update_bal1_tg' WHERE t.key = 1; }
step "c2" { COMMIT; }
# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
permutation "update1" "merge_status" "c2" "select1" "c1"
+permutation "update1_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
permutation "update2" "merge_status" "c2" "select1" "c1"
+permutation "update2_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
permutation "update3" "merge_status" "c2" "select1" "c1"
+permutation "update3_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, but we skip update and MERGE does nothing
permutation "update5" "merge_status" "c2" "select1" "c1"
+permutation "update5_tg" "merge_status_tg" "c2" "select1_tg" "c1"
# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
+permutation "update_bal1_pa" "merge_bal_pa" "c2" "select1_pa" "c1"
+permutation "update_bal1_tg" "merge_bal_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, but recheck passes and row is deleted
+permutation "update1" "merge_delete" "c2" "select1" "c1"
+permutation "update1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
+
+# merge_delete sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance is 100
+permutation "update_bal1" "merge_delete" "c2" "select1" "c1"
+permutation "update_bal1_tg" "merge_delete_tg" "c2" "select1_tg" "c1"
* Re: BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently
@ 2023-03-13 11:42 Dean Rasheed <dean.a.rasheed@gmail.com>
parent: Dean Rasheed <dean.a.rasheed@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dean Rasheed @ 2023-03-13 11:42 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
On Sun, 12 Mar 2023 at 11:14, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Sat, 11 Mar 2023 at 18:12, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> >
> > Attached is an updated patch that I'm more happy with now, and a
> > slightly modified one for v15, keeping the trigger API
> > backwards-compatible for extensions.
>
> If a BEFORE ROW trigger returns NULL to skip an update or delete,
> ExecMergeMatched() will still update estate->es_processed, and so the
> final row count in the command tag will be wrong. I'll tackle that in
> a follow-on patch, since it's really a separate bug.
>
OK, I have pushed fixes for both those issues.
Regards,
Dean
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-03-13 11:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-26 10:00 BUG #17809: MERGE ... UPDATE fails with BEFORE ROW UPDATE trigger when target row updated concurrently PG Bug reporting form <noreply@postgresql.org>
2023-02-27 12:07 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2023-03-07 11:50 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-09 13:11 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2023-03-11 18:12 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2023-03-12 11:14 ` Dean Rasheed <dean.a.rasheed@gmail.com>
2023-03-13 11:42 ` Dean Rasheed <dean.a.rasheed@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox