public inbox for [email protected]
help / color / mirror / Atom feedBUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
7+ messages / 3 participants
[nested] [flat]
* BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
@ 2026-06-24 15:22 PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2026-06-24 15:22 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19536
Logged by: Jonas Boberg
Email address: [email protected]
PostgreSQL version: 18.4
Operating system: Docker image postgres:18.4-alpine3.23
Description:
In an UPDATE ... RETURNING OLD under isolation level READ COMMITTED, when
the table has a BEFORE UPDATE trigger, the OLD value is stale after a
concurrent update.
SELECT version();
> PostgreSQL 18.4 on aarch64-unknown-linux-musl, compiled by gcc (Alpine
15.2.0) 15.2.0, 64-bit
## How to reproduce
Setup:
-------
CREATE TABLE t (
id int PRIMARY KEY,
n int NOT NULL
);
CREATE FUNCTION before_update_noop()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
RETURN NEW;
END;
$$;
CREATE TRIGGER t_before_update
BEFORE UPDATE ON t
FOR EACH ROW EXECUTE FUNCTION before_update_noop();
INSERT INTO t VALUES (1, 7);
-----
psql session 1:
------
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t
SET n = n + 10
WHERE id = 1;
-- do not commit, hold the row lock
------
Leave session 1 open
psql session 2:
-----
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t
SET n = n + 1
WHERE id = 1
RETURNING
OLD.n AS old_n,
NEW.n AS new_n,
NEW.n - 1 AS expected_old;
----
Session 2 blocks. Now commit in session 1:
----
COMMIT;
----
## Actual result
Session 2 outputs:
old_n | new_n | expected_old
-------+-------+--------------
7 | 18 | 17
(1 row)
Here OLD.n is the pre-concurrent update value, while NEW.n is the value
written by the concurrent update.
## Expected result
old_n | new_n | expected_old
-------+-------+--------------
17 | 18 | 17
If I drop the trigger and repeat the test, session 2 returns the expected
result.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
@ 2026-06-27 19:22 ` Bharath Rupireddy <[email protected]>
2026-06-28 09:59 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bharath Rupireddy @ 2026-06-27 19:22 UTC (permalink / raw)
To: [email protected]; [email protected]; +Cc: Dean Rasheed <[email protected]>
Hi,
On Sat, Jun 27, 2026 at 5:54 AM PG Bug reporting form
<[email protected]> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19536
> Logged by: Jonas Boberg
> Email address: [email protected]
> PostgreSQL version: 18.4
> Operating system: Docker image postgres:18.4-alpine3.23
> Description:
Thanks for reporting the bug and a reproducer!
> In an UPDATE ... RETURNING OLD under isolation level READ COMMITTED, when
> the table has a BEFORE UPDATE trigger, the OLD value is stale after a
> concurrent update.
>
> SELECT version();
> > PostgreSQL 18.4 on aarch64-unknown-linux-musl, compiled by gcc (Alpine
> 15.2.0) 15.2.0, 64-bit
>
> ## How to reproduce
>
> Setup:
> -------
> CREATE TABLE t (
> id int PRIMARY KEY,
> n int NOT NULL
> );
>
> CREATE FUNCTION before_update_noop()
> RETURNS trigger
> LANGUAGE plpgsql AS $$
> BEGIN
> RETURN NEW;
> END;
> $$;
>
> CREATE TRIGGER t_before_update
> BEFORE UPDATE ON t
> FOR EACH ROW EXECUTE FUNCTION before_update_noop();
>
> INSERT INTO t VALUES (1, 7);
> -----
>
> psql session 1:
> ------
> BEGIN ISOLATION LEVEL READ COMMITTED;
>
> UPDATE t
> SET n = n + 10
> WHERE id = 1;
> -- do not commit, hold the row lock
> ------
> Leave session 1 open
>
> psql session 2:
> -----
> BEGIN ISOLATION LEVEL READ COMMITTED;
> UPDATE t
> SET n = n + 1
> WHERE id = 1
> RETURNING
> OLD.n AS old_n,
> NEW.n AS new_n,
> NEW.n - 1 AS expected_old;
> ----
> Session 2 blocks. Now commit in session 1:
> ----
> COMMIT;
> ----
>
> ## Actual result
>
> Session 2 outputs:
> old_n | new_n | expected_old
> -------+-------+--------------
> 7 | 18 | 17
> (1 row)
>
> Here OLD.n is the pre-concurrent update value, while NEW.n is the value
> written by the concurrent update.
>
> ## Expected result
>
> old_n | new_n | expected_old
> -------+-------+--------------
> 17 | 18 | 17
>
> If I drop the trigger and repeat the test, session 2 returns the expected
> result.
I reproduced it on HEAD.
Backend 1 stores its txn-id in the old row's xmax (ctid=(0,1), n=7)
and is still in progress.
Backend 2 tries to lock the same row, sees that xmax holds an
in-progress txn, and waits (ExecUpdate -> ExecUpdatePrologue ->
ExecBRUpdateTriggers -> GetTupleForTrigger -> table_tuple_lock ->
heap_lock_tuple -> XactLockTableWait).
When backend 1 commits, backend 2 wakes up. Because the lock was
requested with TUPLE_LOCK_FLAG_FIND_LAST_VERSION (set under READ
COMMITTED in GetTupleForTrigger), heap_lock_tuple walks the
row-version chain to the latest version and advances tupleid from
(ctid=(0,1), n=7) to (ctid=(0,2), n=17).
In ExecUpdate after ExecUpdatePrologue, tupleid now points at the
latest version (ctid=(0,2), n=17) and the lock is already held, so
table_tuple_update has nothing to wait on and returns TM_Ok. But the
oldSlot - the slot RETURNING clause reads from - is only refreshed on
the TM_Updated path. Since we got TM_Ok, oldSlot still holds the value
captured before waiting (ctid=(0,1), n=7). NEW is correct (ctid=(0,3),
n=18).
In short: with a BEFORE UPDATE trigger, the trigger's tuple lock
advances tupleid to the concurrently-updated row version, so the later
table_tuple_update returns TM_Ok instead of TM_Updated. Since oldSlot
is only refreshed on the TM_Updated path, the RETURNING clause keeps
the pre-wait value (ctid=(0,1), n=7) instead of the actual
concurrently-updated value (ctid=(0,2), n=17). Without the trigger,
the wait happens inside table_tuple_update itself, which returns
TM_Updated and correctly refreshes oldSlot.
I'm adding Dean Rasheed (commit 80feb727c86 v18 - Add OLD/NEW support
to RETURNING in DML queries.) for additional inputs.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
@ 2026-06-28 09:59 ` Dean Rasheed <[email protected]>
2026-06-28 23:34 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Dean Rasheed @ 2026-06-28 09:59 UTC (permalink / raw)
To: Bharath Rupireddy <[email protected]>; +Cc: [email protected]; [email protected]
On Sat, 27 Jun 2026 at 20:22, Bharath Rupireddy
<[email protected]> wrote:
>
> In short: with a BEFORE UPDATE trigger, the trigger's tuple lock
> advances tupleid to the concurrently-updated row version, so the later
> table_tuple_update returns TM_Ok instead of TM_Updated. Since oldSlot
> is only refreshed on the TM_Updated path, the RETURNING clause keeps
> the pre-wait value (ctid=(0,1), n=7) instead of the actual
> concurrently-updated value (ctid=(0,2), n=17). Without the trigger,
> the wait happens inside table_tuple_update itself, which returns
> TM_Updated and correctly refreshes oldSlot.
Yes, that analysis seems correct.
This doesn't affect DELETE, because the DELETE code always fetches the
most recent version of the old tuple just before processing the
RETURNING clause. Similarly, it doesn't affect a cross-partition
UPDATE, which does a DELETE followed by an INSERT.
It also doesn't affect MERGE UPDATE/DELETE, because that does its own
EPQ handling, rather than relying on the trigger code to do it (see
9321c79c86e).
So I think we just need something like the attached.
Regards,
Dean
Attachments:
[text/x-patch] fix-returning-old-with-before-update-trigger.patch (21.8K, ../../CAEZATCW8q47GDba1o+Ji9JUhCHFFbB35D9qMSbBhboCx0GXkQQ@mail.gmail.com/2-fix-returning-old-with-before-update-trigger.patch)
download | inline diff:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index c333d71..d059851
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2765,9 +2765,27 @@ ExecUpdate(ModifyTableContext *context,
* Prepare for the update. This includes BEFORE ROW triggers, so we're
* done if it says we are.
*/
+ context->tmfd.traversed = false;
if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
return NULL;
+ /*
+ * If the target tuple was concurrently updated, the trigger code will
+ * have done EPQ and updated tupleid, following the update chain. In this
+ * case, we must fetch the most recent version of old tuple for the
+ * benefit of RETURNING. Technically, we could get away with not doing
+ * this, if there is no RETURNING clause, but it seems preferable to
+ * always ensure that the contents of oldSlot are correct.
+ */
+ if (context->tmfd.traversed)
+ {
+ if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc,
+ tupleid,
+ SnapshotAny,
+ oldSlot))
+ elog(ERROR, "failed to fetch tuple being updated");
+ }
+
/* INSTEAD OF ROW UPDATE Triggers */
if (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_update_instead_row)
diff --git a/src/test/isolation/expected/eval-plan-qual-trigger.out b/src/test/isolation/expected/eval-plan-qual-trigger.out
new file mode 100644
index f6714c2..de3aacf
--- a/src/test/isolation/expected/eval-plan-qual-trigger.out
+++ b/src/test/isolation/expected/eval-plan-qual-trigger.out
@@ -61,11 +61,11 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -132,11 +132,11 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -343,7 +343,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -352,9 +352,9 @@ s2: NOTICE: trigger: name rep_b_u; when
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -417,16 +417,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -491,7 +491,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -500,9 +500,9 @@ s2: NOTICE: trigger: name rep_b_u; when
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -567,16 +567,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -641,13 +641,13 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -711,16 +711,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -873,7 +873,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -881,9 +881,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+----------------------
-key-a|val-a-s1-ups1-upserts2
+key |data |check_old_and_new
+-----+----------------------+-----------------
+key-a|val-a-s1-ups1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -955,7 +955,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -963,9 +963,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+----------------------
-key-a|val-a-s1-ups1-upserts2
+key |data |check_old_and_new
+-----+----------------------+-----------------
+key-a|val-a-s1-ups1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -1012,7 +1012,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1020,9 +1020,9 @@ s2: NOTICE: upk: text val-a-s1 <> text
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+-----------------
-key-a|val-a-s1-upserts2
+key |data |check_old_and_new
+-----+-----------------+-----------------
+key-a|val-a-s1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -1068,14 +1068,14 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_a_i; when: AFTER; lev: ROWs; op: INSERT; old: <NULL> new: (key-a,val-a-upss2)
step s2_upsert_a_data: <... completed>
-key |data
------+-----------
-key-a|val-a-upss2
+key |data |check_old_and_new
+-----+-----------+-----------------
+key-a|val-a-upss2|
(1 row)
step s2_c: COMMIT;
@@ -1137,7 +1137,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1145,9 +1145,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+----------------------
-key-a|val-a-s1-ups1-upserts2
+key |data |check_old_and_new
+-----+----------------------+-----------------
+key-a|val-a-s1-ups1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -1209,14 +1209,14 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_a_i; when: AFTER; lev: ROWs; op: INSERT; old: <NULL> new: (key-a,val-a-upss2)
step s2_upsert_a_data: <... completed>
-key |data
------+-----------
-key-a|val-a-upss2
+key |data |check_old_and_new
+-----+-----------+-----------------
+key-a|val-a-upss2|
(1 row)
step s2_c: COMMIT;
@@ -1276,7 +1276,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1284,9 +1284,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -1347,15 +1347,15 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -1557,13 +1557,13 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -1624,15 +1624,15 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -1829,14 +1829,14 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: upd: text key-c = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -1899,16 +1899,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-c = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -2038,7 +2038,7 @@ step s2_upd_all_data:
WHERE
noisy_oper('upd', key, '<>', 'mismatch') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b <> text mismatch: t
@@ -2050,10 +2050,10 @@ s2: NOTICE: trigger: name rep_b_u; when
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-b,val-a-s1-tobs1) new: (key-b,val-a-s1-tobs1-ups2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-c,val-c-s1) new: (key-c,val-c-s1-ups2)
step s2_upd_all_data: <... completed>
-key |data
------+-------------------
-key-b|val-a-s1-tobs1-ups2
-key-c|val-c-s1-ups2
+key |data |check_old_and_new
+-----+-------------------+-----------------
+key-b|val-a-s1-tobs1-ups2|t
+key-c|val-c-s1-ups2 |t
(2 rows)
step s2_c: COMMIT;
@@ -2118,13 +2118,13 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-c = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -2188,16 +2188,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-c = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -2507,7 +2507,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
step s2_upd_a_data: <... completed>
@@ -2572,16 +2572,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -2646,7 +2646,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
step s2_upd_a_data: <... completed>
@@ -2712,16 +2712,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
diff --git a/src/test/isolation/specs/eval-plan-qual-trigger.spec b/src/test/isolation/specs/eval-plan-qual-trigger.spec
new file mode 100644
index 232b3e2..dd956a4
--- a/src/test/isolation/specs/eval-plan-qual-trigger.spec
+++ b/src/test/isolation/specs/eval-plan-qual-trigger.spec
@@ -127,7 +127,7 @@ step s2_upd_a_data {
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
}
step s2_upd_b_data {
UPDATE trigtest SET data = data || '-ups2'
@@ -141,7 +141,7 @@ step s2_upd_all_data {
WHERE
noisy_oper('upd', key, '<>', 'mismatch') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
}
step s2_upsert_a_data {
INSERT INTO trigtest VALUES ('key-a', 'val-a-upss2')
@@ -150,7 +150,7 @@ step s2_upsert_a_data {
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
}
session s3
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
2026-06-28 09:59 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
@ 2026-06-28 23:34 ` Bharath Rupireddy <[email protected]>
2026-06-30 08:35 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bharath Rupireddy @ 2026-06-28 23:34 UTC (permalink / raw)
To: Dean Rasheed <[email protected]>; +Cc: [email protected]; [email protected]
Hi,
On Sun, Jun 28, 2026 at 2:59 AM Dean Rasheed <[email protected]> wrote:
>
> On Sat, 27 Jun 2026 at 20:22, Bharath Rupireddy
> <[email protected]> wrote:
> >
> > In short: with a BEFORE UPDATE trigger, the trigger's tuple lock
> > advances tupleid to the concurrently-updated row version, so the later
> > table_tuple_update returns TM_Ok instead of TM_Updated. Since oldSlot
> > is only refreshed on the TM_Updated path, the RETURNING clause keeps
> > the pre-wait value (ctid=(0,1), n=7) instead of the actual
> > concurrently-updated value (ctid=(0,2), n=17). Without the trigger,
> > the wait happens inside table_tuple_update itself, which returns
> > TM_Updated and correctly refreshes oldSlot.
>
> Yes, that analysis seems correct.
Thanks for the patch! This matches the fix I had in mind after my
analysis as well.
A couple of comments:
1/ I think the refetch (which is only needed when RETURNING references
the OLD tuple) seems unnecessary. Fetching the row version comes with
an additional buffer pool lookup (possibly a cache miss), a buffer
pin, etc. Could we instead reuse the tuple that GetTupleForTrigger has
already fetched? I might be overthinking the performance overhead
here, but on busy production systems we can't ignore these costs.
Others may have a different take.
2/
+ context->tmfd.traversed = false;
if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple,
slot, NULL))
return NULL;
Setting traversed to false here seems a bit off. Is this needed
because the caller doesn't initialize ModifyTableContext properly?
3/
> This doesn't affect DELETE, because the DELETE code always fetches the
> most recent version of the old tuple just before processing the
> RETURNING clause.
>
> Similarly, it doesn't affect a cross-partition
> UPDATE, which does a DELETE followed by an INSERT.
>
> It also doesn't affect MERGE UPDATE/DELETE, because that does its own
> EPQ handling, rather than relying on the trigger code to do it (see
> 9321c79c86e).
I haven't checked these paths in depth, but I think it's worth adding
tests for all of them.
4/
+ if (context->tmfd.traversed)
+ {
+ if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc,
+ tupleid,
+ SnapshotAny,
+ oldSlot))
+ elog(ERROR, "failed to fetch tuple being updated");
+ }
Could we add some context to the error message, e.g., mentioning that
this happens after processing the BEFORE UPDATE row triggers? That
would make it easier to distinguish from the other similar ones.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
2026-06-28 09:59 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
2026-06-28 23:34 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
@ 2026-06-30 08:35 ` Dean Rasheed <[email protected]>
2026-07-07 07:38 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Dean Rasheed @ 2026-06-30 08:35 UTC (permalink / raw)
To: Bharath Rupireddy <[email protected]>; +Cc: [email protected]; [email protected]
On Mon, 29 Jun 2026 at 00:35, Bharath Rupireddy
<[email protected]> wrote:
>
> Thanks for the patch! This matches the fix I had in mind after my
> analysis as well.
>
> A couple of comments:
>
> 1/ I think the refetch (which is only needed when RETURNING references
> the OLD tuple) seems unnecessary. Fetching the row version comes with
> an additional buffer pool lookup (possibly a cache miss), a buffer
> pin, etc. Could we instead reuse the tuple that GetTupleForTrigger has
> already fetched? I might be overthinking the performance overhead
> here, but on busy production systems we can't ignore these costs.
> Others may have a different take.
Thanks for reviewing.
I did think of that, but I was uneasy about copying the trigger's old
tuple after trigger invocation, in case the trigger scribbled on it
somehow. I think that's not possible, but it just didn't seem good
practice to rely on it being unchanged. ExecBRUpdateTriggers() could
copy and return oldslot before invoking the triggers, but that would
require changing its API. In the end, I think that in the context of
having just waited for the other session to commit or rollback, and
then executing trigger code, the cost of refetching the tuple ought to
be negligible, so trying to reuse the tuple that the trigger code
fetched feels like an unnecessary optimisation.
Actually, I think a neater long-term solution would be to move all the
EPQ rechecking and re-computing of the new tuple out of trigger.c, and
let the caller deal with it, as we do for the MERGE code, or perhaps
even make the caller responsible for locking the old tuple, but that's
beyond the scope of this kind of bug fix.
> 2/
> + context->tmfd.traversed = false;
> if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple,
> slot, NULL))
> return NULL;
>
> Setting traversed to false here seems a bit off. Is this needed
> because the caller doesn't initialize ModifyTableContext properly?
Yes, that's right. The caller does not initialise context->tmfd at
all, and ExecUpdatePrologue() doesn't touch it if there are no
triggers. The other way to do it would have been to make
ExecUpdatePrologue() always set it, but I prefer this way, since it's
more obvious that ExecUpdate() is responding to ExecUpdatePrologue()
changing it (kind-of like setting errno to 0 before calling a function
that might set it to a non-zero value).
> 3/
> > This doesn't affect DELETE, because the DELETE code always fetches the
> > most recent version of the old tuple just before processing the
> > RETURNING clause.
> >
> > Similarly, it doesn't affect a cross-partition
> > UPDATE, which does a DELETE followed by an INSERT.
> >
> > It also doesn't affect MERGE UPDATE/DELETE, because that does its own
> > EPQ handling, rather than relying on the trigger code to do it (see
> > 9321c79c86e).
>
> I haven't checked these paths in depth, but I think it's worth adding
> tests for all of them.
OK, I've added a bunch of tests for those cases. As expected, they
pass even without the code changes, but it's good to confirm that.
> 4/
> + if (context->tmfd.traversed)
> + {
> + if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc,
> + tupleid,
> + SnapshotAny,
> + oldSlot))
> + elog(ERROR, "failed to fetch tuple being updated");
> + }
>
> Could we add some context to the error message, e.g., mentioning that
> this happens after processing the BEFORE UPDATE row triggers? That
> would make it easier to distinguish from the other similar ones.
I'm pretty sure this is a can't-happen error condition, but OK.
Regards,
Dean
Attachments:
[text/x-patch] v2-fix-returning-old-with-before-update-trigger.patch (43.8K, ../../CAEZATCWXA4fkO84u+i7KNS3PzC6ak4atnDAABHdax-H-jukU3w@mail.gmail.com/2-v2-fix-returning-old-with-before-update-trigger.patch)
download | inline diff:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index c333d71..b9781eb
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2765,9 +2765,28 @@ ExecUpdate(ModifyTableContext *context,
* Prepare for the update. This includes BEFORE ROW triggers, so we're
* done if it says we are.
*/
+ context->tmfd.traversed = false;
if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, oldtuple, slot, NULL))
return NULL;
+ /*
+ * If the target tuple was concurrently updated, the trigger code will
+ * have done EPQ and updated tupleid, following the update chain. In this
+ * case, we must fetch the most recent version of old tuple for the
+ * benefit of RETURNING. Technically, we could get away with not doing
+ * this, if there is no RETURNING clause, or it doesn't refer to OLD, but
+ * it seems preferable to always ensure that the contents of oldSlot are
+ * correct.
+ */
+ if (context->tmfd.traversed)
+ {
+ if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc,
+ tupleid,
+ SnapshotAny,
+ oldSlot))
+ elog(ERROR, "failed to re-fetch tuple updated during trigger execution");
+ }
+
/* INSTEAD OF ROW UPDATE Triggers */
if (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_update_instead_row)
diff --git a/src/test/isolation/expected/eval-plan-qual-trigger.out b/src/test/isolation/expected/eval-plan-qual-trigger.out
new file mode 100644
index f6714c2..eca8606
--- a/src/test/isolation/expected/eval-plan-qual-trigger.out
+++ b/src/test/isolation/expected/eval-plan-qual-trigger.out
@@ -61,11 +61,11 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -132,11 +132,11 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -205,11 +205,11 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
-key |data
------+-------------
-key-a|val-a-s1-ups1
+key |data |check_old
+-----+-------------+---------
+key-a|val-a-s1-ups1|t
(1 row)
step s2_c: COMMIT;
@@ -277,11 +277,11 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
-key |data
------+--------
-key-a|val-a-s1
+key |data |check_old
+-----+--------+---------
+key-a|val-a-s1|t
(1 row)
step s2_c: COMMIT;
@@ -343,7 +343,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -352,9 +352,9 @@ s2: NOTICE: trigger: name rep_b_u; when
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -417,16 +417,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -491,7 +491,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -500,9 +500,9 @@ s2: NOTICE: trigger: name rep_b_u; when
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -567,16 +567,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -641,13 +641,13 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -711,16 +711,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -873,7 +873,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -881,9 +881,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+----------------------
-key-a|val-a-s1-ups1-upserts2
+key |data |check_old_and_new
+-----+----------------------+-----------------
+key-a|val-a-s1-ups1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -955,7 +955,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -963,9 +963,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+----------------------
-key-a|val-a-s1-ups1-upserts2
+key |data |check_old_and_new
+-----+----------------------+-----------------
+key-a|val-a-s1-ups1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -1012,7 +1012,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1020,9 +1020,9 @@ s2: NOTICE: upk: text val-a-s1 <> text
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+-----------------
-key-a|val-a-s1-upserts2
+key |data |check_old_and_new
+-----+-----------------+-----------------
+key-a|val-a-s1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -1068,14 +1068,14 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_a_i; when: AFTER; lev: ROWs; op: INSERT; old: <NULL> new: (key-a,val-a-upss2)
step s2_upsert_a_data: <... completed>
-key |data
------+-----------
-key-a|val-a-upss2
+key |data |check_old_and_new
+-----+-----------+-----------------
+key-a|val-a-upss2|
(1 row)
step s2_c: COMMIT;
@@ -1137,7 +1137,7 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1145,9 +1145,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-upserts2)
step s2_upsert_a_data: <... completed>
-key |data
------+----------------------
-key-a|val-a-s1-ups1-upserts2
+key |data |check_old_and_new
+-----+----------------------+-----------------
+key-a|val-a-s1-ups1-upserts2|t
(1 row)
step s2_c: COMMIT;
@@ -1209,14 +1209,14 @@ step s2_upsert_a_data:
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_a_i; when: AFTER; lev: ROWs; op: INSERT; old: <NULL> new: (key-a,val-a-upss2)
step s2_upsert_a_data: <... completed>
-key |data
------+-----------
-key-a|val-a-upss2
+key |data |check_old_and_new
+-----+-----------+-----------------
+key-a|val-a-upss2|
(1 row)
step s2_c: COMMIT;
@@ -1276,7 +1276,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1284,9 +1284,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1-ups1) new: (key-a,val-a-s1-ups1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+------------------
-key-a|val-a-s1-ups1-ups2
+key |data |check_old_and_new
+-----+------------------+-----------------
+key-a|val-a-s1-ups1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -1347,15 +1347,15 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -1417,7 +1417,7 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-a = text key-a: t
@@ -1425,9 +1425,9 @@ s2: NOTICE: upk: text val-a-s1-ups1 <>
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_d; when: AFTER; lev: ROWs; op: DELETE; old: (key-a,val-a-s1-ups1) new: <NULL>
step s2_del_a: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups1
+key |data |check_old
+-----+-------------+---------
+key-a|val-a-s1-ups1|t
(1 row)
step s2_c: COMMIT;
@@ -1488,15 +1488,15 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_d; when: AFTER; lev: ROWs; op: DELETE; old: (key-a,val-a-s1) new: <NULL>
step s2_del_a: <... completed>
-key |data
------+--------
-key-a|val-a-s1
+key |data |check_old
+-----+--------+---------
+key-a|val-a-s1|t
(1 row)
step s2_c: COMMIT;
@@ -1557,13 +1557,13 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -1624,15 +1624,15 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -1693,13 +1693,13 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
step s2_del_a: <... completed>
-key|data
----+----
+key|data|check_old
+---+----+---------
(0 rows)
step s2_c: COMMIT;
@@ -1759,15 +1759,15 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_d; when: AFTER; lev: ROWs; op: DELETE; old: (key-a,val-a-s1) new: <NULL>
step s2_del_a: <... completed>
-key |data
------+--------
-key-a|val-a-s1
+key |data |check_old
+-----+--------+---------
+key-a|val-a-s1|t
(1 row)
step s2_c: COMMIT;
@@ -1829,14 +1829,14 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: upd: text key-c = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -1899,16 +1899,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-c = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -2038,7 +2038,7 @@ step s2_upd_all_data:
WHERE
noisy_oper('upd', key, '<>', 'mismatch') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-b <> text mismatch: t
@@ -2050,10 +2050,10 @@ s2: NOTICE: trigger: name rep_b_u; when
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-b,val-a-s1-tobs1) new: (key-b,val-a-s1-tobs1-ups2)
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-c,val-c-s1) new: (key-c,val-c-s1-ups2)
step s2_upd_all_data: <... completed>
-key |data
------+-------------------
-key-b|val-a-s1-tobs1-ups2
-key-c|val-c-s1-ups2
+key |data |check_old_and_new
+-----+-------------------+-----------------
+key-b|val-a-s1-tobs1-ups2|t
+key-c|val-c-s1-ups2 |t
(2 rows)
step s2_c: COMMIT;
@@ -2118,13 +2118,13 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-c = text key-a: f
step s2_upd_a_data: <... completed>
-key|data
----+----
+key|data|check_old_and_new
+---+----+-----------------
(0 rows)
step s2_c: COMMIT;
@@ -2188,16 +2188,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-c = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -2260,13 +2260,13 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
<waiting ...>
step s1_c: COMMIT;
s2: NOTICE: upd: text key-c = text key-a: f
step s2_del_a: <... completed>
-key|data
----+----
+key|data|check_old
+---+----+---------
(0 rows)
step s2_c: COMMIT;
@@ -2328,16 +2328,16 @@ step s2_del_a:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_d; when: BEFORE; lev: ROWs; op: DELETE; old: (key-a,val-a-s1) new: <NULL>
s2: NOTICE: upd: text key-c = text key-a: f
s2: NOTICE: trigger: name rep_a_d; when: AFTER; lev: ROWs; op: DELETE; old: (key-a,val-a-s1) new: <NULL>
step s2_del_a: <... completed>
-key |data
------+--------
-key-a|val-a-s1
+key |data |check_old
+-----+--------+---------
+key-a|val-a-s1|t
(1 row)
step s2_c: COMMIT;
@@ -2507,7 +2507,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
step s2_upd_a_data: <... completed>
@@ -2572,16 +2572,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: COMMIT;
@@ -2646,7 +2646,7 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_c: COMMIT;
step s2_upd_a_data: <... completed>
@@ -2712,16 +2712,16 @@ step s2_upd_a_data:
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
<waiting ...>
step s1_r: ROLLBACK;
s2: NOTICE: trigger: name rep_b_u; when: BEFORE; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
s2: NOTICE: upd: text key-b = text key-a: f
s2: NOTICE: trigger: name rep_a_u; when: AFTER; lev: ROWs; op: UPDATE; old: (key-a,val-a-s1) new: (key-a,val-a-s1-ups2)
step s2_upd_a_data: <... completed>
-key |data
------+-------------
-key-a|val-a-s1-ups2
+key |data |check_old_and_new
+-----+-------------+-----------------
+key-a|val-a-s1-ups2|t
(1 row)
step s2_c: 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 4250b85..10ef2ad
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -197,15 +197,23 @@ step merge_bal:
MERGE INTO target t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+----------------------------------
+ 1| 50| 100|s1 |setup updated by update_bal1 when1
+(1 row)
+
step select1: SELECT * FROM target;
key|balance|status|val
---+-------+------+----------------------------------
@@ -220,15 +228,23 @@ step merge_bal_pa:
MERGE INTO target_pa t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal_pa: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------
+ 1| 50| 100|s1 |setup updated by update_bal1_pa when1
+(1 row)
+
step select1_pa: SELECT * FROM target_pa;
key|balance|status|val
---+-------+------+-------------------------------------
@@ -245,22 +261,24 @@ step merge_bal_tg:
MERGE INTO target_tg t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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'
- RETURNING t.*
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val
)
SELECT * FROM t;
<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>
-key|balance|status|val
----+-------+------+-------------------------------------
- 1| 100|s1 |setup updated by update_bal1_tg when1
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------
+ 1| 50| 100|s1 |setup updated by update_bal1_tg when1
(1 row)
step select1_tg: SELECT * FROM target_tg;
@@ -278,15 +296,23 @@ step merge_bal:
MERGE INTO target t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------------
+ 1| 70| 140|s1 |setup updated by update1 updated by update6 when1
+(1 row)
+
step select1: SELECT * FROM target;
key|balance|status|val
---+-------+------+-------------------------------------------------
@@ -302,15 +328,23 @@ step merge_bal_pa:
MERGE INTO target_pa t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal_pa: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------------------
+ 1| 70| 140|s1 |setup updated by update1_pa updated by update6_pa when1
+(1 row)
+
step select1_pa: SELECT * FROM target_pa;
key|balance|status|val
---+-------+------+-------------------------------------------------------
@@ -329,22 +363,24 @@ step merge_bal_tg:
MERGE INTO target_tg t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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'
- RETURNING t.*
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val
)
SELECT * FROM t;
<waiting ...>
step c2: COMMIT;
s1: NOTICE: Update: (1,70,s1,"setup updated by update1_tg updated by update6_tg") -> (1,140,s1,"setup updated by update1_tg updated by update6_tg when1")
step merge_bal_tg: <... completed>
-key|balance|status|val
----+-------+------+-------------------------------------------------------
- 1| 140|s1 |setup updated by update1_tg updated by update6_tg when1
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------------------
+ 1| 70| 140|s1 |setup updated by update1_tg updated by update6_tg when1
(1 row)
step select1_tg: SELECT * FROM target_tg;
@@ -355,6 +391,105 @@ key|balance|status|val
step c1: COMMIT;
+starting permutation: update6 update6 merge_bal c2 select1 c1
+step update6: UPDATE target t SET balance = balance - 100, val = t.val || ' updated by update6' WHERE t.key = 1;
+step update6: UPDATE target t SET balance = balance - 100, val = t.val || ' updated by update6' 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 < 0 THEN
+ DELETE
+ 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'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------
+ 1| -40| |s1 |setup updated by update6 updated by update6
+(1 row)
+
+step select1: SELECT * FROM target;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update6_pa update6_pa merge_bal_pa c2 select1_pa c1
+step update6_pa: UPDATE target_pa t SET balance = balance - 100, val = t.val || ' updated by update6_pa' WHERE t.key = 1;
+step update6_pa: UPDATE target_pa t SET balance = balance - 100, val = t.val || ' updated by update6_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 < 0 THEN
+ DELETE
+ 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'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal_pa: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------------
+ 1| -40| |s1 |setup updated by update6_pa updated by update6_pa
+(1 row)
+
+step select1_pa: SELECT * FROM target_pa;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
+starting permutation: update6_tg update6_tg merge_bal_tg c2 select1_tg c1
+s2: NOTICE: Update: (1,160,s1,setup) -> (1,60,s1,"setup updated by update6_tg")
+step update6_tg: UPDATE target_tg t SET balance = balance - 100, val = t.val || ' updated by update6_tg' WHERE t.key = 1;
+s2: NOTICE: Update: (1,60,s1,"setup updated by update6_tg") -> (1,-40,s1,"setup updated by update6_tg updated by update6_tg")
+step update6_tg: UPDATE target_tg t SET balance = balance - 100, val = t.val || ' updated by update6_tg' WHERE t.key = 1;
+step merge_bal_tg:
+ WITH t AS (
+ MERGE INTO target_tg t
+ USING (SELECT 1 as key) s
+ ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
+ 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'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val
+ )
+ SELECT * FROM t;
+ <waiting ...>
+step c2: COMMIT;
+s1: NOTICE: Delete: (1,-40,s1,"setup updated by update6_tg updated by update6_tg")
+step merge_bal_tg: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------------
+ 1| -40| |s1 |setup updated by update6_tg updated by update6_tg
+(1 row)
+
+step select1_tg: SELECT * FROM target_tg;
+key|balance|status|val
+---+-------+------+---
+(0 rows)
+
+step c1: COMMIT;
+
starting permutation: update7 update6 merge_bal c2 select1 c1
step update7: UPDATE target t SET balance = 350, val = t.val || ' updated by update7' WHERE t.key = 1;
step update6: UPDATE target t SET balance = balance - 100, val = t.val || ' updated by update6' WHERE t.key = 1;
@@ -362,15 +497,23 @@ step merge_bal:
MERGE INTO target t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal: <... completed>
+key|old_balance|new_balance|status|val
+---+-----------+-----------+------+-------------------------------------------------
+ 1| 250| 2000|s1 |setup updated by update7 updated by update6 when3
+(1 row)
+
step select1: SELECT * FROM target;
key|balance|status|val
---+-------+------+-------------------------------------------------
@@ -385,12 +528,15 @@ step merge_bal_pa:
MERGE INTO target_pa t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal_pa: <... completed>
@@ -404,12 +550,15 @@ step merge_bal_pa:
MERGE INTO target_pa t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
<waiting ...>
step c2: COMMIT;
step merge_bal_pa: <... completed>
diff --git a/src/test/isolation/specs/eval-plan-qual-trigger.spec b/src/test/isolation/specs/eval-plan-qual-trigger.spec
new file mode 100644
index 232b3e2..575fbe0
--- a/src/test/isolation/specs/eval-plan-qual-trigger.spec
+++ b/src/test/isolation/specs/eval-plan-qual-trigger.spec
@@ -120,14 +120,14 @@ step s2_del_a {
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *
+ RETURNING *, data = old.data AS check_old;
}
step s2_upd_a_data {
UPDATE trigtest SET data = data || '-ups2'
WHERE
noisy_oper('upd', key, '=', 'key-a') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
}
step s2_upd_b_data {
UPDATE trigtest SET data = data || '-ups2'
@@ -141,7 +141,7 @@ step s2_upd_all_data {
WHERE
noisy_oper('upd', key, '<>', 'mismatch') AND
noisy_oper('upk', data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-ups2' AS check_old_and_new;
}
step s2_upsert_a_data {
INSERT INTO trigtest VALUES ('key-a', 'val-a-upss2')
@@ -150,7 +150,7 @@ step s2_upsert_a_data {
WHERE
noisy_oper('upd', trigtest.key, '=', 'key-a') AND
noisy_oper('upk', trigtest.data, '<>', 'mismatch')
- RETURNING *;
+ RETURNING *, new.data = old.data || '-upserts2' AS check_old_and_new;
}
session s3
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 6e7a776..6054fca
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -10,7 +10,7 @@ setup
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_pa1 PARTITION OF target_pa FOR VALUES FROM (-100) 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');
@@ -78,24 +78,30 @@ step "merge_bal"
MERGE INTO target t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
}
step "merge_bal_pa"
{
MERGE INTO target_pa t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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';
+ UPDATE SET balance = balance * 8, val = t.val || ' when3'
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val;
}
step "merge_bal_tg"
{
@@ -103,13 +109,15 @@ step "merge_bal_tg"
MERGE INTO target_tg t
USING (SELECT 1 as key) s
ON s.key = t.key
+ WHEN MATCHED AND balance < 0 THEN
+ DELETE
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'
- RETURNING t.*
+ RETURNING t.key, old.balance AS old_balance, new.balance AS new_balance, t.status, t.val
)
SELECT * FROM t;
}
@@ -190,6 +198,11 @@ permutation "update1" "update6" "merge_b
permutation "update1_pa" "update6_pa" "merge_bal_pa" "c2" "select1_pa" "c1"
permutation "update1_tg" "update6_tg" "merge_bal_tg" "c2" "select1_tg" "c1"
+# merge_bal sees row concurrently updated twice and rechecks WHEN conditions, different check passes, and row is deleted
+permutation "update6" "update6" "merge_bal" "c2" "select1" "c1"
+permutation "update6_pa" "update6_pa" "merge_bal_pa" "c2" "select1_pa" "c1"
+permutation "update6_tg" "update6_tg" "merge_bal_tg" "c2" "select1_tg" "c1"
+
# merge_bal sees row concurrently updated twice, first update would cause all checks to fail, second update causes different check to pass, so final balance = 2000
permutation "update7" "update6" "merge_bal" "c2" "select1" "c1"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
2026-06-28 09:59 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
2026-06-28 23:34 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
2026-06-30 08:35 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
@ 2026-07-07 07:38 ` Bharath Rupireddy <[email protected]>
2026-07-08 20:29 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Bharath Rupireddy @ 2026-07-07 07:38 UTC (permalink / raw)
To: Dean Rasheed <[email protected]>; +Cc: [email protected]; [email protected]
Hi,
On Tue, Jun 30, 2026 at 1:35 AM Dean Rasheed <[email protected]> wrote:
>
> I did think of that, but I was uneasy about copying the trigger's old
> tuple after trigger invocation, in case the trigger scribbled on it
> somehow.
Fair argument. I'm convinced.
> Actually, I think a neater long-term solution would be to move all the
> EPQ rechecking and re-computing of the new tuple out of trigger.c, and
> let the caller deal with it, as we do for the MERGE code
Agreed.
> Yes, that's right. The caller does not initialise context->tmfd at
> all, and ExecUpdatePrologue() doesn't touch it if there are no
> triggers.
That works for me.
> OK, I've added a bunch of tests for those cases. As expected, they
> pass even without the code changes, but it's good to confirm that.
Nice, thank you for adding those.
> > Could we add some context to the error message, e.g., mentioning that
> > this happens after processing the BEFORE UPDATE row triggers? That
> > would make it easier to distinguish from the other similar ones.
>
> I'm pretty sure this is a can't-happen error condition, but OK.
Thanks.
Sorry for the late response. I took a look at the v2 patch, tested it
locally, and it looks good to me. I think we should backpatch this fix
down to PG18 (commit 80feb727c86).
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
2026-06-28 09:59 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
2026-06-28 23:34 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
2026-06-30 08:35 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Dean Rasheed <[email protected]>
2026-07-07 07:38 ` Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger Bharath Rupireddy <[email protected]>
@ 2026-07-08 20:29 ` Dean Rasheed <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Dean Rasheed @ 2026-07-08 20:29 UTC (permalink / raw)
To: Bharath Rupireddy <[email protected]>; +Cc: [email protected]; [email protected]
On Tue, 7 Jul 2026 at 08:38, Bharath Rupireddy
<[email protected]> wrote:
>
> Sorry for the late response. I took a look at the v2 patch, tested it
> locally, and it looks good to me. I think we should backpatch this fix
> down to PG18 (commit 80feb727c86).
>
Thanks for reviewing.
Pushed and back-patched to v18.
Regards,
Dean
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-07-08 20:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-24 15:22 BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger PG Bug reporting form <[email protected]>
2026-06-27 19:22 ` Bharath Rupireddy <[email protected]>
2026-06-28 09:59 ` Dean Rasheed <[email protected]>
2026-06-28 23:34 ` Bharath Rupireddy <[email protected]>
2026-06-30 08:35 ` Dean Rasheed <[email protected]>
2026-07-07 07:38 ` Bharath Rupireddy <[email protected]>
2026-07-08 20:29 ` Dean Rasheed <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox