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