Re: cheaper snapshots

On Thu, Jul 28, 2011 at 11:36 AM, Hannu Krosing <hannu(at)krosing(dot)net> wrote:
> On Thu, 2011-07-28 at 11:15 -0400, Robert Haas wrote:
>> On Thu, Jul 28, 2011 at 11:10 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>> > My main point was, that we already do synchronization when writing wal,
>> > why not piggyback on this to also update latest snapshot .
>>
>> Well, one problem is that it would break sync rep.
>
> Can you elaborate, in what way it "breaks" sync rep ?

Well, the point of synchronous replication is that the local machine
doesn't see the effects of the transaction until it's been replicated.
Therefore, no one can be relying on data that might disappear in the
event the system is crushed by a falling meteor. It would be easy,
technically speaking, to remove the transaction from the ProcArray and
*then* wait for synchronous replication, but that would offer a much
weaker guarantee than what the current version provides. We would
still guarantee that the commit wouldn't be acknowledged to the client
which submitted it until it was replicated, but we would no longer be
able to guarantee that no one else relied on data written by the
transaction prior to successful replication.

For example, consider this series of events:

1. User asks ATM "what is my balance?". ATM inquires of database,
which says $500.
2. User deposits a check for $100. ATM does an UPDATE to add $100 to
balance and issues a COMMIT. But the master has become disconnected
from the synchronous standby, so the sync rep wait hangs.
3. ATM eventually times out and tells user "sorry, i can't complete
your transaction right now".
4. User wants to know whether their check got deposited, so they walk
into the bank and ask a teller to check their balance. Teller's
computer connects to the database and gets $600. User is happy and
leaves.
5. Master dies. Failover.
6. User's balance is now back to $500. When the user finds out much
later, they say "wtf? you told me before it was $600!".

Right now, when using synchronous replication, this series of events
CANNOT HAPPEN. If some other transaction interrogates the state of
the database and sees the results of some transaction, it is an
ironclad guarantee that the transaction has been replicated. If we
start making transactions visible when their WAL record is flushed or
- worse - when it's inserted, then those guarantees go away.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company