On Sat, Jun 11, 2016 at 9:03 PM, Christian Ohler <ohler@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
