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
