| # Tests for the EvalPlanQual mechanism |
| # |
| # EvalPlanQual is used in READ COMMITTED isolation level to attempt to |
| # re-execute UPDATE and DELETE operations against rows that were updated |
| # by some concurrent transaction. |
| |
| setup |
| { |
| CREATE TABLE accounts (accountid text PRIMARY KEY, balance numeric not null); |
| INSERT INTO accounts VALUES ('checking', 600), ('savings', 600); |
| |
| CREATE FUNCTION update_checking(int) RETURNS bool LANGUAGE sql AS $$ |
| UPDATE accounts SET balance = balance + 1 WHERE accountid = 'checking'; SELECT true;$$; |
| |
| CREATE TABLE accounts_ext (accountid text PRIMARY KEY, balance numeric not null, other text); |
| INSERT INTO accounts_ext VALUES ('checking', 600, 'other'), ('savings', 700, null); |
| ALTER TABLE accounts_ext ADD COLUMN newcol int DEFAULT 42; |
| ALTER TABLE accounts_ext ADD COLUMN newcol2 text DEFAULT NULL; |
| |
| CREATE TABLE p (a int, b int, c int); |
| CREATE TABLE c1 () INHERITS (p); |
| CREATE TABLE c2 () INHERITS (p); |
| CREATE TABLE c3 () INHERITS (p); |
| INSERT INTO c1 SELECT 0, a / 3, a % 3 FROM generate_series(0, 9) a; |
| INSERT INTO c2 SELECT 1, a / 3, a % 3 FROM generate_series(0, 9) a; |
| INSERT INTO c3 SELECT 2, a / 3, a % 3 FROM generate_series(0, 9) a; |
| |
| CREATE TABLE table_a (id integer, value text); |
| CREATE TABLE table_b (id integer, value text); |
| INSERT INTO table_a VALUES (1, 'tableAValue'); |
| INSERT INTO table_b VALUES (1, 'tableBValue'); |
| |
| CREATE TABLE jointest AS SELECT generate_series(1,10) AS id, 0 AS data; |
| CREATE INDEX ON jointest(id); |
| |
| CREATE TABLE parttbl (a int, b int, c int) PARTITION BY LIST (a); |
| CREATE TABLE parttbl1 PARTITION OF parttbl FOR VALUES IN (1); |
| CREATE TABLE parttbl2 PARTITION OF parttbl FOR VALUES IN (2); |
| INSERT INTO parttbl VALUES (1, 1, 1); |
| |
| CREATE TABLE another_parttbl (a int, b int, c int) PARTITION BY LIST (a); |
| CREATE TABLE another_parttbl1 PARTITION OF another_parttbl FOR VALUES IN (1); |
| CREATE TABLE another_parttbl2 PARTITION OF another_parttbl FOR VALUES IN (2); |
| INSERT INTO another_parttbl VALUES (1, 1, 1); |
| |
| CREATE FUNCTION noisy_oper(p_comment text, p_a anynonarray, p_op text, p_b anynonarray) |
| RETURNS bool LANGUAGE plpgsql AS $$ |
| DECLARE |
| r bool; |
| BEGIN |
| EXECUTE format('SELECT $1 %s $2', p_op) INTO r USING p_a, p_b; |
| RAISE NOTICE '%: % % % % %: %', p_comment, pg_typeof(p_a), p_a, p_op, pg_typeof(p_b), p_b, r; |
| RETURN r; |
| END;$$; |
| } |
| |
| teardown |
| { |
| DROP TABLE accounts; |
| DROP FUNCTION update_checking(int); |
| DROP TABLE accounts_ext; |
| DROP TABLE p CASCADE; |
| DROP TABLE table_a, table_b, jointest; |
| DROP TABLE parttbl; |
| DROP TABLE another_parttbl; |
| DROP FUNCTION noisy_oper(text, anynonarray, text, anynonarray) |
| } |
| |
| 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' RETURNING balance; } |
| # wy1 then wy2 checks the case where quals pass then fail |
| step wy1 { UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance; } |
| |
| step wxext1 { UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance; } |
| step tocds1 { UPDATE accounts SET accountid = 'cds' WHERE accountid = 'checking'; } |
| step tocdsext1 { UPDATE accounts_ext SET accountid = 'cds' WHERE accountid = 'checking'; } |
| |
| # 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 |
| (UPDATE accounts SET balance = balance + 500 |
| WHERE accountid = 'savings' |
| RETURNING accountid) |
| INSERT INTO accounts SELECT 'savings', 500 |
| WHERE NOT EXISTS (SELECT 1 FROM upsert); |
| } |
| |
| # tests with table p check inheritance cases: |
| # readp1/writep1/readp2 tests a bug where nodeLockRows did the wrong thing |
| # when the first updated tuple was in a non-first child table. |
| # writep2/returningp1 tests a memory allocation issue |
| # writep3a/writep3b tests updates touching more than one table |
| |
| step readp1 { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; } |
| 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 writep3a { UPDATE p SET b = -b WHERE c = 0; } |
| step c1 { COMMIT; } |
| step r1 { ROLLBACK; } |
| |
| # these tests are meant to exercise EvalPlanQualFetchRowMark, |
| # ie, handling non-locked tables in an EvalPlanQual recheck |
| |
| step partiallock { |
| SELECT * FROM accounts a1, accounts a2 |
| WHERE a1.accountid = a2.accountid |
| FOR UPDATE OF a1; |
| } |
| 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; |
| } |
| step partiallock_ext { |
| SELECT * FROM accounts_ext a1, accounts_ext a2 |
| WHERE a1.accountid = a2.accountid |
| FOR UPDATE OF a1; |
| } |
| |
| # these tests exercise EvalPlanQual with a SubLink sub-select (which should be |
| # unaffected by any EPQ recheck behavior in the outer query); cf bug #14034 |
| |
| step updateforss { |
| UPDATE table_a SET value = 'newTableAValue' WHERE id = 1; |
| UPDATE table_b SET value = 'newTableBValue' WHERE id = 1; |
| } |
| |
| # these tests exercise EvalPlanQual with conditional InitPlans which |
| # have not been executed prior to the EPQ |
| |
| step updateforcip { |
| UPDATE table_a SET value = NULL WHERE id = 1; |
| } |
| |
| # these tests exercise mark/restore during EPQ recheck, cf bug #15032 |
| |
| 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; |
| } |
| |
| # these tests exercise Result plan nodes participating in EPQ |
| |
| 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; |
| } |
| |
| # test for EPQ on a partitioned result table |
| |
| step simplepartupdate { |
| update parttbl set a = a; |
| } |
| |
| # test scenarios where update may cause row movement |
| |
| step simplepartupdate_route1to2 { |
| update parttbl set a = 2 where c = 1 returning *; |
| } |
| |
| step simplepartupdate_noroute { |
| update parttbl set b = 2 where c = 1 returning *; |
| } |
| |
| |
| session s2 |
| setup { BEGIN ISOLATION LEVEL READ COMMITTED; } |
| 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 |
| WHERE accountid = 'savings' |
| RETURNING accountid) |
| INSERT INTO accounts SELECT 'savings', 1234 |
| WHERE NOT EXISTS (SELECT 1 FROM upsert); |
| } |
| step wx2_ext { UPDATE accounts_ext SET balance = balance + 450; } |
| step readp2 { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; } |
| step returningp1 { |
| WITH u AS ( UPDATE p SET b = b WHERE a > 0 RETURNING * ) |
| SELECT * FROM u; |
| } |
| step writep3b { UPDATE p SET b = -b WHERE c = 0; } |
| 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; |
| } |
| step updateforcip2 { |
| UPDATE table_a SET value = COALESCE(value, (SELECT text 'newValue')) 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; |
| } |
| step wrtwcte { UPDATE table_a SET value = 'tableAValue2' WHERE id = 1; } |
| step wrjt { UPDATE jointest SET data = 42 WHERE id = 7; } |
| step complexpartupdate { |
| with u as (update parttbl set a = a returning parttbl.*) |
| update parttbl set a = u.a from u; |
| } |
| |
| 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.*; |
| } |
| |
| 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.*; |
| } |
| |
| 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.*; |
| } |
| |
| # Use writable CTEs to create self-updated rows, that then are |
| # (updated|deleted). The *fail versions of the tests additionally |
| # perform an update, via a function, in a different command, to test |
| # behaviour relating to that. |
| 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 *; } |
| 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 *; } |
| step delwcte { WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *) DELETE FROM accounts a USING doup RETURNING *; } |
| 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 *; } |
| |
| # Check that nested EPQ works correctly |
| 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 c2 { COMMIT; } |
| step r2 { ROLLBACK; } |
| |
| session s3 |
| setup { BEGIN ISOLATION LEVEL READ COMMITTED; } |
| step read { SELECT * FROM accounts ORDER BY accountid; } |
| step read_ext { SELECT * FROM accounts_ext ORDER BY accountid; } |
| step read_a { SELECT * FROM table_a ORDER BY id; } |
| |
| # this test exercises EvalPlanQual with a CTE, cf bug #14328 |
| 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; |
| } |
| |
| # this test exercises a different CTE misbehavior, cf bug #14870 |
| 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; |
| } |
| |
| 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 |
| |
| # Check that nested EPQ works correctly |
| permutation wnested2 c1 c2 read |
| permutation wx1 wxext1 wnested2 c1 c2 read |
| permutation wx1 wx1 wxext1 wnested2 c1 c2 read |
| permutation wx1 wx1 wxext1 wxext1 wnested2 c1 c2 read |
| permutation wx1 wxext1 wxext1 wnested2 c1 c2 read |
| permutation wx1 tocds1 wnested2 c1 c2 read |
| permutation wx1 tocdsext1 wnested2 c1 c2 read |
| |
| # test that an update to a self-modified row is ignored when |
| # previously updated by the same cid |
| permutation wx1 updwcte c1 c2 read |
| # test that an update to a self-modified row throws error when |
| # previously updated by a different cid |
| permutation wx1 updwctefail c1 c2 read |
| # test that a delete to a self-modified row is ignored when |
| # previously updated by the same cid |
| permutation wx1 delwcte c1 c2 read |
| # test that a delete to a self-modified row throws error when |
| # previously updated by a different cid |
| permutation wx1 delwctefail c1 c2 read |
| |
| permutation upsert1 upsert2 c1 c2 read |
| permutation readp1 writep1 readp2 c1 c2 |
| permutation writep2 returningp1 c1 c2 |
| permutation writep3a writep3b c1 c2 |
| permutation wx2 partiallock c2 c1 read |
| permutation wx2 lockwithvalues c2 c1 read |
| permutation wx2_ext partiallock_ext c2 c1 read_ext |
| permutation updateforss readforss c1 c2 |
| permutation updateforcip updateforcip2 c1 c2 read_a |
| permutation updateforcip updateforcip3 c1 c2 read_a |
| permutation wrtwcte readwcte c1 c2 |
| permutation wrjt selectjoinforupdate c2 c1 |
| permutation wrjt selectresultforupdate c2 c1 |
| permutation wrtwcte multireadwcte c1 c2 |
| |
| permutation simplepartupdate complexpartupdate c1 c2 |
| permutation simplepartupdate_route1to2 complexpartupdate_route_err1 c1 c2 |
| permutation simplepartupdate_noroute complexpartupdate_route c1 c2 |
| permutation simplepartupdate_noroute complexpartupdate_doesnt_route c1 c2 |