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