1 -- txid_snapshot data type and related functions
3 select '12:13:'::txid_snapshot;
9 select '12:18:14,16'::txid_snapshot;
16 select '31:12:'::txid_snapshot;
17 ERROR: invalid input for txid_snapshot: "31:12:"
18 select '0:1:'::txid_snapshot;
19 ERROR: invalid input for txid_snapshot: "0:1:"
20 select '12:13:0'::txid_snapshot;
21 ERROR: invalid input for txid_snapshot: "12:13:0"
22 select '12:16:14,13'::txid_snapshot;
23 ERROR: invalid input for txid_snapshot: "12:16:14,13"
24 select '12:16:14,14'::txid_snapshot;
25 ERROR: invalid input for txid_snapshot: "12:16:14,14"
26 create temp table snapshot_test (
30 insert into snapshot_test values (1, '12:13:');
31 insert into snapshot_test values (2, '12:20:13,15,18');
32 insert into snapshot_test values (3, '100001:100009:100005,100007,100008');
33 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');
34 select snap from snapshot_test order by nr;
36 -------------------------------------------------------------------------------------------------------------------------------------
39 100001:100009:100005,100007,100008
40 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
43 select txid_snapshot_xmin(snap),
44 txid_snapshot_xmax(snap),
45 txid_snapshot_xip(snap)
46 from snapshot_test order by nr;
47 txid_snapshot_xmin | txid_snapshot_xmax | txid_snapshot_xip
48 --------------------+--------------------+-------------------
52 100001 | 100009 | 100005
53 100001 | 100009 | 100007
54 100001 | 100009 | 100008
88 select id, txid_visible_in_snapshot(id, snap)
89 from snapshot_test, generate_series(11, 21) id
91 id | txid_visible_in_snapshot
92 ----+--------------------------
107 select id, txid_visible_in_snapshot(id, snap)
108 from snapshot_test, generate_series(90, 160) id
110 id | txid_visible_in_snapshot
111 -----+--------------------------
185 -- test current values also
186 select txid_current() >= txid_snapshot_xmin(txid_current_snapshot());
192 -- we can't assume current is always less than xmax, however
193 select txid_visible_in_snapshot(txid_current(), txid_current_snapshot());
194 txid_visible_in_snapshot
195 --------------------------
200 select txid_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013';
202 ---------------------------------------------------------------------
203 1000100010001000:1000100010001100:1000100010001012,1000100010001013
206 select txid_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
207 txid_visible_in_snapshot
208 --------------------------
212 select txid_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
213 txid_visible_in_snapshot
214 --------------------------
218 -- test 64bit overflow
219 SELECT txid_snapshot '1:9223372036854775807:3';
221 -------------------------
222 1:9223372036854775807:3
225 SELECT txid_snapshot '1:9223372036854775808:3';
226 ERROR: invalid input for txid_snapshot: "1:9223372036854775808:3"