Search Postgresql Archives

Re: Sequences, txids, and serial order of transactions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Christian Ohler <ohler@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux