Re: Sequences, txids, and serial order of transactions

Christian Ohler <ohler(at)shift(dot)com> writes:
> we have a use case similar to auditing packages like pgMemento or Audit
> Trigger 91plus we are looking to keep an ordered history of certain write
> transactions. I'm trying to understand the trade-offs between different
> ways of getting that order, i.e., assigning numbers to transactions
> (ideally strictly monotonic, modulo concurrency). All of our transactions
> are serializable (for now).
> ...

> (4) behaviors like
> http://permalink.gmane.org/gmane.comp.db.postgresql.bugs/35636 make me
> think that monotonicity of txid_current is not something we should bet on

Not following why you think bugs might break txids but not sequences.

> (7) given that we can't get a valid serial order, what guarantees can we
> get from the ordering? I'm not entirely sure what to look for, but at a
> minimum, it seems like we want writes that clobber each other to be
> correctly ordered.

Um ... if you're running the transactions in serializable mode, there
aren't going to *be* any "writes that clobber each other". Maybe you
should clarify what you're hoping to accomplish exactly.

> Specifically, it seems that Postgres acquires the
> snapshot for the transaction (if it hasn't already) when I call nextval(),
> and as long as the snapshot is acquired before the sequence is incremented,
> I suspect that this guarantees ordering writes. Does it?

If you're doing "BEGIN; SELECT nextval(); ..." in each transaction, then
yes, the SELECT would acquire a snapshot before calling nextval, but no,
that doesn't mean anything with respect to the apparent commit order of
the transactions. I think you are confusing snapshots with XIDs.

A look at the nextval() source code says that, if the sequence doesn't
have caching enabled and is WAL-logged, and we don't already have an XID,
then we acquire one inside the buffer lock on the sequence's page. This
would explain why you are seeing txid_current and the sequence value as
always advancing in lockstep. It doesn't seem like something to rely on
though; somebody might decide to move that out of the buffer critical
section to improve concurrency. In any case, neither txid_current nor the
sequence value will provide any reliable guide to the apparent commit
order of concurrently-running transactions.

regards, tom lane