Re: bigint vs txid user confusion

On 17 December 2016 at 00:13, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Dec 15, 2016 at 3:02 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> I really wish we could just change the pg_stat_activity and
>> pg_stat_replication xid fields to be epoch qualified in a 64-bit wide
>> 'fullxid' type, or similar.
>
> I think that approach is worth considering.

I'm worried about how many monitoring tools it'd break.

Arguably most or all will already be broken and not know it, though.
Those that aren't will be using age(xid), which we can support just
fine for the new type too, so they won't notice.

Ideally I'd just like to widen 'xid' to 64-bits. This would upset
clients that use binary mode and "know" it's a 32-bit type on the
wire, though, so I'm not sure it's a good idea even though it'd be
nicer in pretty much every other way.

So the idea then is to add a new bigxid type, a 64-bit wide
epoch-extended xid, and replace _all_ appearances of 'xid' with it in
catalogs, views, etc, such that 'xid' is entirely deprecated.

Rather than convert 64-bit extended XIDs to 32-bit for internal
comparisons/functions/operators we'll just epoch-extend our 32-bit
xids, getting rid of the need to handle any sort of "too old" concept
in most cases.

The return type of txid_current() should probably change to the new
bitxid type. This'll upset apps that expect to do maths on it since
the new bigxid type won't have many operators, but since most (all?)
of that maths will be wrong I don't think that's a bad thing. Banging
in a ::bigint will quickfix so adaptation is easy, and it'll highlight
incorrect uses (many) of the call. The type is on-wire compatible with
the current bigint return type until you hit epoch 2^31 in which case
you have bigger things to worry about, like pending epoch wraparound.

HOWEVER, if we're going to really remove 'xid' from user view, it
should vanish from pg_database and pg_class too. That's a LOT more
intrusive, and widens pg_class by 4 bytes per row for minimal gain. No
entry there can ever have an epoch older than the current epoch - 1,
and only then the part that's after the wraparound threshold. pg_class
is a raw relation so we can't transform what the user sees via a view
or function.

Tools look at pg_class.relfrozenxid and pg_databse.datfrozenxid more
than probably anything else, so making changes that ignores them is
pretty pointless.

Everything else looks easy and minimally intrusive, but I'm not sure
there's a sensible answer to pg_class.relfrozenxid.

test=> select table_name, column_name from information_schema.columns
where data_type = 'xid';
table_name | column_name
----------------------+---------------
pg_class | relfrozenxid
pg_class | relminmxid
pg_database | datfrozenxid
pg_database | datminmxid
pg_locks | transactionid
pg_prepared_xacts | transaction
pg_stat_activity | backend_xid
pg_stat_activity | backend_xmin
pg_stat_replication | backend_xmin
pg_replication_slots | xmin
pg_replication_slots | catalog_xmin
(11 rows)

test=> SELECT proname FROM pg_proc WHERE prorettype = 'xid'::regtype
OR 'xid'::regtype = ANY (proargtypes);
proname
--------------------------
xidin
xidout
xideq
age
mxid_age
xideqint4
pg_get_multixact_members
pg_xact_commit_timestamp
xidrecv
xidsend
(10 rows)

test=> SELECT oprname FROM pg_operator WHERE 'xid'::regtype IN
(oprleft, oprright, oprresult);
oprname
---------
=
=
(2 rows)

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