1-- tests for tidscans 2CREATE TABLE tidscan(id integer); 3-- only insert a few rows, we don't want to spill onto a second table page 4INSERT INTO tidscan VALUES (1), (2), (3); 5-- show ctids 6SELECT ctid, * FROM tidscan; 7 ctid | id 8-------+---- 9 (0,1) | 1 10 (0,2) | 2 11 (0,3) | 3 12(3 rows) 13 14-- ctid equality - implemented as tidscan 15EXPLAIN (COSTS OFF) 16SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)'; 17 QUERY PLAN 18----------------------------------- 19 Tid Scan on tidscan 20 TID Cond: (ctid = '(0,1)'::tid) 21(2 rows) 22 23SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)'; 24 ctid | id 25-------+---- 26 (0,1) | 1 27(1 row) 28 29EXPLAIN (COSTS OFF) 30SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid; 31 QUERY PLAN 32----------------------------------- 33 Tid Scan on tidscan 34 TID Cond: ('(0,1)'::tid = ctid) 35(2 rows) 36 37SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid; 38 ctid | id 39-------+---- 40 (0,1) | 1 41(1 row) 42 43-- ctid = ScalarArrayOp - implemented as tidscan 44EXPLAIN (COSTS OFF) 45SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 46 QUERY PLAN 47------------------------------------------------------- 48 Tid Scan on tidscan 49 TID Cond: (ctid = ANY ('{"(0,1)","(0,2)"}'::tid[])) 50(2 rows) 51 52SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 53 ctid | id 54-------+---- 55 (0,1) | 1 56 (0,2) | 2 57(2 rows) 58 59-- ctid != ScalarArrayOp - can't be implemented as tidscan 60EXPLAIN (COSTS OFF) 61SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 62 QUERY PLAN 63------------------------------------------------------ 64 Seq Scan on tidscan 65 Filter: (ctid <> ANY ('{"(0,1)","(0,2)"}'::tid[])) 66(2 rows) 67 68SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 69 ctid | id 70-------+---- 71 (0,1) | 1 72 (0,2) | 2 73 (0,3) | 3 74(3 rows) 75 76-- tid equality extracted from sub-AND clauses 77EXPLAIN (COSTS OFF) 78SELECT ctid, * FROM tidscan 79WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); 80 QUERY PLAN 81-------------------------------------------------------------------------------------------------------------- 82 Tid Scan on tidscan 83 TID Cond: ((ctid = ANY ('{"(0,2)","(0,3)"}'::tid[])) OR (ctid = '(0,1)'::tid)) 84 Filter: (((id = 3) AND (ctid = ANY ('{"(0,2)","(0,3)"}'::tid[]))) OR ((ctid = '(0,1)'::tid) AND (id = 1))) 85(3 rows) 86 87SELECT ctid, * FROM tidscan 88WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); 89 ctid | id 90-------+---- 91 (0,1) | 1 92 (0,3) | 3 93(2 rows) 94 95-- exercise backward scan and rewind 96BEGIN; 97DECLARE c CURSOR FOR 98SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]); 99FETCH ALL FROM c; 100 ctid | id 101-------+---- 102 (0,1) | 1 103 (0,2) | 2 104(2 rows) 105 106FETCH BACKWARD 1 FROM c; 107 ctid | id 108-------+---- 109 (0,2) | 2 110(1 row) 111 112FETCH FIRST FROM c; 113 ctid | id 114-------+---- 115 (0,1) | 1 116(1 row) 117 118ROLLBACK; 119-- tidscan via CURRENT OF 120BEGIN; 121DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; 122FETCH NEXT FROM c; -- skip one row 123 ctid | id 124-------+---- 125 (0,1) | 1 126(1 row) 127 128FETCH NEXT FROM c; 129 ctid | id 130-------+---- 131 (0,2) | 2 132(1 row) 133 134-- perform update 135EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 136UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 137 QUERY PLAN 138--------------------------------------------------- 139 Update on tidscan (actual rows=1 loops=1) 140 -> Tid Scan on tidscan (actual rows=1 loops=1) 141 TID Cond: CURRENT OF c 142(3 rows) 143 144FETCH NEXT FROM c; 145 ctid | id 146-------+---- 147 (0,3) | 3 148(1 row) 149 150-- perform update 151EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 152UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 153 QUERY PLAN 154--------------------------------------------------- 155 Update on tidscan (actual rows=1 loops=1) 156 -> Tid Scan on tidscan (actual rows=1 loops=1) 157 TID Cond: CURRENT OF c 158(3 rows) 159 160SELECT * FROM tidscan; 161 id 162---- 163 1 164 -2 165 -3 166(3 rows) 167 168-- position cursor past any rows 169FETCH NEXT FROM c; 170 ctid | id 171------+---- 172(0 rows) 173 174-- should error out 175EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) 176UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; 177ERROR: cursor "c" is not positioned on a row 178ROLLBACK; 179DROP TABLE tidscan; 180