Regression tests fail once XID counter exceeds 2 billion

While investigating bug #6291 I was somewhat surprised to discover
$SUBJECT. The cause turns out to be this kluge in alter_table.sql:

select virtualtransaction
from pg_locks
where transactionid = txid_current()::integer

which of course starts to fail with "integer out of range" as soon as
txid_current() gets past 2^31. Right now, since there is no cast
between xid and any integer type, and no comparison operator except the
dubious xideqint4 one, the only way we could fix this is something
like

where transactionid::text = (txid_current() % (2^32))::text

which is surely pretty ugly. Is it worth doing something less ugly?
I'm not sure if there are any other use-cases for this type of
comparison, but if there are, seems like it would be sensible to invent
a function along the lines of

txid_from_xid(xid) returns bigint

that plasters on the appropriate epoch value for an
assumed-to-be-current-or-recent xid, and returns something that squares
with the txid_snapshot functions. Then the test could be coded without
kluges as

where txid_from_xid(transactionid) = txid_current()

Thoughts?

regards, tom lane

On Sun, Nov 13, 2011 at 6:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> While investigating bug #6291 I was somewhat surprised to discover
> $SUBJECT.  The cause turns out to be this kluge in alter_table.sql:
>
>        select virtualtransaction
>        from pg_locks
>        where transactionid = txid_current()::integer
>
> which of course starts to fail with "integer out of range" as soon as
> txid_current() gets past 2^31.  Right now, since there is no cast
> between xid and any integer type, and no comparison operator except the
> dubious xideqint4 one, the only way we could fix this is something
> like
>
>        where transactionid::text = (txid_current() % (2^32))::text
>
> which is surely pretty ugly.  Is it worth doing something less ugly?
> I'm not sure if there are any other use-cases for this type of
> comparison, but if there are, seems like it would be sensible to invent
> a function along the lines of
>
>        txid_from_xid(xid) returns bigint
>
> that plasters on the appropriate epoch value for an
> assumed-to-be-current-or-recent xid, and returns something that squares
> with the txid_snapshot functions.  Then the test could be coded without
> kluges as
>
>        where txid_from_xid(transactionid) = txid_current()
>
> Thoughts?

Well, the mod-2^32 arithmetic doesn't bother me, but if you're feeling
motivated to invent txid_from_xid() I think that would be fine, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sun, Nov 13, 2011 at 11:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> While investigating bug #6291 I was somewhat surprised to discover
> $SUBJECT.  The cause turns out to be this kluge in alter_table.sql:
>
>        select virtualtransaction
>        from pg_locks
>        where transactionid = txid_current()::integer

...

> that plasters on the appropriate epoch value for an
> assumed-to-be-current-or-recent xid, and returns something that squares
> with the txid_snapshot functions.  Then the test could be coded without
> kluges as

That fixes the test, but it doesn't fix the unreasonability of this situation.

We need a function called transactionid_current() so a normal user can write

select virtualtransaction
from pg_locks
where transactionid = transactionid_current()

and have it "just work".

We need a function whose behaviour matches xid columns in pg_locks and
elsewhere and that doesn't need to have anything to do with txid
datatype.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> We need a function called transactionid_current() so a normal user can write

> select virtualtransaction
> from pg_locks
> where transactionid = transactionid_current()

> and have it "just work".

That would solve that one specific use-case. The reason I suggested
txid_from_xid is that it could also be used to compare XIDs seen in
tuples to members of a txid_snapshot, which is not possible now.

regards, tom lane

I wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> We need a function called transactionid_current() so a normal user can write

>> select virtualtransaction
>> from pg_locks
>> where transactionid = transactionid_current()

>> and have it "just work".

> That would solve that one specific use-case. The reason I suggested
> txid_from_xid is that it could also be used to compare XIDs seen in
> tuples to members of a txid_snapshot, which is not possible now.

BTW, a pgsql-general question just now made me realize that
txid_from_xid() could have another use-case too. Right now, there are
no inequality comparisons on XIDs, which is necessary because XIDs in
themselves don't have a total order. However, you could

ORDER BY txid_from_xid(xmin)

and it would work, ie, give you rows in their XID order. This could be
useful for finding the latest-modified rows in a table, modulo the fact
that it would be ordering by transaction start time not commit time.

regards, tom lane

On Wed, Nov 16, 2011 at 07:08:27PM -0500, Tom Lane wrote:
> I wrote:
> > Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> >> We need a function called transactionid_current() so a normal user can write
>
> >> select virtualtransaction
> >> from pg_locks
> >> where transactionid = transactionid_current()
>
> >> and have it "just work".
>
> > That would solve that one specific use-case. The reason I suggested
> > txid_from_xid is that it could also be used to compare XIDs seen in
> > tuples to members of a txid_snapshot, which is not possible now.
>
> BTW, a pgsql-general question just now made me realize that
> txid_from_xid() could have another use-case too. Right now, there are
> no inequality comparisons on XIDs, which is necessary because XIDs in
> themselves don't have a total order. However, you could
>
> ORDER BY txid_from_xid(xmin)
>
> and it would work, ie, give you rows in their XID order. This could be
> useful for finding the latest-modified rows in a table, modulo the fact
> that it would be ordering by transaction start time not commit time.

Added to TODO:

Add function to allow easier transaction id comparisons

http://archives.postgresql.org/pgsql-hackers/2011-11/msg00786.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

> Added to TODO:
>
> Add function to allow easier transaction id comparisons
>
> http://archives.postgresql.org/pgsql-hackers/2011-11/msg00786.php
>
Did this ever happen? Or did it fall through the cracks?