1-- txid_snapshot data type and related functions 2-- Note: these are backward-compatibility functions and types, and have been 3-- replaced by new xid8-based variants. See xid.sql. The txid variants will 4-- be removed in a future release. 5 6-- i/o 7select '12:13:'::txid_snapshot; 8select '12:18:14,16'::txid_snapshot; 9select '12:16:14,14'::txid_snapshot; 10 11-- errors 12select '31:12:'::txid_snapshot; 13select '0:1:'::txid_snapshot; 14select '12:13:0'::txid_snapshot; 15select '12:16:14,13'::txid_snapshot; 16 17create temp table snapshot_test ( 18 nr integer, 19 snap txid_snapshot 20); 21 22insert into snapshot_test values (1, '12:13:'); 23insert into snapshot_test values (2, '12:20:13,15,18'); 24insert into snapshot_test values (3, '100001:100009:100005,100007,100008'); 25insert 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'); 26select snap from snapshot_test order by nr; 27 28select txid_snapshot_xmin(snap), 29 txid_snapshot_xmax(snap), 30 txid_snapshot_xip(snap) 31from snapshot_test order by nr; 32 33select id, txid_visible_in_snapshot(id, snap) 34from snapshot_test, generate_series(11, 21) id 35where nr = 2; 36 37-- test bsearch 38select id, txid_visible_in_snapshot(id, snap) 39from snapshot_test, generate_series(90, 160) id 40where nr = 4; 41 42-- test current values also 43select txid_current() >= txid_snapshot_xmin(txid_current_snapshot()); 44 45-- we can't assume current is always less than xmax, however 46 47select txid_visible_in_snapshot(txid_current(), txid_current_snapshot()); 48 49-- test 64bitness 50 51select txid_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013'; 52select txid_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013'); 53select txid_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013'); 54 55-- test 64bit overflow 56SELECT txid_snapshot '1:9223372036854775807:3'; 57SELECT txid_snapshot '1:9223372036854775808:3'; 58 59-- test txid_current_if_assigned 60BEGIN; 61SELECT txid_current_if_assigned() IS NULL; 62SELECT txid_current() \gset 63SELECT txid_current_if_assigned() IS NOT DISTINCT FROM BIGINT :'txid_current'; 64COMMIT; 65 66-- test xid status functions 67BEGIN; 68SELECT txid_current() AS committed \gset 69COMMIT; 70 71BEGIN; 72SELECT txid_current() AS rolledback \gset 73ROLLBACK; 74 75BEGIN; 76SELECT txid_current() AS inprogress \gset 77 78SELECT txid_status(:committed) AS committed; 79SELECT txid_status(:rolledback) AS rolledback; 80SELECT txid_status(:inprogress) AS inprogress; 81SELECT txid_status(1); -- BootstrapTransactionId is always committed 82SELECT txid_status(2); -- FrozenTransactionId is always committed 83SELECT txid_status(3); -- in regress testing FirstNormalTransactionId will always be behind oldestXmin 84 85COMMIT; 86 87BEGIN; 88CREATE FUNCTION test_future_xid_status(bigint) 89RETURNS void 90LANGUAGE plpgsql 91AS 92$$ 93BEGIN 94 PERFORM txid_status($1); 95 RAISE EXCEPTION 'didn''t ERROR at xid in the future as expected'; 96EXCEPTION 97 WHEN invalid_parameter_value THEN 98 RAISE NOTICE 'Got expected error for xid in the future'; 99END; 100$$; 101SELECT test_future_xid_status(:inprogress + 10000); 102ROLLBACK; 103