| blob | 930b86a65628482cc4e841f89ffbab6055cfe720 |
1 -- txid_snapshot data type and related functions
2 -- i/o
3 select '12:13:'::txid_snapshot;
4 txid_snapshot
5 ---------------
6 12:13:
7 (1 row)
9 select '12:18:14,16'::txid_snapshot;
10 txid_snapshot
11 ---------------
12 12:18:14,16
13 (1 row)
15 -- errors
16 select '31:12:'::txid_snapshot;
17 ERROR: invalid input for txid_snapshot: "31:12:"
18 LINE 1: select '31:12:'::txid_snapshot;
19 ^
20 select '0:1:'::txid_snapshot;
21 ERROR: invalid input for txid_snapshot: "0:1:"
22 LINE 1: select '0:1:'::txid_snapshot;
23 ^
24 select '12:13:0'::txid_snapshot;
25 ERROR: invalid input for txid_snapshot: "12:13:0"
26 LINE 1: select '12:13:0'::txid_snapshot;
27 ^
28 select '12:16:14,13'::txid_snapshot;
29 ERROR: invalid input for txid_snapshot: "12:16:14,13"
30 LINE 1: select '12:16:14,13'::txid_snapshot;
31 ^
32 select '12:16:14,14'::txid_snapshot;
33 ERROR: invalid input for txid_snapshot: "12:16:14,14"
34 LINE 1: select '12:16:14,14'::txid_snapshot;
35 ^
36 create temp table snapshot_test (
37 nr integer,
38 snap txid_snapshot
39 );
40 insert into snapshot_test values (1, '12:13:');
41 insert into snapshot_test values (2, '12:20:13,15,18');
42 insert into snapshot_test values (3, '100001:100009:100005,100007,100008');
43 insert into snapshot_test values (4, '100:150: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');
44 select snap from snapshot_test order by nr;
45 snap
46 -------------------------------------------------------------------------------------------------------------------------------------
47 12:13:
48 12:20:13,15,18
49 100001:100009:100005,100007,100008
50 100:150: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
51 (4 rows)
53 select txid_snapshot_xmin(snap),
54 txid_snapshot_xmax(snap),
55 txid_snapshot_xip(snap)
56 from snapshot_test order by nr;
57 txid_snapshot_xmin | txid_snapshot_xmax | txid_snapshot_xip
58 --------------------+--------------------+-------------------
59 12 | 20 | 13
60 12 | 20 | 15
61 12 | 20 | 18
62 100001 | 100009 | 100005
63 100001 | 100009 | 100007
64 100001 | 100009 | 100008
65 100 | 150 | 101
66 100 | 150 | 102
67 100 | 150 | 103
68 100 | 150 | 104
69 100 | 150 | 105
70 100 | 150 | 106
71 100 | 150 | 107
72 100 | 150 | 108
73 100 | 150 | 109
74 100 | 150 | 110
75 100 | 150 | 111
76 100 | 150 | 112
77 100 | 150 | 113
78 100 | 150 | 114
79 100 | 150 | 115
80 100 | 150 | 116
81 100 | 150 | 117
82 100 | 150 | 118
83 100 | 150 | 119
84 100 | 150 | 120
85 100 | 150 | 121
86 100 | 150 | 122
87 100 | 150 | 123
88 100 | 150 | 124
89 100 | 150 | 125
90 100 | 150 | 126
91 100 | 150 | 127
92 100 | 150 | 128
93 100 | 150 | 129
94 100 | 150 | 130
95 100 | 150 | 131
96 (37 rows)
98 select id, txid_visible_in_snapshot(id, snap)
99 from snapshot_test, generate_series(11, 21) id
100 where nr = 2;
101 id | txid_visible_in_snapshot
102 ----+--------------------------
103 11 | t
104 12 | t
105 13 | f
106 14 | t
107 15 | f
108 16 | t
109 17 | t
110 18 | f
111 19 | t
112 20 | f
113 21 | f
114 (11 rows)
116 -- test bsearch
117 select id, txid_visible_in_snapshot(id, snap)
118 from snapshot_test, generate_series(90, 160) id
119 where nr = 4;
120 id | txid_visible_in_snapshot
121 -----+--------------------------
122 90 | t
123 91 | t
124 92 | t
125 93 | t
126 94 | t
127 95 | t
128 96 | t
129 97 | t
130 98 | t
131 99 | t
132 100 | t
133 101 | f
134 102 | f
135 103 | f
136 104 | f
137 105 | f
138 106 | f
139 107 | f
140 108 | f
141 109 | f
142 110 | f
143 111 | f
144 112 | f
145 113 | f
146 114 | f
147 115 | f
148 116 | f
149 117 | f
150 118 | f
151 119 | f
152 120 | f
153 121 | f
154 122 | f
155 123 | f
156 124 | f
157 125 | f
158 126 | f
159 127 | f
160 128 | f
161 129 | f
162 130 | f
163 131 | f
164 132 | t
165 133 | t
166 134 | t
167 135 | t
168 136 | t
169 137 | t
170 138 | t
171 139 | t
172 140 | t
173 141 | t
174 142 | t
175 143 | t
176 144 | t
177 145 | t
178 146 | t
179 147 | t
180 148 | t
181 149 | t
182 150 | f
183 151 | f
184 152 | f
185 153 | f
186 154 | f
187 155 | f
188 156 | f
189 157 | f
190 158 | f
191 159 | f
192 160 | f
193 (71 rows)
195 -- test current values also
196 select txid_current() >= txid_snapshot_xmin(txid_current_snapshot());
197 ?column?
198 ----------
199 t
200 (1 row)
202 -- we can't assume current is always less than xmax, however
203 select txid_visible_in_snapshot(txid_current(), txid_current_snapshot());
204 txid_visible_in_snapshot
205 --------------------------
206 f
207 (1 row)
209 -- test 64bitness
210 select txid_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013';
211 txid_snapshot
212 ---------------------------------------------------------------------
213 1000100010001000:1000100010001100:1000100010001012,1000100010001013
214 (1 row)
216 select txid_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
217 txid_visible_in_snapshot
218 --------------------------
219 f
220 (1 row)
222 select txid_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
223 txid_visible_in_snapshot
224 --------------------------
225 t
226 (1 row)
228 -- test 64bit overflow
229 SELECT txid_snapshot '1:9223372036854775807:3';
230 txid_snapshot
231 -------------------------
232 1:9223372036854775807:3
233 (1 row)
235 SELECT txid_snapshot '1:9223372036854775808:3';
236 ERROR: invalid input for txid_snapshot: "1:9223372036854775808:3"
237 LINE 1: SELECT txid_snapshot '1:9223372036854775808:3';
238 ^