1# Tests for the EvalPlanQual mechanism 2# 3# EvalPlanQual is used in READ COMMITTED isolation level to attempt to 4# re-execute UPDATE and DELETE operations against rows that were updated 5# by some concurrent transaction. 6 7setup 8{ 9 CREATE TABLE accounts (accountid text PRIMARY KEY, balance numeric not null); 10 INSERT INTO accounts VALUES ('checking', 600), ('savings', 600); 11 12 CREATE FUNCTION update_checking(int) RETURNS bool LANGUAGE sql AS $$ 13 UPDATE accounts SET balance = balance + 1 WHERE accountid = 'checking'; SELECT true;$$; 14 15 CREATE TABLE accounts_ext (accountid text PRIMARY KEY, balance numeric not null, other text); 16 INSERT INTO accounts_ext VALUES ('checking', 600, 'other'), ('savings', 700, null); 17 ALTER TABLE accounts_ext ADD COLUMN newcol int DEFAULT 42; 18 ALTER TABLE accounts_ext ADD COLUMN newcol2 text DEFAULT NULL; 19 20 CREATE TABLE p (a int, b int, c int); 21 CREATE TABLE c1 () INHERITS (p); 22 CREATE TABLE c2 () INHERITS (p); 23 CREATE TABLE c3 () INHERITS (p); 24 INSERT INTO c1 SELECT 0, a / 3, a % 3 FROM generate_series(0, 9) a; 25 INSERT INTO c2 SELECT 1, a / 3, a % 3 FROM generate_series(0, 9) a; 26 INSERT INTO c3 SELECT 2, a / 3, a % 3 FROM generate_series(0, 9) a; 27 28 CREATE TABLE table_a (id integer, value text); 29 CREATE TABLE table_b (id integer, value text); 30 INSERT INTO table_a VALUES (1, 'tableAValue'); 31 INSERT INTO table_b VALUES (1, 'tableBValue'); 32 33 CREATE TABLE jointest AS SELECT generate_series(1,10) AS id, 0 AS data; 34 CREATE INDEX ON jointest(id); 35 36 CREATE TABLE parttbl (a int, b int, c int) PARTITION BY LIST (a); 37 CREATE TABLE parttbl1 PARTITION OF parttbl FOR VALUES IN (1); 38 CREATE TABLE parttbl2 PARTITION OF parttbl FOR VALUES IN (2); 39 INSERT INTO parttbl VALUES (1, 1, 1); 40 41 CREATE TABLE another_parttbl (a int, b int, c int) PARTITION BY LIST (a); 42 CREATE TABLE another_parttbl1 PARTITION OF another_parttbl FOR VALUES IN (1); 43 CREATE TABLE another_parttbl2 PARTITION OF another_parttbl FOR VALUES IN (2); 44 INSERT INTO another_parttbl VALUES (1, 1, 1); 45 46 CREATE FUNCTION noisy_oper(p_comment text, p_a anynonarray, p_op text, p_b anynonarray) 47 RETURNS bool LANGUAGE plpgsql AS $$ 48 DECLARE 49 r bool; 50 BEGIN 51 EXECUTE format('SELECT $1 %s $2', p_op) INTO r USING p_a, p_b; 52 RAISE NOTICE '%: % % % % %: %', p_comment, pg_typeof(p_a), p_a, p_op, pg_typeof(p_b), p_b, r; 53 RETURN r; 54 END;$$; 55} 56 57teardown 58{ 59 DROP TABLE accounts; 60 DROP FUNCTION update_checking(int); 61 DROP TABLE accounts_ext; 62 DROP TABLE p CASCADE; 63 DROP TABLE table_a, table_b, jointest; 64 DROP TABLE parttbl; 65 DROP TABLE another_parttbl; 66 DROP FUNCTION noisy_oper(text, anynonarray, text, anynonarray) 67} 68 69session s1 70setup { BEGIN ISOLATION LEVEL READ COMMITTED; } 71# wx1 then wx2 checks the basic case of re-fetching up-to-date values 72step wx1 { UPDATE accounts SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance; } 73# wy1 then wy2 checks the case where quals pass then fail 74step wy1 { UPDATE accounts SET balance = balance + 500 WHERE accountid = 'checking' RETURNING balance; } 75 76step wxext1 { UPDATE accounts_ext SET balance = balance - 200 WHERE accountid = 'checking' RETURNING balance; } 77step tocds1 { UPDATE accounts SET accountid = 'cds' WHERE accountid = 'checking'; } 78step tocdsext1 { UPDATE accounts_ext SET accountid = 'cds' WHERE accountid = 'checking'; } 79 80# d1 then wx1 checks that update can deal with the updated row vanishing 81# wx2 then d1 checks that the delete affects the updated row 82# wx2, wx2 then d1 checks that the delete checks the quals correctly (balance too high) 83# wx2, d2, then d1 checks that delete handles a vanishing row correctly 84step d1 { DELETE FROM accounts WHERE accountid = 'checking' AND balance < 1500 RETURNING balance; } 85 86# upsert tests are to check writable-CTE cases 87step upsert1 { 88 WITH upsert AS 89 (UPDATE accounts SET balance = balance + 500 90 WHERE accountid = 'savings' 91 RETURNING accountid) 92 INSERT INTO accounts SELECT 'savings', 500 93 WHERE NOT EXISTS (SELECT 1 FROM upsert); 94} 95 96# tests with table p check inheritance cases: 97# readp1/writep1/readp2 tests a bug where nodeLockRows did the wrong thing 98# when the first updated tuple was in a non-first child table. 99# writep2/returningp1 tests a memory allocation issue 100# writep3a/writep3b tests updates touching more than one table 101 102step readp1 { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; } 103step writep1 { UPDATE p SET b = -1 WHERE a = 1 AND b = 1 AND c = 0; } 104step writep2 { UPDATE p SET b = -b WHERE a = 1 AND c = 0; } 105step writep3a { UPDATE p SET b = -b WHERE c = 0; } 106step c1 { COMMIT; } 107step r1 { ROLLBACK; } 108 109# these tests are meant to exercise EvalPlanQualFetchRowMark, 110# ie, handling non-locked tables in an EvalPlanQual recheck 111 112step partiallock { 113 SELECT * FROM accounts a1, accounts a2 114 WHERE a1.accountid = a2.accountid 115 FOR UPDATE OF a1; 116} 117step lockwithvalues { 118 -- Reference rowmark column that differs in type from targetlist at some attno. 119 -- See CAHU7rYZo_C4ULsAx_LAj8az9zqgrD8WDd4hTegDTMM1LMqrBsg@mail.gmail.com 120 SELECT a1.*, v.id FROM accounts a1, (values('checking'::text, 'nan'::text),('savings', 'nan')) v(id, notnumeric) 121 WHERE a1.accountid = v.id AND v.notnumeric != 'einszwei' 122 FOR UPDATE OF a1; 123} 124step partiallock_ext { 125 SELECT * FROM accounts_ext a1, accounts_ext a2 126 WHERE a1.accountid = a2.accountid 127 FOR UPDATE OF a1; 128} 129 130# these tests exercise EvalPlanQual with a SubLink sub-select (which should be 131# unaffected by any EPQ recheck behavior in the outer query); cf bug #14034 132 133step updateforss { 134 UPDATE table_a SET value = 'newTableAValue' WHERE id = 1; 135 UPDATE table_b SET value = 'newTableBValue' WHERE id = 1; 136} 137 138# these tests exercise EvalPlanQual with conditional InitPlans which 139# have not been executed prior to the EPQ 140 141step updateforcip { 142 UPDATE table_a SET value = NULL WHERE id = 1; 143} 144 145# these tests exercise mark/restore during EPQ recheck, cf bug #15032 146 147step selectjoinforupdate { 148 set local enable_nestloop to 0; 149 set local enable_hashjoin to 0; 150 set local enable_seqscan to 0; 151 explain (costs off) 152 select * from jointest a join jointest b on a.id=b.id for update; 153 select * from jointest a join jointest b on a.id=b.id for update; 154} 155 156# these tests exercise Result plan nodes participating in EPQ 157 158step selectresultforupdate { 159 select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true 160 left join table_a a on a.id = x, jointest jt 161 where jt.id = y; 162 explain (verbose, costs off) 163 select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true 164 left join table_a a on a.id = x, jointest jt 165 where jt.id = y for update of jt, ss1, ss2; 166 select * from (select 1 as x) ss1 join (select 7 as y) ss2 on true 167 left join table_a a on a.id = x, jointest jt 168 where jt.id = y for update of jt, ss1, ss2; 169} 170 171# test for EPQ on a partitioned result table 172 173step simplepartupdate { 174 update parttbl set a = a; 175} 176 177# test scenarios where update may cause row movement 178 179step simplepartupdate_route1to2 { 180 update parttbl set a = 2 where c = 1 returning *; 181} 182 183step simplepartupdate_noroute { 184 update parttbl set b = 2 where c = 1 returning *; 185} 186 187 188session s2 189setup { BEGIN ISOLATION LEVEL READ COMMITTED; } 190step wx2 { UPDATE accounts SET balance = balance + 450 WHERE accountid = 'checking' RETURNING balance; } 191step wy2 { UPDATE accounts SET balance = balance + 1000 WHERE accountid = 'checking' AND balance < 1000 RETURNING balance; } 192step d2 { DELETE FROM accounts WHERE accountid = 'checking'; } 193 194step upsert2 { 195 WITH upsert AS 196 (UPDATE accounts SET balance = balance + 1234 197 WHERE accountid = 'savings' 198 RETURNING accountid) 199 INSERT INTO accounts SELECT 'savings', 1234 200 WHERE NOT EXISTS (SELECT 1 FROM upsert); 201} 202step wx2_ext { UPDATE accounts_ext SET balance = balance + 450; } 203step readp2 { SELECT tableoid::regclass, ctid, * FROM p WHERE b IN (0, 1) AND c = 0 FOR UPDATE; } 204step returningp1 { 205 WITH u AS ( UPDATE p SET b = b WHERE a > 0 RETURNING * ) 206 SELECT * FROM u; 207} 208step writep3b { UPDATE p SET b = -b WHERE c = 0; } 209step readforss { 210 SELECT ta.id AS ta_id, ta.value AS ta_value, 211 (SELECT ROW(tb.id, tb.value) 212 FROM table_b tb WHERE ta.id = tb.id) AS tb_row 213 FROM table_a ta 214 WHERE ta.id = 1 FOR UPDATE OF ta; 215} 216step updateforcip2 { 217 UPDATE table_a SET value = COALESCE(value, (SELECT text 'newValue')) WHERE id = 1; 218} 219step updateforcip3 { 220 WITH d(val) AS (SELECT text 'newValue' FROM generate_series(1,1)) 221 UPDATE table_a SET value = COALESCE(value, (SELECT val FROM d)) WHERE id = 1; 222} 223step wrtwcte { UPDATE table_a SET value = 'tableAValue2' WHERE id = 1; } 224step wrjt { UPDATE jointest SET data = 42 WHERE id = 7; } 225step complexpartupdate { 226 with u as (update parttbl set a = a returning parttbl.*) 227 update parttbl set a = u.a from u; 228} 229 230step complexpartupdate_route_err1 { 231 with u as (update another_parttbl set a = 1 returning another_parttbl.*) 232 update parttbl p set a = u.a from u where p.a = u.a and p.c = 1 returning p.*; 233} 234 235step complexpartupdate_route { 236 with u as (update another_parttbl set a = 1 returning another_parttbl.*) 237 update parttbl p set a = p.b from u where p.a = u.a and p.c = 1 returning p.*; 238} 239 240step complexpartupdate_doesnt_route { 241 with u as (update another_parttbl set a = 1 returning another_parttbl.*) 242 update parttbl p set a = 3 - p.b from u where p.a = u.a and p.c = 1 returning p.*; 243} 244 245# Use writable CTEs to create self-updated rows, that then are 246# (updated|deleted). The *fail versions of the tests additionally 247# perform an update, via a function, in a different command, to test 248# behaviour relating to that. 249step 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 *; } 250step 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 *; } 251step delwcte { WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *) DELETE FROM accounts a USING doup RETURNING *; } 252step delwctefail { WITH doup AS (UPDATE accounts SET balance = balance + 1100 WHERE accountid = 'checking' RETURNING *, update_checking(999)) DELETE FROM accounts a USING doup RETURNING *; } 253 254# Check that nested EPQ works correctly 255step wnested2 { 256 UPDATE accounts SET balance = balance - 1200 257 WHERE noisy_oper('upid', accountid, '=', 'checking') 258 AND noisy_oper('up', balance, '>', 200.0) 259 AND EXISTS ( 260 SELECT accountid 261 FROM accounts_ext ae 262 WHERE noisy_oper('lock_id', ae.accountid, '=', accounts.accountid) 263 AND noisy_oper('lock_bal', ae.balance, '>', 200.0) 264 FOR UPDATE 265 ); 266} 267 268step c2 { COMMIT; } 269step r2 { ROLLBACK; } 270 271session s3 272setup { BEGIN ISOLATION LEVEL READ COMMITTED; } 273step read { SELECT * FROM accounts ORDER BY accountid; } 274step read_ext { SELECT * FROM accounts_ext ORDER BY accountid; } 275step read_a { SELECT * FROM table_a ORDER BY id; } 276 277# this test exercises EvalPlanQual with a CTE, cf bug #14328 278step readwcte { 279 WITH 280 cte1 AS ( 281 SELECT id FROM table_b WHERE value = 'tableBValue' 282 ), 283 cte2 AS ( 284 SELECT * FROM table_a 285 WHERE id = (SELECT id FROM cte1) 286 FOR UPDATE 287 ) 288 SELECT * FROM cte2; 289} 290 291# this test exercises a different CTE misbehavior, cf bug #14870 292step multireadwcte { 293 WITH updated AS ( 294 UPDATE table_a SET value = 'tableAValue3' WHERE id = 1 RETURNING id 295 ) 296 SELECT (SELECT id FROM updated) AS subid, * FROM updated; 297} 298 299teardown { COMMIT; } 300 301# test that normal update follows update chains, and reverifies quals 302permutation wx1 wx2 c1 c2 read 303permutation wy1 wy2 c1 c2 read 304permutation wx1 wx2 r1 c2 read 305permutation wy1 wy2 r1 c2 read 306 307# test that deletes follow chains, and if necessary reverifies quals 308permutation wx1 d1 wx2 c1 c2 read 309permutation wx2 d1 c2 c1 read 310permutation wx2 wx2 d1 c2 c1 read 311permutation wx2 d2 d1 c2 c1 read 312permutation wx1 d1 wx2 r1 c2 read 313permutation wx2 d1 r2 c1 read 314permutation wx2 wx2 d1 r2 c1 read 315permutation wx2 d2 d1 r2 c1 read 316permutation d1 wx2 c1 c2 read 317permutation d1 wx2 r1 c2 read 318 319# Check that nested EPQ works correctly 320permutation wnested2 c1 c2 read 321permutation wx1 wxext1 wnested2 c1 c2 read 322permutation wx1 wx1 wxext1 wnested2 c1 c2 read 323permutation wx1 wx1 wxext1 wxext1 wnested2 c1 c2 read 324permutation wx1 wxext1 wxext1 wnested2 c1 c2 read 325permutation wx1 tocds1 wnested2 c1 c2 read 326permutation wx1 tocdsext1 wnested2 c1 c2 read 327 328# test that an update to a self-modified row is ignored when 329# previously updated by the same cid 330permutation wx1 updwcte c1 c2 read 331# test that an update to a self-modified row throws error when 332# previously updated by a different cid 333permutation wx1 updwctefail c1 c2 read 334# test that a delete to a self-modified row is ignored when 335# previously updated by the same cid 336permutation wx1 delwcte c1 c2 read 337# test that a delete to a self-modified row throws error when 338# previously updated by a different cid 339permutation wx1 delwctefail c1 c2 read 340 341permutation upsert1 upsert2 c1 c2 read 342permutation readp1 writep1 readp2 c1 c2 343permutation writep2 returningp1 c1 c2 344permutation writep3a writep3b c1 c2 345permutation wx2 partiallock c2 c1 read 346permutation wx2 lockwithvalues c2 c1 read 347permutation wx2_ext partiallock_ext c2 c1 read_ext 348permutation updateforss readforss c1 c2 349permutation updateforcip updateforcip2 c1 c2 read_a 350permutation updateforcip updateforcip3 c1 c2 read_a 351permutation wrtwcte readwcte c1 c2 352permutation wrjt selectjoinforupdate c2 c1 353permutation wrjt selectresultforupdate c2 c1 354permutation wrtwcte multireadwcte c1 c2 355 356permutation simplepartupdate complexpartupdate c1 c2 357permutation simplepartupdate_route1to2 complexpartupdate_route_err1 c1 c2 358permutation simplepartupdate_noroute complexpartupdate_route c1 c2 359permutation simplepartupdate_noroute complexpartupdate_doesnt_route c1 c2 360