summaryrefslogtreecommitdiffstats
path: root/src/test/isolation/expected/eval-plan-qual.out
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--src/test/isolation/expected/eval-plan-qual.out1179
1 files changed, 1179 insertions, 0 deletions
diff --git a/src/test/isolation/expected/eval-plan-qual.out b/src/test/isolation/expected/eval-plan-qual.out
new file mode 100644
index 0000000..d906350
--- /dev/null
+++ b/src/test/isolation/expected/eval-plan-qual.out
@@ -0,0 +1,1179 @@
+Parsed test spec with 3 sessions
+
+starting permutation: wx1 wx2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step c1: COMMIT;
+step wx2: <... completed>
+balance
+-------
+ 850
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 850
+savings | 600
+(2 rows)
+
+
+starting permutation: wy1 wy2 c1 c2 read
+step wy1: UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1100
+(1 row)
+
+step wy2: UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000 RETURNING balance; <waiting ...>
+step c1: COMMIT;
+step wy2: <... completed>
+balance
+-------
+(0 rows)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1100
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 wx2 r1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wx2: <... completed>
+balance
+-------
+ 1050
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1050
+savings | 600
+(2 rows)
+
+
+starting permutation: wy1 wy2 r1 c2 read
+step wy1: UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1100
+(1 row)
+
+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
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1600
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 d1 wx2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step c1: COMMIT;
+step wx2: <... completed>
+balance
+-------
+(0 rows)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: wx2 d1 c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step c2: COMMIT;
+step d1: <... completed>
+balance
+-------
+ 1050
+(1 row)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: wx2 wx2 d1 c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1500
+(1 row)
+
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step c2: COMMIT;
+step d1: <... completed>
+balance
+-------
+(0 rows)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1500
+savings | 600
+(2 rows)
+
+
+starting permutation: wx2 d2 d1 c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+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
+-------
+(0 rows)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: wx1 d1 wx2 r1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wx2: <... completed>
+balance
+-------
+ 1050
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1050
+savings | 600
+(2 rows)
+
+
+starting permutation: wx2 d1 r2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step r2: ROLLBACK;
+step d1: <... completed>
+balance
+-------
+ 600
+(1 row)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: wx2 wx2 d1 r2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1500
+(1 row)
+
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; <waiting ...>
+step r2: ROLLBACK;
+step d1: <... completed>
+balance
+-------
+ 600
+(1 row)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: wx2 d2 d1 r2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+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
+(1 row)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: d1 wx2 c1 c2 read
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+-------
+ 600
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step c1: COMMIT;
+step wx2: <... completed>
+balance
+-------
+(0 rows)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+savings | 600
+(1 row)
+
+
+starting permutation: d1 wx2 r1 c2 read
+step d1: DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance;
+balance
+-------
+ 600
+(1 row)
+
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; <waiting ...>
+step r1: ROLLBACK;
+step wx2: <... completed>
+balance
+-------
+ 1050
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1050
+savings | 600
+(2 rows)
+
+
+starting permutation: wnested2 c1 c2 read
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+
+step c1: COMMIT;
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | -600
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 wxext1 wnested2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wxext1: UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 400 > numeric 200.0: t
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 400 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 400 > numeric 200.0: t
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | -800
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 wx1 wxext1 wnested2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 200
+(1 row)
+
+step wxext1: UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 400 > numeric 200.0: t
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 200 > numeric 200.0: f
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 200
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 wx1 wxext1 wxext1 wnested2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 200
+(1 row)
+
+step wxext1: UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wxext1: UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 200
+(1 row)
+
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 200 > numeric 200.0: f
+s2: NOTICE: lock_id: text savings = text checking: f
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 200
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 wxext1 wxext1 wnested2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wxext1: UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step wxext1: UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 200
+(1 row)
+
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 200 > numeric 200.0: f
+s2: NOTICE: lock_id: text savings = text checking: f
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 400
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 tocds1 wnested2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step tocds1: UPDATE accounts SET accountid = 'cds' WHERE accountid = 'checking';
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: upid: text cds = text checking: f
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+cds | 400
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 tocdsext1 wnested2 c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step tocdsext1: UPDATE accounts_ext SET accountid = 'cds' WHERE accountid = 'checking';
+s2: NOTICE: upid: text checking = text checking: t
+s2: NOTICE: up: numeric 600 > numeric 200.0: t
+s2: NOTICE: lock_id: text checking = text checking: t
+s2: NOTICE: lock_bal: numeric 600 > numeric 200.0: t
+step wnested2:
+ UPDATE accounts SET balance = balance - 1200
+ WHERE noisy_oper('upid', accountid, '=', 'checking')
+ AND noisy_oper('up', balance, '>', 200.0)
+ AND EXISTS (
+ SELECT accountid
+ FROM accounts_ext ae
+ WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid)
+ AND noisy_oper('lock_bal', ae.balance, '>', 200.0)
+ FOR UPDATE
+ );
+ <waiting ...>
+step c1: COMMIT;
+s2: NOTICE: lock_id: text cds = text checking: f
+s2: NOTICE: lock_id: text savings = text checking: f
+s2: NOTICE: upid: text savings = text checking: f
+step wnested2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 400
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 updwcte c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step updwcte: WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *) UPDATE accounts a SET balance = doup.balance + 100 FROM doup RETURNING *; <waiting ...>
+step c1: COMMIT;
+step updwcte: <... completed>
+accountid|balance|accountid|balance
+---------+-------+---------+-------
+savings | 1600|checking | 1500
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1500
+savings | 1600
+(2 rows)
+
+
+starting permutation: wx1 updwctefail c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step updwctefail: WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *, update_checking(999)) UPDATE accounts a SET balance = doup.balance + 100 FROM doup RETURNING *; <waiting ...>
+step c1: COMMIT;
+step updwctefail: <... completed>
+ERROR: tuple to be updated was already modified by an operation triggered by the current command
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 400
+savings | 600
+(2 rows)
+
+
+starting permutation: wx1 delwcte c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step delwcte: WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *) DELETE FROM accounts a USING doup RETURNING *; <waiting ...>
+step c1: COMMIT;
+step delwcte: <... completed>
+accountid|balance|accountid|balance
+---------+-------+---------+-------
+savings | 600|checking | 1500
+(1 row)
+
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1500
+(1 row)
+
+
+starting permutation: wx1 delwctefail c1 c2 read
+step wx1: UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 400
+(1 row)
+
+step delwctefail: WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *, update_checking(999)) DELETE FROM accounts a USING doup RETURNING *; <waiting ...>
+step c1: COMMIT;
+step delwctefail: <... completed>
+ERROR: tuple to be deleted was already modified by an operation triggered by the current command
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 400
+savings | 600
+(2 rows)
+
+
+starting permutation: upsert1 upsert2 c1 c2 read
+step upsert1:
+ WITH upsert AS
+ (UPDATE accounts SET balance = balance + 500
+ WHERE accountid = 'savings'
+ RETURNING accountid)
+ INSERT INTO accounts SELECT 'savings', 500
+ WHERE NOT EXISTS (SELECT 1 FROM upsert);
+
+step upsert2:
+ WITH upsert AS
+ (UPDATE accounts SET balance = balance + 1234
+ WHERE accountid = 'savings'
+ RETURNING accountid)
+ INSERT INTO accounts SELECT 'savings', 1234
+ WHERE NOT EXISTS (SELECT 1 FROM upsert);
+ <waiting ...>
+step c1: COMMIT;
+step upsert2: <... completed>
+step c2: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 600
+savings | 2334
+(2 rows)
+
+
+starting permutation: readp1 writep1 readp2 c1 c2
+step readp1: SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE;
+tableoid|ctid |a|b|c
+--------+-----+-+-+-
+c1 |(0,1)|0|0|0
+c1 |(0,4)|0|1|0
+c2 |(0,1)|1|0|0
+c2 |(0,4)|1|1|0
+c3 |(0,1)|2|0|0
+c3 |(0,4)|2|1|0
+(6 rows)
+
+step writep1: UPDATE p SET b = -1 WHERE a = 1 AND b = 1 AND c = 0;
+step readp2: SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; <waiting ...>
+step c1: COMMIT;
+step readp2: <... completed>
+tableoid|ctid |a|b|c
+--------+-----+-+-+-
+c1 |(0,1)|0|0|0
+c1 |(0,4)|0|1|0
+c2 |(0,1)|1|0|0
+c3 |(0,1)|2|0|0
+c3 |(0,4)|2|1|0
+(5 rows)
+
+step c2: COMMIT;
+
+starting permutation: writep2 returningp1 c1 c2
+step writep2: UPDATE p SET b = -b WHERE a = 1 AND c = 0;
+step returningp1:
+ WITH u AS ( UPDATE p SET b = b WHERE a > 0 RETURNING * )
+ SELECT * FROM u;
+ <waiting ...>
+step c1: COMMIT;
+step returningp1: <... completed>
+a| b|c
+-+--+-
+1| 0|0
+1| 0|1
+1| 0|2
+1|-1|0
+1| 1|1
+1| 1|2
+1|-2|0
+1| 2|1
+1| 2|2
+1|-3|0
+2| 0|0
+2| 0|1
+2| 0|2
+2| 1|0
+2| 1|1
+2| 1|2
+2| 2|0
+2| 2|1
+2| 2|2
+2| 3|0
+(20 rows)
+
+step c2: COMMIT;
+
+starting permutation: writep3a writep3b c1 c2
+step writep3a: UPDATE p SET b = -b WHERE c = 0;
+step writep3b: UPDATE p SET b = -b WHERE c = 0; <waiting ...>
+step c1: COMMIT;
+step writep3b: <... completed>
+step c2: COMMIT;
+
+starting permutation: wx2 partiallock c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+step partiallock:
+ SELECT * FROM accounts a1, accounts a2
+ WHERE a1.accountid = a2.accountid
+ FOR UPDATE OF a1;
+ <waiting ...>
+step c2: COMMIT;
+step partiallock: <... completed>
+accountid|balance|accountid|balance
+---------+-------+---------+-------
+checking | 1050|checking | 600
+savings | 600|savings | 600
+(2 rows)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1050
+savings | 600
+(2 rows)
+
+
+starting permutation: wx2 lockwithvalues c2 c1 read
+step wx2: UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance;
+balance
+-------
+ 1050
+(1 row)
+
+step lockwithvalues:
+ -- Reference rowmark column that differs in type from targetlist at some attno.
+ -- See CAHU7rYZo_C4ULsAx_LAj8az9zqgrD8WDd4hTegDTMM1LMqrBsg@mail.gmail.com
+ SELECT a1.*, v.id FROM accounts a1, (values('checking'::text, 'nan'::text),('savings', 'nan')) v(id, notnumeric)
+ WHERE a1.accountid = v.id AND v.notnumeric != 'einszwei'
+ FOR UPDATE OF a1;
+ <waiting ...>
+step c2: COMMIT;
+step lockwithvalues: <... completed>
+accountid|balance|id
+---------+-------+--------
+checking | 1050|checking
+savings | 600|savings
+(2 rows)
+
+step c1: COMMIT;
+step read: SELECT * FROM accounts ORDER BY accountid;
+accountid|balance
+---------+-------
+checking | 1050
+savings | 600
+(2 rows)
+
+
+starting permutation: wx2_ext partiallock_ext c2 c1 read_ext
+step wx2_ext: UPDATE accounts_ext SET balance = balance + 450;
+step partiallock_ext:
+ SELECT * FROM accounts_ext a1, accounts_ext a2
+ WHERE a1.accountid = a2.accountid
+ FOR UPDATE OF a1;
+ <waiting ...>
+step c2: COMMIT;
+step partiallock_ext: <... completed>
+accountid|balance|other|newcol|newcol2|accountid|balance|other|newcol|newcol2
+---------+-------+-----+------+-------+---------+-------+-----+------+-------
+checking | 1050|other| 42| |checking | 600|other| 42|
+savings | 1150| | 42| |savings | 700| | 42|
+(2 rows)
+
+step c1: COMMIT;
+step read_ext: SELECT * FROM accounts_ext ORDER BY accountid;
+accountid|balance|other|newcol|newcol2
+---------+-------+-----+------+-------
+checking | 1050|other| 42|
+savings | 1150| | 42|
+(2 rows)
+
+
+starting permutation: updateforss readforss c1 c2
+step updateforss:
+ UPDATE table_a SET value = 'newTableAValue' WHERE id = 1;
+ UPDATE table_b SET value = 'newTableBValue' WHERE id = 1;
+
+step readforss:
+ SELECT ta.id AS ta_id, ta.value AS ta_value,
+ (SELECT ROW(tb.id, tb.value)
+ FROM table_b tb WHERE ta.id = tb.id) AS tb_row
+ FROM table_a ta
+ WHERE ta.id = 1 FOR UPDATE OF ta;
+ <waiting ...>
+step c1: COMMIT;
+step readforss: <... completed>
+ta_id|ta_value |tb_row
+-----+--------------+---------------
+ 1|newTableAValue|(1,tableBValue)
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: updateforcip updateforcip2 c1 c2 read_a
+step updateforcip:
+ UPDATE table_a SET value = NULL WHERE id = 1;
+
+step updateforcip2:
+ UPDATE table_a SET value = COALESCE(value, (SELECT text 'newValue')) WHERE id = 1;
+ <waiting ...>
+step c1: COMMIT;
+step updateforcip2: <... completed>
+step c2: COMMIT;
+step read_a: SELECT * FROM table_a ORDER BY id;
+id|value
+--+--------
+ 1|newValue
+(1 row)
+
+
+starting permutation: updateforcip updateforcip3 c1 c2 read_a
+step updateforcip:
+ UPDATE table_a SET value = NULL WHERE id = 1;
+
+step updateforcip3:
+ WITH d(val) AS (SELECT text 'newValue' FROM generate_series(1,1))
+ UPDATE table_a SET value = COALESCE(value, (SELECT val FROM d)) WHERE id = 1;
+ <waiting ...>
+step c1: COMMIT;
+step updateforcip3: <... completed>
+step c2: COMMIT;
+step read_a: SELECT * FROM table_a ORDER BY id;
+id|value
+--+--------
+ 1|newValue
+(1 row)
+
+
+starting permutation: wrtwcte readwcte c1 c2
+step wrtwcte: UPDATE table_a SET value = 'tableAValue2' WHERE id = 1;
+step readwcte:
+ WITH
+ cte1 AS (
+ SELECT id FROM table_b WHERE value = 'tableBValue'
+ ),
+ cte2 AS (
+ SELECT * FROM table_a
+ WHERE id = (SELECT id FROM cte1)
+ FOR UPDATE
+ )
+ SELECT * FROM cte2;
+ <waiting ...>
+step c1: COMMIT;
+step c2: COMMIT;
+step readwcte: <... completed>
+id|value
+--+------------
+ 1|tableAValue2
+(1 row)
+
+
+starting permutation: wrjt selectjoinforupdate c2 c1
+step wrjt: UPDATE jointest SET data = 42 WHERE id = 7;
+step selectjoinforupdate:
+ set local enable_nestloop to 0;
+ set local enable_hashjoin to 0;
+ set local enable_seqscan to 0;
+ explain (costs off)
+ select * from jointest a join jointest b on a.id=b.id for update;
+ select * from jointest a join jointest b on a.id=b.id for update;
+ <waiting ...>
+step c2: COMMIT;
+step selectjoinforupdate: <... completed>
+QUERY PLAN
+----------------------------------------------------------
+LockRows
+ -> Merge Join
+ Merge Cond: (a.id = b.id)
+ -> Index Scan using jointest_id_idx on jointest a
+ -> Index Scan using jointest_id_idx on jointest b
+(5 rows)
+
+id|data|id|data
+--+----+--+----
+ 1| 0| 1| 0
+ 2| 0| 2| 0
+ 3| 0| 3| 0
+ 4| 0| 4| 0
+ 5| 0| 5| 0
+ 6| 0| 6| 0
+ 7| 42| 7| 42
+ 8| 0| 8| 0
+ 9| 0| 9| 0
+10| 0|10| 0
+(10 rows)
+
+step c1: COMMIT;
+
+starting permutation: wrjt selectresultforupdate c2 c1
+step wrjt: UPDATE jointest SET data = 42 WHERE id = 7;
+step selectresultforupdate:
+ select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true
+ left join table_a a on a.id = x, jointest jt
+ where jt.id = y;
+ explain (verbose, costs off)
+ select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true
+ left join table_a a on a.id = x, jointest jt
+ where jt.id = y for update of jt, ss1, ss2;
+ select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true
+ left join table_a a on a.id = x, jointest jt
+ where jt.id = y for update of jt, ss1, ss2;
+ <waiting ...>
+step c2: COMMIT;
+step selectresultforupdate: <... completed>
+x|y|id|value |id|data
+-+-+--+-----------+--+----
+1|7| 1|tableAValue| 7| 0
+(1 row)
+
+QUERY PLAN
+--------------------------------------------------------------------
+LockRows
+ Output: 1, 7, a.id, a.value, jt.id, jt.data, jt.ctid, a.ctid
+ -> Nested Loop Left Join
+ Output: 1, 7, a.id, a.value, jt.id, jt.data, jt.ctid, a.ctid
+ -> Nested Loop
+ Output: jt.id, jt.data, jt.ctid
+ -> Seq Scan on public.jointest jt
+ Output: jt.id, jt.data, jt.ctid
+ Filter: (jt.id = 7)
+ -> Result
+ -> Seq Scan on public.table_a a
+ Output: a.id, a.value, a.ctid
+ Filter: (a.id = 1)
+(13 rows)
+
+x|y|id|value |id|data
+-+-+--+-----------+--+----
+1|7| 1|tableAValue| 7| 42
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: wrtwcte multireadwcte c1 c2
+step wrtwcte: UPDATE table_a SET value = 'tableAValue2' WHERE id = 1;
+step multireadwcte:
+ WITH updated AS (
+ UPDATE table_a SET value = 'tableAValue3' WHERE id = 1 RETURNING id
+ )
+ SELECT (SELECT id FROM updated) AS subid, * FROM updated;
+ <waiting ...>
+step c1: COMMIT;
+step c2: COMMIT;
+step multireadwcte: <... completed>
+subid|id
+-----+--
+ 1| 1
+(1 row)
+
+
+starting permutation: simplepartupdate complexpartupdate c1 c2
+step simplepartupdate:
+ update parttbl set a = a;
+
+step complexpartupdate:
+ with u as (update parttbl set a = a returning parttbl.*)
+ update parttbl set a = u.a from u;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate: <... completed>
+step c2: COMMIT;
+
+starting permutation: simplepartupdate_route1to2 complexpartupdate_route_err1 c1 c2
+step simplepartupdate_route1to2:
+ update parttbl set a = 2 where c = 1 returning *;
+
+a|b|c
+-+-+-
+2|1|1
+(1 row)
+
+step complexpartupdate_route_err1:
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = u.a from u where p.a = u.a and p.c = 1 returning p.*;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate_route_err1: <... completed>
+ERROR: tuple to be locked was already moved to another partition due to concurrent update
+step c2: COMMIT;
+
+starting permutation: simplepartupdate_noroute complexpartupdate_route c1 c2
+step simplepartupdate_noroute:
+ update parttbl set b = 2 where c = 1 returning *;
+
+a|b|c
+-+-+-
+1|2|1
+(1 row)
+
+step complexpartupdate_route:
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = p.b from u where p.a = u.a and p.c = 1 returning p.*;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate_route: <... completed>
+a|b|c
+-+-+-
+2|2|1
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: simplepartupdate_noroute complexpartupdate_doesnt_route c1 c2
+step simplepartupdate_noroute:
+ update parttbl set b = 2 where c = 1 returning *;
+
+a|b|c
+-+-+-
+1|2|1
+(1 row)
+
+step complexpartupdate_doesnt_route:
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = 3 - p.b from u where p.a = u.a and p.c = 1 returning p.*;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate_doesnt_route: <... completed>
+a|b|c
+-+-+-
+1|2|1
+(1 row)
+
+step c2: COMMIT;
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-19 06:41:13 +0000