Re: Reporting the commit LSN at commit time

On 08/14/2014 05:45 PM, Craig Ringer wrote:

> Wouldn't that force client drivers - libpq, psqlODBC, PgJDBC, etc - to
> all watch for explicit "COMMIT"s sent by the application and rewrite them?

Realistically, users are going to need new drivers to take advantage of
any automated connection failover anyway.

> Full automatic transparent failover _will_ be complex on the client. No
> denying that. The hard parts are picking which node to connect to when
> one goes away, the decision making around what to do when the new node
> fails to catch up to the last committed state on the old node, and
> tracking session state.

Frankly, I'd love to see just the simplest version of this implemented
in libpq as a start: the ability for client drivers to take a list of
hosts instead of a singe hostaddr (this was discussed at the 2013
clustering meeting).

> There are some quite simple uses too though. The main one of interest to
> me is an app that routes read-only queries to an async read-replica and
> wants to guarantee that some of them see a state consistent with the
> last commit on the master.
>
> It's the first thing many people have asked me about BDR, though. "How
> does client-side failover work". This is a priority for a lot of people.

> As far as I can see, if you have client-side failover with asynchronous
> replication of any form, the client _must_ have some way to reliably
> connect to a new node and ask it "are you caught up to the state of the
> last node I was connected to yet?". Or "Please wait until the last
> transaction I committed elsewhere is visible here".

There are quite a few use-cases where this information isn't required;
even for BDR, I'd love to see the ability to disable this check.

There's also cases where it's not adequate; the user may not have
committed anything on the master, but they still don't want to connect
to a replica which is hours behind the last node they queried.

There's also use-cases for which automated connection failover without a
managed proxy is a Seriously Bad Idea. For one thing, you're setting up
a strong risk of split-brain.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com