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-- ctid = ScalarArrayOp - implemented as tidscan 21EXPLAIN (COSTS OFF) 22SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 23SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 24 25-- ctid != ScalarArrayOp - can't be 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-- tid equality extracted from sub-AND clauses 31EXPLAIN (COSTS OFF) 32SELECT ctid, * FROM tidscan 33WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); 34SELECT ctid, * FROM tidscan 35WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); 36 37-- exercise backward scan and rewind 38BEGIN; 39DECLARE c CURSOR FOR 40SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 41FETCH ALL FROM c; 42FETCH BACKWARD 1 FROM c; 43FETCH FIRST FROM c; 44ROLLBACK; 45 46-- tidscan via CURRENT OF 47BEGIN; 48DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; 49FETCH NEXT FROM c; -- skip one row 50FETCH NEXT FROM c; 51-- perform update 52EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 53UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 54FETCH NEXT FROM c; 55-- perform update 56EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 57UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 58SELECT * FROM tidscan; 59-- position cursor past any rows 60FETCH NEXT FROM c; 61-- should error out 62EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 63UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 64ROLLBACK; 65 66DROP TABLE tidscan; 67