Re: SSI and Hot Standby - Mailing list pgsql-hackers

2011/1/21 Robert Haas <robertmhaas@gmail.com>:

> On Fri, Jan 21, 2011 at 8:05 AM, Nicolas Barbier
> <nicolas.barbier@gmail.com> wrote:
>
>> 2011/1/21 Anssi Kääriäinen <anssi.kaariainen@thl.fi>:
>>
>>> Sorry for bothering all of you, but I just don't get this. What if T2 rolls
>>> back instead of committing? Then the snapshot of T3 would have been valid,
>>> right? Now, for the snapshot of T3 it doesn't matter if T2 commits or if it
>>> doesn't, because it can't see the changes of T2 in any case. Thus, it would
>>> seem that the snapshot is valid. On the other hand I can't see anything
>>> wrong in the logic in your post. What am I missing? I am feeling stupid...
>>>
>>> At least for dumps I don't see how T2 can matter (assuming T3 is the
>>> pg_dump's snapshot). Because if you reload from the dump, T2 never happened
>>> in that dump. In the reloaded database it just did not exist at all.
>>
>> This has been discussed before; in [1] I summarized:
>>
>> "IOW, one could say that the backup is consistent only if it were
>> never compared against the system as it continued running after the
>> dump took place."
>
> But that's a pretty fair way to look at it, isn't it?

Indeed, I just wanted to point Anssi to the previous discussion.

> It occurs to me that focusing on how this is going to work on Hot
> Standby might be looking at the question too narrowly.  The general
> issue is - does this technique generalize to a distributed computing
> environment, with distributed transactions across multiple PostgreSQL
> databases?  For example, what if the control record in Kevin's example
> is stored in another database, or on another server.  Or what if some
> tables are replicated via Slony?  I realize this is all outside the
> scope of the patch, but that's exactly the point: making this stuff
> work across multiple databases (even if they are replicas of each
> other) is much more complex than getting it to work on just one
> machine.  Even if we could agree on how to do it, coming up with some
> hack that can only ever possibly work in the Hot Standby case might
> not be the best thing to do.

You seem to be questioning whether the normal (?) way of using 2PC on
multiple DBs (just start transactions, and let 2PC coordinate the
commits) that all use SERIALIZABLE isolation mode always results in
global serializable behavior.

I must say that I don't immediately see the answer (my gut feeling
says "nope"), but it sure is an interesting question.

In the special case where all databases use SS2PL as their concurrency
mechanism, the answer is "yes". (Because any "possibly conflicting"
local transactions of any global transactions that touch the same
tables ("conflict") will necessarily commit in some globally
consistent order, which in the case of SS2PL is consistent with the
"must-have-happened-before-in-any-serialized-order" relation, i.e.,
using the terminology from the literature: "because SS2PL is CO".)

I don't know whether it is generally considered a requirement for a
2PC environment to guarantee global serializability.

Nicolas