path: root/src/test/regress/expected/xid.out

-- xid and xid8
-- values in range, in octal, decimal, hex
select '010'::xid,
       '42'::xid,
       '0xffffffff'::xid,
       '-1'::xid,
	   '010'::xid8,
	   '42'::xid8,
	   '0xffffffffffffffff'::xid8,
	   '-1'::xid8;
 xid | xid |    xid     |    xid     | xid8 | xid8 |         xid8         |         xid8         
-----+-----+------------+------------+------+------+----------------------+----------------------
   8 |  42 | 4294967295 | 4294967295 |    8 |   42 | 18446744073709551615 | 18446744073709551615
(1 row)

-- garbage values are not yet rejected (perhaps they should be)
select ''::xid;
 xid 
-----
   0
(1 row)

select 'asdf'::xid;
 xid 
-----
   0
(1 row)

select ''::xid8;
 xid8 
------
    0
(1 row)

select 'asdf'::xid8;
 xid8 
------
    0
(1 row)

-- equality
select '1'::xid = '1'::xid;
 ?column? 
----------
 t
(1 row)

select '1'::xid != '1'::xid;
 ?column? 
----------
 f
(1 row)

select '1'::xid8 = '1'::xid8;
 ?column? 
----------
 t
(1 row)

select '1'::xid8 != '1'::xid8;
 ?column? 
----------
 f
(1 row)

-- conversion
select '1'::xid = '1'::xid8::xid;
 ?column? 
----------
 t
(1 row)

select '1'::xid != '1'::xid8::xid;
 ?column? 
----------
 f
(1 row)

-- we don't want relational operators for xid, due to use of modular arithmetic
select '1'::xid < '2'::xid;
ERROR:  operator does not exist: xid < xid
LINE 1: select '1'::xid < '2'::xid;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
select '1'::xid <= '2'::xid;
ERROR:  operator does not exist: xid <= xid
LINE 1: select '1'::xid <= '2'::xid;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
select '1'::xid > '2'::xid;
ERROR:  operator does not exist: xid > xid
LINE 1: select '1'::xid > '2'::xid;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
select '1'::xid >= '2'::xid;
ERROR:  operator does not exist: xid >= xid
LINE 1: select '1'::xid >= '2'::xid;
                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
-- we want them for xid8 though
select '1'::xid8 < '2'::xid8, '2'::xid8 < '2'::xid8, '2'::xid8 < '1'::xid8;
 ?column? | ?column? | ?column? 
----------+----------+----------
 t        | f        | f
(1 row)

select '1'::xid8 <= '2'::xid8, '2'::xid8 <= '2'::xid8, '2'::xid8 <= '1'::xid8;
 ?column? | ?column? | ?column? 
----------+----------+----------
 t        | t        | f
(1 row)

select '1'::xid8 > '2'::xid8, '2'::xid8 > '2'::xid8, '2'::xid8 > '1'::xid8;
 ?column? | ?column? | ?column? 
----------+----------+----------
 f        | f        | t
(1 row)

select '1'::xid8 >= '2'::xid8, '2'::xid8 >= '2'::xid8, '2'::xid8 >= '1'::xid8;
 ?column? | ?column? | ?column? 
----------+----------+----------
 f        | t        | t
(1 row)

-- we also have a 3way compare for btrees
select xid8cmp('1', '2'), xid8cmp('2', '2'), xid8cmp('2', '1');
 xid8cmp | xid8cmp | xid8cmp 
---------+---------+---------
      -1 |       0 |       1
(1 row)

-- xid8 has btree and hash opclasses
create table xid8_t1 (x xid8);
create index on xid8_t1 using btree(x);
create index on xid8_t1 using hash(x);
drop table xid8_t1;
-- pg_snapshot data type and related functions
-- Note: another set of tests similar to this exists in txid.sql, for a limited
-- time (the relevant functions share C code)
-- i/o
select '12:13:'::pg_snapshot;
 pg_snapshot 
-------------
 12:13:
(1 row)

select '12:18:14,16'::pg_snapshot;
 pg_snapshot 
-------------
 12:18:14,16
(1 row)

select '12:16:14,14'::pg_snapshot;
 pg_snapshot 
-------------
 12:16:14
(1 row)

-- errors
select '31:12:'::pg_snapshot;
ERROR:  invalid input syntax for type pg_snapshot: "31:12:"
LINE 1: select '31:12:'::pg_snapshot;
               ^
select '0:1:'::pg_snapshot;
ERROR:  invalid input syntax for type pg_snapshot: "0:1:"
LINE 1: select '0:1:'::pg_snapshot;
               ^
select '12:13:0'::pg_snapshot;
ERROR:  invalid input syntax for type pg_snapshot: "12:13:0"
LINE 1: select '12:13:0'::pg_snapshot;
               ^
select '12:16:14,13'::pg_snapshot;
ERROR:  invalid input syntax for type pg_snapshot: "12:16:14,13"
LINE 1: select '12:16:14,13'::pg_snapshot;
               ^
create temp table snapshot_test (
	nr	integer,
	snap	pg_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  pg_snapshot_xmin(snap),
	pg_snapshot_xmax(snap),
	pg_snapshot_xip(snap)
from snapshot_test order by nr;
 pg_snapshot_xmin | pg_snapshot_xmax | pg_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, pg_visible_in_snapshot(id::text::xid8, snap)
from snapshot_test, generate_series(11, 21) id
where nr = 2;
 id | pg_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, pg_visible_in_snapshot(id::text::xid8, snap)
from snapshot_test, generate_series(90, 160) id
where nr = 4;
 id  | pg_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 pg_current_xact_id() >= pg_snapshot_xmin(pg_current_snapshot());
 ?column? 
----------
 t
(1 row)

-- we can't assume current is always less than xmax, however
select pg_visible_in_snapshot(pg_current_xact_id(), pg_current_snapshot());
 pg_visible_in_snapshot 
------------------------
 f
(1 row)

-- test 64bitness
select pg_snapshot '1000100010001000:1000100010001100:1000100010001012,1000100010001013';
                             pg_snapshot                             
---------------------------------------------------------------------
 1000100010001000:1000100010001100:1000100010001012,1000100010001013
(1 row)

select pg_visible_in_snapshot('1000100010001012', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
 pg_visible_in_snapshot 
------------------------
 f
(1 row)

select pg_visible_in_snapshot('1000100010001015', '1000100010001000:1000100010001100:1000100010001012,1000100010001013');
 pg_visible_in_snapshot 
------------------------
 t
(1 row)

-- test 64bit overflow
SELECT pg_snapshot '1:9223372036854775807:3';
       pg_snapshot       
-------------------------
 1:9223372036854775807:3
(1 row)

SELECT pg_snapshot '1:9223372036854775808:3';
ERROR:  invalid input syntax for type pg_snapshot: "1:9223372036854775808:3"
LINE 1: SELECT pg_snapshot '1:9223372036854775808:3';
                           ^
-- test pg_current_xact_id_if_assigned
BEGIN;
SELECT pg_current_xact_id_if_assigned() IS NULL;
 ?column? 
----------
 t
(1 row)

SELECT pg_current_xact_id() \gset
SELECT pg_current_xact_id_if_assigned() IS NOT DISTINCT FROM xid8 :'pg_current_xact_id';
 ?column? 
----------
 t
(1 row)

COMMIT;
-- test xid status functions
BEGIN;
SELECT pg_current_xact_id() AS committed \gset
COMMIT;
BEGIN;
SELECT pg_current_xact_id() AS rolledback \gset
ROLLBACK;
BEGIN;
SELECT pg_current_xact_id() AS inprogress \gset
SELECT pg_xact_status(:committed::text::xid8) AS committed;
 committed 
-----------
 committed
(1 row)

SELECT pg_xact_status(:rolledback::text::xid8) AS rolledback;
 rolledback 
------------
 aborted
(1 row)

SELECT pg_xact_status(:inprogress::text::xid8) AS inprogress;
 inprogress  
-------------
 in progress
(1 row)

SELECT pg_xact_status('1'::xid8); -- BootstrapTransactionId is always committed
 pg_xact_status 
----------------
 committed
(1 row)

SELECT pg_xact_status('2'::xid8); -- FrozenTransactionId is always committed
 pg_xact_status 
----------------
 committed
(1 row)

SELECT pg_xact_status('3'::xid8); -- in regress testing FirstNormalTransactionId will always be behind oldestXmin
 pg_xact_status 
----------------
 
(1 row)

COMMIT;
BEGIN;
CREATE FUNCTION test_future_xid_status(xid8)
RETURNS void
LANGUAGE plpgsql
AS
$$
BEGIN
  PERFORM pg_xact_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)::text::xid8);
NOTICE:  Got expected error for xid in the future
 test_future_xid_status 
------------------------
 
(1 row)

ROLLBACK;