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