Re: Sequences, txids, and serial order of transactions
От | Alban Hertroys |
---|---|
Тема | Re: Sequences, txids, and serial order of transactions |
Дата | |
Msg-id | 444A41D6-9F91-4B17-8747-9E0AB1C2FF4C@gmail.com обсуждение исходный текст |
Ответ на | Sequences, txids, and serial order of transactions (Christian Ohler <ohler@shift.com>) |
Список | pgsql-general |
> On 12 Jun 2016, at 4:03, Christian Ohler <ohler@shift.com> wrote: > we have a use case similar to auditing packages like pgMemento or Audit Trigger 91plus – we are looking to keep an orderedhistory of certain write transactions. I'm trying to understand the trade-offs between different ways of gettingthat order, i.e., assigning numbers to transactions (ideally strictly monotonic, modulo concurrency). All of ourtransactions are serializable (for now). > (2) the orders produced by txid_current and a sequence can be different (unsurprisingly). (If it was desirable to makethem match, we could probably do so by briefly holding a lock while we call both txid_current and nextval – seems likethis shouldn't limit concurrency too much. Or would it? Is one of them potentially slow?) I'm aware of only 2 cases that those can have a different order: 1. The txid or the sequence wraps 2. The txid of a transaction exists some time already when the sequence's nextval() gets called. A later transaction (highertxid) running in parallel could request a nextval() in between those moments. I think that situation 1 can be caught (the few times it occurs). Situation 2 is probably what bothers you? As long as therequest for nextval() is early in the transaction, a wait-lock shouldn't block other waiting transactions for long. To make sure, I would run some tests comparing running enough parallel transactions calling a sequence's nextval() both withand without the lock. The first of those will also give you some insight in how bad the transaction ordering vs. sequenceordering problem actually is. That is, unless you're perhaps overcomplicating your problem (see my answer to (6)). > (5) Postgres can give us a "high watermark" ("no transactions with IDs below this number are still in-flight") for txid_current(using txid_snapshot_xmin(txid_current_snapshot())), but has no equivalent feature for sequences How would it know whether a sequence number is still in use? For example, I have a process @work where I use a database sequenceto distinguish between batches of data in a user's HTTP session. Nothing of that is in the database, but the sequenceis most certainly in use, across different database sessions. > (6) neither txid_current nor a sequence give us a valid serial order of the transactions That depends on what you consider a transaction for your application. Do you care about the order that data got manipulatedin, or do you care in what order the surrounding database transactions were created? Usually, people only care about the first, for which a sequence should be just fine. The second is usually only relevantfor systems that are closely tied to the database internals, such as replication systems. > (7) given that we can't get a valid serial order, what guarantees can we get from the ordering? I'm not entirely surewhat to look for, but at a minimum, it seems like we want writes that clobber each other to be correctly ordered. Arethey, for both txid_current and for sequences? My guess was "yes" for txids (seems intuitive but just a guess) and "no"for sequences (because https://www.postgresql.org/docs/current/static/functions-sequence.html mentions that sequencesare non-transactional); but for sequences, I couldn't immediately construct a counterexample and am wondering whetherthat's by design. 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 thatthis guarantees ordering writes. Does it? As I understand it, sequences have to be non-transactional to be able to guarantee correct ordering. Calling nextval() will increment the sequence, but does not relate it to the transaction at that point. The select statementthat does the call to nextval() receives the value from the sequence and is part of the transaction. That linksthem together, as long as you don't use that sequence value outside that transaction. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
В списке pgsql-general по дате отправления: