1 -- txid_snapshot data type and related functions
3 select '12:13:'::txid_snapshot;
9 select '12:18:14,16'::txid_snapshot;
15 select '12:16:14,14'::txid_snapshot;
22 select '31:12:'::txid_snapshot;
23 ERROR: invalid input syntax for type txid_snapshot: "31:12:"
24 LINE 1: select '31:12:'::txid_snapshot;
26 select '0:1:'::txid_snapshot;
27 ERROR: invalid input syntax for type txid_snapshot: "0:1:"
28 LINE 1: select '0:1:'::txid_snapshot;
30 select '12:13:0'::txid_snapshot;
31 ERROR: invalid input syntax for type txid_snapshot: "12:13:0"
32 LINE 1: select '12:13:0'::txid_snapshot;
34 select '12:16:14,13'::txid_snapshot;
35 ERROR: invalid input syntax for type txid_snapshot: "12:16:14,13"
36 LINE 1: select '12:16:14,13'::txid_snapshot;
38 create temp table snapshot_test (
42 insert into snapshot_test values (1, '12:13:');
43 insert into snapshot_test values (2, '12:20:13,15,18');
44 insert into snapshot_test values (3, '100001:100009:100005,100007,100008');
45 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');
46 select snap from snapshot_test order by nr;
48 -------------------------------------------------------------------------------------------------------------------------------------
51 100001:100009:100005,100007,100008
52 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
55 select txid_snapshot_xmin(snap),
56 txid_snapshot_xmax(snap),
57 txid_snapshot_xip(snap)
58 from snapshot_test order by nr;
59 txid_snapshot_xmin | txid_snapshot_xmax | txid_snapshot_xip
60 --------------------+--------------------+-------------------
64 100001 | 100009 | 100005
65 100001 | 100009 | 100007
66 100001 | 100009 | 100008
100 select id, txid_visible_in_snapshot(id, snap)
101 from snapshot_test, generate_series(11, 21) id
103 id | txid_visible_in_snapshot
104 ----+--------------------------
119 select id, txid_visible_in_snapshot(id, snap)
120 from snapshot_test, generate_series(90, 160) id
122 id | txid_visible_in_snapshot
123 -----+--------------------------
197 -- test current values also
198 select txid_current() >= txid_snapshot_xmin(txid_current_snapshot());
204 -- we can't assume current is always less than xmax, however
205 select txid_visible_in_snapshot(txid_current(), txid_current_snapshot());
206 txid_visible_in_snapshot
207 --------------------------
212 select txid_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013';
214 ---------------------------------------------------------------------
215 1000100010001000:1000100010001100:1000100010001012,1000100010001013
218 select txid_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
219 txid_visible_in_snapshot
220 --------------------------
224 select txid_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
225 txid_visible_in_snapshot
226 --------------------------
230 -- test 64bit overflow
231 SELECT txid_snapshot '1:9223372036854775807:3';
233 -------------------------
234 1:9223372036854775807:3
237 SELECT txid_snapshot '1:9223372036854775808:3';
238 ERROR: invalid input syntax for type txid_snapshot: "1:9223372036854775808:3"
239 LINE 1: SELECT txid_snapshot '1:9223372036854775808:3';
241 -- test txid_current_if_assigned
243 SELECT txid_current_if_assigned() IS NULL;
249 SELECT txid_current() \gset
250 SELECT txid_current_if_assigned() IS NOT DISTINCT FROM BIGINT :'txid_current';
257 -- test xid status functions
259 SELECT txid_current() AS committed \gset
262 SELECT txid_current() AS rolledback \gset
265 SELECT txid_current() AS inprogress \gset
266 SELECT txid_status(:committed) AS committed;
272 SELECT txid_status(:rolledback) AS rolledback;
278 SELECT txid_status(:inprogress) AS inprogress;
284 SELECT txid_status(1); -- BootstrapTransactionId is always committed
290 SELECT txid_status(2); -- FrozenTransactionId is always committed
296 SELECT txid_status(3); -- in regress testing FirstNormalTransactionId will always be behind oldestXmin
304 CREATE FUNCTION test_future_xid_status(bigint)
310 PERFORM txid_status($1);
311 RAISE EXCEPTION 'didn''t ERROR at xid in the future as expected';
313 WHEN invalid_parameter_value THEN
314 RAISE NOTICE 'Got expected error for xid in the future';
317 SELECT test_future_xid_status(:inprogress + 10000);
318 NOTICE: Got expected error for xid in the future
319 test_future_xid_status
320 ------------------------