0

AFAIK txid_current returns uint 64 value (source).
However, the biggest int column type is bigint which is signed (docs).
From what I understand, NUMERIC type is slow and should be used "for storing monetary amounts and other quantities where exactness is required" (docs).

Should I store the txid_current value as text?

I need to perform mainly greater than queries on this field:

select * from t where fxid > 438298432

What would be the most efficient (from search speed POV) for full-transaction-id (unsigned integer 64)?

4
  • 1
    You could use a bigint and store it with an offset. If you transform your search parameters it should be possible to utilize indexes. Just a thought Jun 20, 2020 at 15:39
  • offset = save the first 32 bits in one column and the other 32 bits in another column?
    – Michael
    Jun 20, 2020 at 17:48
  • 1
    No, I was thinking in terms of storing 0 as - (2^32) or whatever number that may be. Searching for a specific transaktion would be: SELECT... FROM T WHERE txid =? - n, where n is the offset Jun 20, 2020 at 17:54
  • Oh I see, nice idea!
    – Michael
    Jun 20, 2020 at 17:59

2 Answers 2

1

I'm suspicious about your motivations, but I'd say you should either use bigint (which is the data type xid_current returns) or use xid (which is the internal data type for xid's). By way of example, see the following:

pagila=# create table xidvals as select * from txid_current();
SELECT 1
pagila=# \d xidvals
                 Table "public.xidvals"
    Column    |  Type  | Collation | Nullable | Default
--------------+--------+-----------+----------+---------
 txid_current | bigint |           |          |

pagila=# select xmin,xmax,txid_current from xidvals;
  xmin  | xmax | txid_current
--------+------+--------------
 358165 |    0 |       358165
(1 row)

pagila=# select attname, atttypid::regtype from pg_attribute where attrelid = 'xidvals'::regclass and attname in ('xmax','xmin','txid_current');
   attname    | atttypid
--------------+----------
 xmax         | xid
 xmin         | xid
 txid_current | bigint
(3 rows)
1
1

On pg v10, The txid value is limted to 0x7FFFFFFFFFFFFFFF (9223372036854775807) which is fit in bigint.
source code (v10)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.