public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dean Rasheed <[email protected]>
To: Bharath Rupireddy <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
Date: Sun, 28 Jun 2026 10:59:09 +0100
Message-ID: <CAEZATCW8q47GDba1o+Ji9JUhCHFFbB35D9qMSbBhboCx0GXkQQ@mail.gmail.com> (raw)
In-Reply-To: <CALj2ACVThKXik0+X12yEz=78uzUzLZABLj0WKOgH+q-kyvdDcg@mail.gmail.com>
References: <[email protected]>
<CALj2ACVThKXik0+X12yEz=78uzUzLZABLj0WKOgH+q-kyvdDcg@mail.gmail.com>
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
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19536: UPDATE RETURNING OLD value is stale after concurrent update when table has a BEFORE UPDATE trigger
In-Reply-To: <CAEZATCW8q47GDba1o+Ji9JUhCHFFbB35D9qMSbBhboCx0GXkQQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox