Re: Reporting the commit LSN at commit time

On 08/15/2014 12:21 AM, Robert Haas wrote:
> On Sat, Aug 9, 2014 at 12:54 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> I don't have a very clear idea whether this is a good idea in any form
> because I can't quite imagine how this is going to be used by the
> client without adding an unwarranted amount of complexity there.

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.

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".

The client must keep track of some kind of information that indicates
the last node it talked to and identifies the last transaction it
committed. ("Client" could mean "proxy" in the case of a failover-aware
pgbouncer.)

> So, even accepting for the moment the premise that the basic idea here
> is good, I think the benefits would have to be monumental to convince
> me that a protocol change is a good idea. If we do anything like
> that, we'll be hearing about the downstream damage for years.

Yes, that's a real concern.

PgJDBC and psqlODBC both implement the wire protocol themselves. PgJDBC
does because it's a type 4 JDBC driver (pure Java, no native code, no
recompile required). I don't understand why psqlODBC goes its own way
instead of using libpq, but it does.

There are also numerous language-specific pure-language bindings, though
half of them seem pretty close to unmaintained.

That's why I proposed a new protocol message carrying extra info, that
clients can optionally request only if they understand it. Nobody else
needs to care or notice that anything's new.

The v2 to v3 protocol switch has only now reached the point where it's
realistic to to drop v2 support from clients. I'm hardly keen to do
another protocol rev, especially for something as minor as this.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services