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