Is there a reliable way to determine the serial order in which Postgres transactions were committed without logical decoding? From what I understand
pg_xact_commit_timestamp can’t necessarily be relied upon for this (and I’m not even sure I’ll be able to turn this on in this environment)? I don’t need to know the exact times transactions were committed - I just care about the order.
I should probably state the problem as perhaps there’s a better way to go about this. I have a series of tables which I am using to generate quotes. For any one of those quotes I need to be able to go back and prove that the correct quote was generated. Therefore for any tables that informed this calculation I also need to be able to reproduce their state at the time the point was generated. As this is derived data it makes the problem a bit harder.
My initial thought was I could generate these quotes using a
READ ONLY transaction and record the
txid_current_snapshot() so I know which transactions were still in-flight and therefore which snapshots to rule out from my history (I need to do this because with the current audit logs the timestamps are before the transaction was committed and therefore can not be used reliably to determine order). If there’s no way to reliably determine transaction ordering this solution falls apart.
This is what I’m using for my audit history: https://wiki.postgresql.org/wiki/Audit_trigger_91plus
Streaming the WAL via logical decoding to some upstream consumer is the eventual goal, but it’s not something I have the time to do at the moment.