Exposing the Xact commit order to the user
in [PgSql]
|
Prev: [HACKERS] Exposing the Xact commit order to the user
Next: [HACKERS] Synchronization levels in SR
From: Robert Haas on 24 May 2010 17:24 On Mon, May 24, 2010 at 4:03 PM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov> wrote: > Robert Haas wrote: > >> It's a tool for replication solutions to use. > > I was thrown by the original post referencing "data warehousing". > For replication I definitely see that it would be good to provide > some facility to grab a coherent snapshot out of the transaction > stream, but I'm still not clear on a use case where other solutions > aren't better. If you want a *particular* past snapshot, something > akin to the transactional caching that Dan Ports mentioned seems > best. If you just want a coherent snapshot like snapshot isolation, > the current mechanisms seem to work (unless I'm missing something?). > If you want solid data integrity querying the most recent replicated > data, the proposal I posted earlier in the thread is the best I can > see, so far. Well, AIUI, what you're really trying to do is derive the delta between an old snapshot and a newer snapshot. >> Can you give an example and explain how your proposal would solve >> it? > > I gave an example (without rigorous proof accompanying it, granted) > earlier in the thread. In that example, if you allow a selection > against a snapshot which includes the earlier commit (the update of > the control table) and before the later commits (the receipts which > used the old deposit date) you have exactly the kind of > serialization anomaly which the work in progress prevents on the > source (master) database -- the receipts *appear* to run in earlier > transactions because the see the pre-update deposit date, but they > show up out of order. Yep, I see it now. > As far as I'm concerned this is only a > problem if the user *requested* serializable behavior for all > transactions involved. Agreed. > If we send the information I suggested in the WAL stream, then any > slave using the WAL stream could build a snapshot for a serializable > transaction which excluded serializable transactions from the source > which overlap with still-pending serializable transactions on the > source. In this example, the update of the control table would not > be visible to a serializable transaction on the slave until any > overlapping serializable transactions (which would include any > receipts using the old date) had also committed, so you could never > see the writes out of order. > > I don't think that passing detailed predicate locking information > would be feasible from a performance perspective, but since the > slaves are read-only, I think it is fine to pass just the minimal > transaction-level information I described. I suspect that's still going to be sort of hard on performance, but maybe we should get serializable working and committed on one node first and then worry about how to distribute it. I think there might be other approaches to this problem (global transaction coordinator? standby requests snapshot from primary?). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jan Wieck on 24 May 2010 17:25 On 5/24/2010 12:51 PM, Kevin Grittner wrote: > Robert Haas wrote: > >> I think you're confusing two subtly different things. > > The only thing I'm confused about is what benefit anyone expects to > get from looking at data between commits in some way other than our > current snapshot mechanism. Can someone explain a use case where > what Jan is proposing is better than snapshot isolation? It doesn't > provide any additional integrity guarantees that I can see. > >> But the commit order is still the order the effects of those >> transactions have become visible - if we inserted a new read-only >> transaction into the stream at some arbitrary point in time, it >> would see all the transactions which committed before it and none >> of those that committed afterward. > > Isn't that what a snapshot does already? It does and the proposed is a mere alternative serving the same purpose. Have you ever looked at one of those queries, that Londiste or Slony issue against the provider DB in order to get all the log data that has been committed between two snapshots? Is that really the best you can think of? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jan Wieck on 24 May 2010 17:53 On 5/24/2010 3:10 PM, Dan Ports wrote: > I'm not clear on why the total rowcount is useful, but perhaps I'm > missing something obvious. It is a glimpse into the future. Several years of pain doing replication work has taught me that knowing approximately who much work the next chunk will be "before you select it all" is a really useful thing. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Florian Pflug on 24 May 2010 21:09 On May 25, 2010, at 0:42 , Dan Ports wrote: > On Mon, May 24, 2010 at 10:24:07AM -0500, Kevin Grittner wrote: >> Jan Wieck wrote: >> >>> In some systems (data warehousing, replication), the order of >>> commits is important, since that is the order in which changes >>> have become visible. >> >> This issue intersects with the serializable work I've been doing. >> While in database transactions using S2PL the above is true, in >> snapshot isolation and the SSI implementation of serializable >> transactions, it's not. In particular, the snapshot anomalies which >> can cause non-serializable behavior happen precisely because the >> apparent order of execution doesn't match anything so linear as >> order of commit. > > All true, but this doesn't pose a problem in snapshot isolation. Maybe > this is obvious to everyone else, but just to be clear: a transaction's > snapshot is determined entirely by which transactions committed before > it snapshotted (and hence are visible to it). Thus, replaying update > transactions in the sae order on a slave makes the same sequence of > states visible to it. The subtle point here is whether you consider the view from the "outside" (in the sense of what a read-only transaction started at an arbitrary time can or cannot observe), or from the "inside" (what updating transactions can observe and might base their updates on). The former case is completely determined by the commit ordering of the transactions, while the latter is not - otherwise serializability wouldn't be such a hard problem. For some problems, like replication, the former ("outside") view is what matters - if slave synthesizes transactions that insert/update/delete the very same tuples as the original transaction did, and commits them in the same order, no read-only transaction can observe the difference. But that is *not* a serial schedule of the original transactions, since the transactions are *not* the same - the merely touch the same tuples. In fact, if you try replaying the original SQL, you *will* get different results on the slave, and not only because of now() and the like. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 24 May 2010 21:21
Florian Pflug <fgp(a)phlo.org> writes: > The subtle point here is whether you consider the view from the "outside" (in the sense of what a read-only transaction started at an arbitrary time can or cannot observe), or from the "inside" (what updating transactions can observe and might base their updates on). > The former case is completely determined by the commit ordering of the transactions, while the latter is not - otherwise serializability wouldn't be such a hard problem. BTW, doesn't all this logic fall in a heap as soon as you consider read-committed transactions? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |