Re: 32/64-bit transaction IDs?

On Friday March 21 2003 11:12, Ed L. wrote:
>
> Using the transaction ID for ordering seems problematic given the
> variability of transaction lifetimes, not to mention the 32-bit issue.  I
> wonder if it'd be advisable to make WAL data available in a (system?)
> table, maybe mapping the transaction ID to the WAL record number?  Just
> looking for some way to make the true commit order visible to a SQL query
> in order to leverage existing replication code ...

This is admittedly a half-baked idea from someone with little knowledge of
the pg code, but more specifically, I'm imagining a system table that looks
like this (I know this is not how system tables are specified):

    create table pg_xlog (
        xid        int4 unique not null,
        recno    int8 unique not null,
    );
    -- recno = (XLogRecPtr.xlogid) << 32) + XLogRecPtr.xrecoff

This would map transaction IDs to WAL log record numbers.  It seems
straight-forward to get the right data into this table.  But of course,
this table would get a gazillion inserts, and the appropriate logic for
clearing/truncating and the potential performance and memory impacts are
unclear to me.  Still, it does have the appeal of allowing a combination of
a trigger and SQL to reliably determine the transaction order, which would
seem to allow asyncronous trigger-based replication schemes to get the
right replay order.

Any other ideas as to how to get the guaranteed correct transaction order
via triggers?

Ed