Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.

On Fri, Mar 18, 2011 at 2:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On further review, I've changed my mind.  Making synchronous_commit
> trump synchronous_replication is appealing conceptually, but it's
> going to lead to some weird corner cases.  For example, a transaction
> that drops a non-temporary relation always commits synchronously; and
> 2PC also ignores synchronous_commit.  In the case where
> synchronous_commit=off and synchronous_replication=on, we'd either
> have to decide that these sorts of transactions aren't going to
> replicate synchronously (which would give synchronous_commit a rather
> long reach into areas it doesn't currently touch) or else that it's OK
> for CREATE TABLE foo () to be totally asynchronous but that DROP TABLE
> foo requires sync commit AND sync rep.  That's pretty weird.
>
> What makes more sense to me after having thought about this more
> carefully is to simply make a blanket rule that when
> synchronous_replication=on, synchronous_commit has no effect.  That is
> easy to understand and document.  I'm inclined to think it's OK to let
> synchronous_replication have this effect even if max_wal_senders=0 or
> synchronous_standby_names=''; you shouldn't turn
> synchronous_replication on just for kicks, and I don't think we want
> to complicate the test in RecordTransactionCommit() more than
> necessary.  We should, however, adjust the logic so that a transaction
> which has not written WAL can still commit asynchronously, because
> such a transaction has only touched temp or unlogged tables and so
> it's not important for it to make it to the standby, where that data
> doesn't exist anyway.

In the first place, I think that it's complicated to keep those two parameters
separately. What about merging them to one parameter? What I'm thinking
is to remove synchronous_replication and to increase the valid values of
synchronous_commit from on/off to async/local/remote/both. Each value
works as follows.

async = (synchronous_commit = off && synchronous_replication = off)
"async" makes a transaction do local WAL flush and replication
asynchronously.

local = (synchronous_commit = on && synchronous_replication = off)
"local" makes a transaction wait for only local WAL flush.

remote = (synchronous_commit = off && synchronous_replication = on)
"remote" makes a transaction wait for only replication. Local WAL flush is
performed by walwriter. This is useless in 9.1 because we always must
wait for local WAL flush when we wait for replication. But in the future,
if we'll be able to send WAL before WAL write (i.e., send WAL from
wal_buffers), this might become useful. In 9.1, it seems reasonable to
remove this value.

both = (synchronous_commit = on && synchronous_replication = on)
"both" makes a transaction wait for local WAL flush and replication.

Thought?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center