Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 359 insertions, 0 deletions

diff --git a/src/test/isolation/specs/eval-plan-qual.spec b/src/test/isolation/specs/eval-plan-qual.spec
new file mode 100644
index 0000000..4bb9595
--- /dev/null
+++ b/src/test/isolation/specs/eval-plan-qual.spec
@@ -0,0 +1,359 @@
+# 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