| -- txid_snapshot data type and related functions |
| -- Note: these are backward-compatibility functions and types, and have been |
| -- replaced by new xid8-based variants. See xid.sql. The txid variants will |
| -- be removed in a future release. |
| -- i/o |
| select '12:13:'::txid_snapshot; |
| txid_snapshot |
| --------------- |
| 12:13: |
| (1 row) |
| |
| select '12:18:14,16'::txid_snapshot; |
| txid_snapshot |
| --------------- |
| 12:18:14,16 |
| (1 row) |
| |
| select '12:16:14,14'::txid_snapshot; |
| txid_snapshot |
| --------------- |
| 12:16:14 |
| (1 row) |
| |
| -- errors |
| select '31:12:'::txid_snapshot; |
| ERROR: invalid input syntax for type pg_snapshot: "31:12:" |
| LINE 1: select '31:12:'::txid_snapshot; |
| ^ |
| select '0:1:'::txid_snapshot; |
| ERROR: invalid input syntax for type pg_snapshot: "0:1:" |
| LINE 1: select '0:1:'::txid_snapshot; |
| ^ |
| select '12:13:0'::txid_snapshot; |
| ERROR: invalid input syntax for type pg_snapshot: "12:13:0" |
| LINE 1: select '12:13:0'::txid_snapshot; |
| ^ |
| select '12:16:14,13'::txid_snapshot; |
| ERROR: invalid input syntax for type pg_snapshot: "12:16:14,13" |
| LINE 1: select '12:16:14,13'::txid_snapshot; |
| ^ |
| create temp table snapshot_test ( |
| nr integer, |
| snap txid_snapshot |
| ); |
| insert into snapshot_test values (1, '12:13:'); |
| insert into snapshot_test values (2, '12:20:13,15,18'); |
| insert into snapshot_test values (3, '100001:100009:100005,100007,100008'); |
| 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'); |
| select snap from snapshot_test order by nr; |
| snap |
| ------------------------------------------------------------------------------------------------------------------------------------- |
| 12:13: |
| 12:20:13,15,18 |
| 100001:100009:100005,100007,100008 |
| 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 |
| (4 rows) |
| |
| select txid_snapshot_xmin(snap), |
| txid_snapshot_xmax(snap), |
| txid_snapshot_xip(snap) |
| from snapshot_test order by nr, 1, 2, 3; |
| txid_snapshot_xmin | txid_snapshot_xmax | txid_snapshot_xip |
| --------------------+--------------------+------------------- |
| 12 | 20 | 13 |
| 12 | 20 | 15 |
| 12 | 20 | 18 |
| 100001 | 100009 | 100005 |
| 100001 | 100009 | 100007 |
| 100001 | 100009 | 100008 |
| 100 | 150 | 101 |
| 100 | 150 | 102 |
| 100 | 150 | 103 |
| 100 | 150 | 104 |
| 100 | 150 | 105 |
| 100 | 150 | 106 |
| 100 | 150 | 107 |
| 100 | 150 | 108 |
| 100 | 150 | 109 |
| 100 | 150 | 110 |
| 100 | 150 | 111 |
| 100 | 150 | 112 |
| 100 | 150 | 113 |
| 100 | 150 | 114 |
| 100 | 150 | 115 |
| 100 | 150 | 116 |
| 100 | 150 | 117 |
| 100 | 150 | 118 |
| 100 | 150 | 119 |
| 100 | 150 | 120 |
| 100 | 150 | 121 |
| 100 | 150 | 122 |
| 100 | 150 | 123 |
| 100 | 150 | 124 |
| 100 | 150 | 125 |
| 100 | 150 | 126 |
| 100 | 150 | 127 |
| 100 | 150 | 128 |
| 100 | 150 | 129 |
| 100 | 150 | 130 |
| 100 | 150 | 131 |
| (37 rows) |
| |
| select id, txid_visible_in_snapshot(id, snap) |
| from snapshot_test, generate_series(11, 21) id |
| where nr = 2; |
| id | txid_visible_in_snapshot |
| ----+-------------------------- |
| 11 | t |
| 12 | t |
| 13 | f |
| 14 | t |
| 15 | f |
| 16 | t |
| 17 | t |
| 18 | f |
| 19 | t |
| 20 | f |
| 21 | f |
| (11 rows) |
| |
| -- test bsearch |
| select id, txid_visible_in_snapshot(id, snap) |
| from snapshot_test, generate_series(90, 160) id |
| where nr = 4; |
| id | txid_visible_in_snapshot |
| -----+-------------------------- |
| 90 | t |
| 91 | t |
| 92 | t |
| 93 | t |
| 94 | t |
| 95 | t |
| 96 | t |
| 97 | t |
| 98 | t |
| 99 | t |
| 100 | t |
| 101 | f |
| 102 | f |
| 103 | f |
| 104 | f |
| 105 | f |
| 106 | f |
| 107 | f |
| 108 | f |
| 109 | f |
| 110 | f |
| 111 | f |
| 112 | f |
| 113 | f |
| 114 | f |
| 115 | f |
| 116 | f |
| 117 | f |
| 118 | f |
| 119 | f |
| 120 | f |
| 121 | f |
| 122 | f |
| 123 | f |
| 124 | f |
| 125 | f |
| 126 | f |
| 127 | f |
| 128 | f |
| 129 | f |
| 130 | f |
| 131 | f |
| 132 | t |
| 133 | t |
| 134 | t |
| 135 | t |
| 136 | t |
| 137 | t |
| 138 | t |
| 139 | t |
| 140 | t |
| 141 | t |
| 142 | t |
| 143 | t |
| 144 | t |
| 145 | t |
| 146 | t |
| 147 | t |
| 148 | t |
| 149 | t |
| 150 | f |
| 151 | f |
| 152 | f |
| 153 | f |
| 154 | f |
| 155 | f |
| 156 | f |
| 157 | f |
| 158 | f |
| 159 | f |
| 160 | f |
| (71 rows) |
| |
| -- test current values also |
| select txid_current() >= txid_snapshot_xmin(txid_current_snapshot()); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- we can't assume current is always less than xmax, however |
| select txid_visible_in_snapshot(txid_current(), txid_current_snapshot()); |
| txid_visible_in_snapshot |
| -------------------------- |
| f |
| (1 row) |
| |
| -- test 64bitness |
| select txid_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013'; |
| txid_snapshot |
| --------------------------------------------------------------------- |
| 1000100010001000:1000100010001100:1000100010001012,1000100010001013 |
| (1 row) |
| |
| select txid_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013'); |
| txid_visible_in_snapshot |
| -------------------------- |
| f |
| (1 row) |
| |
| select txid_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013'); |
| txid_visible_in_snapshot |
| -------------------------- |
| t |
| (1 row) |
| |
| -- test 64bit overflow |
| SELECT txid_snapshot '1:9223372036854775807:3'; |
| txid_snapshot |
| ------------------------- |
| 1:9223372036854775807:3 |
| (1 row) |
| |
| SELECT txid_snapshot '1:9223372036854775808:3'; |
| ERROR: invalid input syntax for type pg_snapshot: "1:9223372036854775808:3" |
| LINE 1: SELECT txid_snapshot '1:9223372036854775808:3'; |
| ^ |
| -- test txid_current_if_assigned |
| BEGIN; |
| SELECT txid_current_if_assigned() IS NULL; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| SELECT txid_current() \gset |
| SELECT txid_current_if_assigned() IS NOT DISTINCT FROM BIGINT :'txid_current'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| COMMIT; |
| -- test xid status functions |
| BEGIN; |
| SELECT txid_current() AS committed \gset |
| COMMIT; |
| BEGIN; |
| SELECT txid_current() AS rolledback \gset |
| ROLLBACK; |
| BEGIN; |
| SELECT txid_current() AS inprogress \gset |
| SELECT txid_status(:committed) AS committed; |
| committed |
| ----------- |
| committed |
| (1 row) |
| |
| SELECT txid_status(:rolledback) AS rolledback; |
| rolledback |
| ------------ |
| aborted |
| (1 row) |
| |
| SELECT txid_status(:inprogress) AS inprogress; |
| inprogress |
| ------------- |
| in progress |
| (1 row) |
| |
| SELECT txid_status(1); -- BootstrapTransactionId is always committed |
| txid_status |
| ------------- |
| committed |
| (1 row) |
| |
| SELECT txid_status(2); -- FrozenTransactionId is always committed |
| txid_status |
| ------------- |
| committed |
| (1 row) |
| |
| SELECT txid_status(3); -- in regress testing FirstNormalTransactionId will always be behind oldestXmin |
| txid_status |
| ------------- |
| |
| (1 row) |
| |
| COMMIT; |
| BEGIN; |
| CREATE FUNCTION test_future_xid_status(bigint) |
| RETURNS void |
| LANGUAGE plpgsql |
| AS |
| $$ |
| BEGIN |
| PERFORM txid_status($1); |
| RAISE EXCEPTION 'didn''t ERROR at xid in the future as expected'; |
| EXCEPTION |
| WHEN invalid_parameter_value THEN |
| RAISE NOTICE 'Got expected error for xid in the future'; |
| END; |
| $$; |
| SELECT test_future_xid_status(:inprogress + 10000); |
| NOTICE: Got expected error for xid in the future |
| test_future_xid_status |
| ------------------------ |
| |
| (1 row) |
| |
| ROLLBACK; |
