Re: Sequences, txids, and serial order of transactions

On Sat, Jun 11, 2016 at 9:03 PM, Christian Ohler <ohler(at)shift(dot)com> wrote:

> 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).

The guarantee that serializable transactions provide is that for
any group of concurrent serializable transactions which
successfully commit, there is some serial (one-at-a-time) order in
which they could have been run which would provide the same
results. Note that in PostgreSQL that order is not necessarily
commit order. So the first question is whether you want the order
of the numbers to match the apparent order of execution of the
serializable transactions which committed or the commit order.
Those almost certainly won't always be the same.

If you are satisfied with the commit order, there is a way to do
that with minimal loss of concurrency. As the very last thing
before commit, take out an exclusive transactional advisory lock
(pg_advisory_xact_lock):

https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS-TABLE

Under cover of that lock, assign the number. You may need to write
some custom code for assigning that across multiple backends with
the right characteristics (e.g., the database may need to make a
request of some external service for the number). There is some
actual serialization of this small bit at the end of the
transaction, but if you're careful it can be a very small window of
time.

If you want the numbers to be assigned in the apparent order of
execution of the serializable transactions, I'm afraid that I don't
know of any good solution for that right now. There has been some
occasional talk of providing a way to read the AOoE, but nothing
has come of it so far.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company