Subject: [PATCH v20 1/2] Expand EPQ tests for UPDATEs and DELETEs

From: Andres Freund <[email-masked]>
Date: Wed, 20 Mar 2019 11:44:01 -0700

Previously there was basically no coverage for UPDATEs encountering
deleted rows, and no coverage for DELETE having to perform EPQ. That's
problematic for an upcoming commit in which EPQ is tought to integrate
with tableams.

Author: Andres Freund
---
 .../isolation/expected/eval-plan-qual.out     | 218 +++++++++++++++++-
 src/test/isolation/specs/eval-plan-qual.spec  |  33 ++-
 2 files changed, 241 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..bab01e0788a 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,195 @@ 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: upsert1 upsert2 c1 c2 read
 step upsert1: 
 	WITH upsert AS
@@ -106,7 +306,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 +329,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/specs/eval-plan-qual.spec b/src/test/isolation/specs/eval-plan-qual.spec
index 2e1b5095e8e..c22bf65f42f 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,22 @@ 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 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 "upsert1" "upsert2" "c1" "c2" "read"
 permutation "readp1" "writep1" "readp2" "c1" "c2"
 permutation "writep2" "returningp1" "c1" "c2"
-- 
2.21.0.dirty

--qw3ipai4e5xhdytn
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v20-0002-tableam-Add-insert-delete-update-lock_tuple.patch"