1-- tests for tidscans 2 3CREATE TABLE tidscan(id integer); 4 5-- only insert a few rows, we don't want to spill onto a second table page 6INSERT INTO tidscan VALUES (1), (2), (3); 7 8-- show ctids 9SELECT ctid, * FROM tidscan; 10 11-- ctid equality - implemented as tidscan 12EXPLAIN (COSTS OFF) 13SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)'; 14SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)'; 15 16EXPLAIN (COSTS OFF) 17SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid; 18SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid; 19 20-- OR'd clauses 21EXPLAIN (COSTS OFF) 22SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid; 23SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid; 24 25-- ctid = ScalarArrayOp - implemented as tidscan 26EXPLAIN (COSTS OFF) 27SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 28SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 29 30-- ctid != ScalarArrayOp - can't be implemented as tidscan 31EXPLAIN (COSTS OFF) 32SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 33SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 34 35-- tid equality extracted from sub-AND clauses 36EXPLAIN (COSTS OFF) 37SELECT ctid, * FROM tidscan 38WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); 39SELECT ctid, * FROM tidscan 40WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); 41 42-- nestloop-with-inner-tidscan joins on tid 43SET enable_hashjoin TO off; -- otherwise hash join might win 44EXPLAIN (COSTS OFF) 45SELECT t1.ctid, t1.*, t2.ctid, t2.* 46FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; 47SELECT t1.ctid, t1.*, t2.ctid, t2.* 48FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; 49EXPLAIN (COSTS OFF) 50SELECT t1.ctid, t1.*, t2.ctid, t2.* 51FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; 52SELECT t1.ctid, t1.*, t2.ctid, t2.* 53FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; 54RESET enable_hashjoin; 55 56-- exercise backward scan and rewind 57BEGIN; 58DECLARE c CURSOR FOR 59SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 60FETCH ALL FROM c; 61FETCH BACKWARD 1 FROM c; 62FETCH FIRST FROM c; 63ROLLBACK; 64 65-- tidscan via CURRENT OF 66BEGIN; 67DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; 68FETCH NEXT FROM c; -- skip one row 69FETCH NEXT FROM c; 70-- perform update 71EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 72UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 73FETCH NEXT FROM c; 74-- perform update 75EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 76UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 77SELECT * FROM tidscan; 78-- position cursor past any rows 79FETCH NEXT FROM c; 80-- should error out 81EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 82UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 83ROLLBACK; 84 85-- bulk joins on CTID 86-- (these plans don't use TID scans, but this still seems like an 87-- appropriate place for these tests) 88EXPLAIN (COSTS OFF) 89SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; 90SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; 91SET enable_hashjoin TO off; 92EXPLAIN (COSTS OFF) 93SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; 94SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; 95RESET enable_hashjoin; 96 97-- check predicate lock on CTID 98BEGIN ISOLATION LEVEL SERIALIZABLE; 99SELECT * FROM tidscan WHERE ctid = '(0,1)'; 100-- locktype should be 'tuple' 101SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND mode = 'SIReadLock'; 102ROLLBACK; 103 104DROP TABLE tidscan; 105