From 3e5458614abf572bd6afc83ce203b4fec8f18363 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Wed, 20 Mar 2019 11:44:01 -0700 Subject: [PATCH v20 1/2] Expand EPQ tests for UPDATEs and DELETEs 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'; +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; 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; +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; 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; +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; +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; +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; +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; +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; +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; +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; +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; +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; +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