Re: cheaper snapshots

On Thu, 2011-07-28 at 14:27 -0400, Robert Haas wrote:
> On Thu, Jul 28, 2011 at 11:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Thu, Jul 28, 2011 at 10:33 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> But should we rethink that? Your point that hot standby transactions on
> >>> a slave could see snapshots that were impossible on the parent was
> >>> disturbing. Should we look for a way to tie "transaction becomes
> >>> visible" to its creation of a commit WAL record? I think the fact that
> >>> they are not an indivisible operation is an implementation artifact, and
> >>> not a particularly nice one.
> >
> >> Well, I agree with you that it isn't especially nice, but it seems
> >> like a fairly intractable problem. Currently, the standby has no way
> >> of knowing in what order the transactions became visible on the
> >> master.
> >
> > Right, but if the visibility order were *defined* as the order in which
> > commit records appear in WAL, that problem neatly goes away. It's only
> > because we have the implementation artifact that "set my xid to 0 in the
> > ProcArray" is decoupled from inserting the commit record that there's
> > any difference.
>
> Hmm, interesting idea. However, consider the scenario where some
> transactions are using synchronous_commit or synchronous replication,
> and others are not. If a transaction that needs to wait (either just
> for WAL flush, or for WAL flush and synchronous replication) inserts
> its commit record, and then another transaction with
> synchronous_commit=off comes along and inserts its commit record, the
> second transaction will have to block until the first transaction is
> done waiting.

What is the current behavior when the synchronous replication fails (say
the slave breaks down) - will the transaction be rolled back at some
point or will it wait indefinitely , that is until a new slave is
installed ?

Or will the sync rep transaction commit when archive_command returns
true after copying the WAL segment containing this commit ?

> We can't make either transaction visible without making
> both visible, and we certainly can't acknowledge the second
> transaction to the client until we've made it visible. I'm not going
> to say that's so horrible we shouldn't even consider it, but it
> doesn't seem great, either.

Maybe this is why other databases don't offer per backend async commit ?

--
-------
Hannu Krosing
PostgreSQL Infinite Scalability and Performance Consultant
PG Admin Book: http://www.2ndQuadrant.com/books/