aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/isolation/expected/eval-plan-qual.out252
-rw-r--r--src/test/isolation/expected/partition-key-update-1.out10
-rw-r--r--src/test/isolation/specs/eval-plan-qual.spec35
-rw-r--r--src/test/isolation/specs/partition-key-update-1.spec1
4 files changed, 288 insertions, 10 deletions
diff --git a/src/test/isolation/expected/eval-plan-qual.out b/src/test/isolation/expected/eval-plan-qual.out
index bbbb62ef4b1..c09e97faf82 100644
--- a/src/test/isolation/expected/eval-plan-qual.out
+++ b/src/test/isolation/expected/eval-plan-qual.out
@@ -1,10 +1,16 @@
Parsed test spec with 3 sessions
starting permutation: wx1 wx2 c1 c2 read
-step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking';
-step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking'; <waiting ...>
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+400
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
step c1: COMMIT;
step wx2: <... completed>
+balance
+
+850
step c2: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
@@ -13,10 +19,15 @@ checking 850
savings 600
starting permutation: wy1 wy2 c1 c2 read
-step wy1: UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking';
-step wy2: UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000; <waiting ...>
+step wy1: UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1100
+step wy2: UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000 RETURNING balance; <waiting ...>
step c1: COMMIT;
step wy2: <... completed>
+balance
+
step c2: COMMIT;
step read: SELECT * FROM accounts ORDER BY accountid;
accountid balance
@@ -24,6 +35,229 @@ accountid balance
checking 1100
savings 600
+starting permutation: wx1 wx2 r1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+400
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wx2: <... completed>
+balance
+
+1050
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+checking 1050
+savings 600
+
+starting permutation: wy1 wy2 r1 c2 read
+step wy1: UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1100
+step wy2: UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000 RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wy2: <... completed>
+balance
+
+1600
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+checking 1600
+savings 600
+
+starting permutation: wx1 d1 wx2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+400
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+
+400
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step c1: COMMIT;
+step wx2: <... completed>
+balance
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: wx2 d1 c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step c2: COMMIT;
+step d1: <... completed>
+balance
+
+1050
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: wx2 wx2 d1 c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1500
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step c2: COMMIT;
+step d1: <... completed>
+balance
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+checking 1500
+savings 600
+
+starting permutation: wx2 d2 d1 c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
+step d2: DELETE FROM accounts WHERE accountid = 'checking';
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step c2: COMMIT;
+step d1: <... completed>
+balance
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: wx1 d1 wx2 r1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+400
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+
+400
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wx2: <... completed>
+balance
+
+1050
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+checking 1050
+savings 600
+
+starting permutation: wx2 d1 r2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step r2: ROLLBACK;
+step d1: <... completed>
+balance
+
+600
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: wx2 wx2 d1 r2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1500
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step r2: ROLLBACK;
+step d1: <... completed>
+balance
+
+600
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: wx2 d2 d1 r2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
+step d2: DELETE FROM accounts WHERE accountid = 'checking';
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step r2: ROLLBACK;
+step d1: <... completed>
+balance
+
+600
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: d1 wx2 c1 c2 read
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+
+600
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step c1: COMMIT;
+step wx2: <... completed>
+balance
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+savings 600
+
+starting permutation: d1 wx2 r1 c2 read
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+
+600
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wx2: <... completed>
+balance
+
+1050
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid balance
+
+checking 1050
+savings 600
+
starting permutation: upsert1 upsert2 c1 c2 read
step upsert1:
WITH upsert AS
@@ -106,7 +340,10 @@ a b c
step c2: COMMIT;
starting permutation: wx2 partiallock c2 c1 read
-step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking';
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
step partiallock:
SELECT * FROM accounts a1, accounts a2
WHERE a1.accountid = a2.accountid
@@ -126,7 +363,10 @@ checking 1050
savings 600
starting permutation: wx2 lockwithvalues c2 c1 read
-step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking';
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+
+1050
step lockwithvalues:
SELECT * FROM accounts a1, (values('checking'),('savings')) v(id)
WHERE a1.accountid = v.id
diff --git a/src/test/isolation/expected/partition-key-update-1.out b/src/test/isolation/expected/partition-key-update-1.out
index 37fe6a7b277..282073dbae4 100644
--- a/src/test/isolation/expected/partition-key-update-1.out
+++ b/src/test/isolation/expected/partition-key-update-1.out
@@ -18,6 +18,16 @@ step s2d: <... completed>
error in steps s1c s2d: ERROR: tuple to be deleted was already moved to another partition due to concurrent update
step s2c: COMMIT;
+starting permutation: s1b s2b s1u s2u s1c s2c
+step s1b: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s2b: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s1u: UPDATE foo SET a=2 WHERE a=1;
+step s2u: UPDATE foo SET b='EFG' WHERE a=1; <waiting ...>
+step s1c: COMMIT;
+step s2u: <... completed>
+error in steps s1c s2u: ERROR: tuple to be updated was already moved to another partition due to concurrent update
+step s2c: COMMIT;
+
starting permutation: s1b s2b s2d s1u s2c s1c
step s1b: BEGIN ISOLATION LEVEL READ COMMITTED;
step s2b: BEGIN ISOLATION LEVEL READ COMMITTED;
diff --git a/src/test/isolation/specs/eval-plan-qual.spec b/src/test/isolation/specs/eval-plan-qual.spec
index 2e1b5095e8e..41c8d564409 100644
--- a/src/test/isolation/specs/eval-plan-qual.spec
+++ b/src/test/isolation/specs/eval-plan-qual.spec
@@ -42,9 +42,16 @@ teardown
session "s1"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
# wx1 then wx2 checks the basic case of re-fetching up-to-date values
-step "wx1" { UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking'; }
+step "wx1" { UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance; }
# wy1 then wy2 checks the case where quals pass then fail
-step "wy1" { UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking'; }
+step "wy1" { UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance; }
+
+# d1 then wx1 checks that update can deal with the updated row vanishing
+# wx2 then d1 checks that the delete affects the updated row
+# wx2, wx2 then d1 checks that the delete checks the quals correctly (balance too high)
+# wx2, d2, then d1 checks that delete handles a vanishing row correctly
+step "d1" { DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; }
+
# upsert tests are to check writable-CTE cases
step "upsert1" {
WITH upsert AS
@@ -64,6 +71,7 @@ step "readp1" { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND
step "writep1" { UPDATE p SET b = -1 WHERE a = 1 AND b = 1 AND c = 0; }
step "writep2" { UPDATE p SET b = -b WHERE a = 1 AND c = 0; }
step "c1" { COMMIT; }
+step "r1" { ROLLBACK; }
# these tests are meant to exercise EvalPlanQualFetchRowMarks,
# ie, handling non-locked tables in an EvalPlanQual recheck
@@ -128,8 +136,10 @@ step "selectresultforupdate" {
session "s2"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
-step "wx2" { UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking'; }
-step "wy2" { UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000; }
+step "wx2" { UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; }
+step "wy2" { UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000 RETURNING balance; }
+step "d2" { DELETE FROM accounts WHERE accountid = 'checking'; }
+
step "upsert2" {
WITH upsert AS
(UPDATE accounts SET balance = balance + 1234
@@ -161,6 +171,7 @@ step "updateforcip3" {
step "wrtwcte" { UPDATE table_a SET value = 'tableAValue2' WHERE id = 1; }
step "wrjt" { UPDATE jointest SET data = 42 WHERE id = 7; }
step "c2" { COMMIT; }
+step "r2" { ROLLBACK; }
session "s3"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
@@ -192,8 +203,24 @@ step "multireadwcte" {
teardown { COMMIT; }
+# test that normal update follows update chains, and reverifies quals
permutation "wx1" "wx2" "c1" "c2" "read"
permutation "wy1" "wy2" "c1" "c2" "read"
+permutation "wx1" "wx2" "r1" "c2" "read"
+permutation "wy1" "wy2" "r1" "c2" "read"
+
+# test that deletes follow chains, and if necessary reverifies quals
+permutation "wx1" "d1" "wx2" "c1" "c2" "read"
+permutation "wx2" "d1" "c2" "c1" "read"
+permutation "wx2" "wx2" "d1" "c2" "c1" "read"
+permutation "wx2" "d2" "d1" "c2" "c1" "read"
+permutation "wx1" "d1" "wx2" "r1" "c2" "read"
+permutation "wx2" "d1" "r2" "c1" "read"
+permutation "wx2" "wx2" "d1" "r2" "c1" "read"
+permutation "wx2" "d2" "d1" "r2" "c1" "read"
+permutation "d1" "wx2" "c1" "c2" "read"
+permutation "d1" "wx2" "r1" "c2" "read"
+
permutation "upsert1" "upsert2" "c1" "c2" "read"
permutation "readp1" "writep1" "readp2" "c1" "c2"
permutation "writep2" "returningp1" "c1" "c2"
diff --git a/src/test/isolation/specs/partition-key-update-1.spec b/src/test/isolation/specs/partition-key-update-1.spec
index 8393f47c596..79db1d0f307 100644
--- a/src/test/isolation/specs/partition-key-update-1.spec
+++ b/src/test/isolation/specs/partition-key-update-1.spec
@@ -66,6 +66,7 @@ step "s2c" { COMMIT; }
# Concurrency error from ExecUpdate and ExecDelete.
permutation "s1b" "s2b" "s1u" "s1c" "s2d" "s2c"
permutation "s1b" "s2b" "s1u" "s2d" "s1c" "s2c"
+permutation "s1b" "s2b" "s1u" "s2u" "s1c" "s2c"
permutation "s1b" "s2b" "s2d" "s1u" "s2c" "s1c"
# Concurrency error from GetTupleForTrigger
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-28 19:58:07 +0000