src/test/regress/expected/txid.out - master - githubmirror/postgres

src/test/regress/expected/txid.out
327 строк8 KB
Thomas Munro
Introduce xid8-based functions to replace txid_XXX.
07 апр 2020, 03:04
07 апр 2020, 03:044c04be9
-- 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;
 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;