path: root/src/test/isolation/specs/eval-plan-qual.spec

# 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