Re: Reporting the commit LSN at commit time

Hi all

To support transparent client-side failover in BDR, it's necessary to
know what the LSN of a node was at the time a transaction committed and
keep track of that in the client/proxy.

I'm thinking about adding a new message type in the protocol that gets
sent immediately before CommandComplete, containing the LSN of the
commit. Clients would need to enable the sending of this message with a
GUC that they set when they connect, so it doesn't confuse clients that
aren't expecting it or aware of it.

Is this something you can see being useful for other non-BDR purposes?
Are there any obvious issues with this?

Clients can always follow up with a second query to get the xlog
position, after commit, but that's potentially slow and has a race that
might cause a client to wait longer than it has to after fail-over to a
different node. That's why having the server report it automatically
seems useful.

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

On Thu, Aug 7, 2014 at 4:15 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> I'm thinking about adding a new message type in the protocol that gets
> sent immediately before CommandComplete, containing the LSN of the
> commit. Clients would need to enable the sending of this message with a
> GUC that they set when they connect, so it doesn't confuse clients that
> aren't expecting it or aware of it.
>
> Is this something you can see being useful for other non-BDR purposes?

I have been thinking about something similar.

For regular streaming replication you could keep track of the commit
LSN on a per client basis and automatically redirect read queries to a
standby if standby apply location is larger than the commit LSN of
this particular client. This can be done mostly transparently for the
application while not running into the issue that recent modifications
disappear for a while after commit.

Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

On Wed, Aug 6, 2014 at 9:15 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> To support transparent client-side failover in BDR, it's necessary to
> know what the LSN of a node was at the time a transaction committed and
> keep track of that in the client/proxy.

I doubt whether it makes sense to do this without a broader
understanding of how the client-side failover mechanism would work.
If we're going to add something like this, it should include libpq
support for actually doing something useful with it.

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

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> Hi all
> To support transparent client-side failover in BDR, it's necessary to
> know what the LSN of a node was at the time a transaction committed and
> keep track of that in the client/proxy.

> I'm thinking about adding a new message type in the protocol that gets
> sent immediately before CommandComplete, containing the LSN of the
> commit. Clients would need to enable the sending of this message with a
> GUC that they set when they connect, so it doesn't confuse clients that
> aren't expecting it or aware of it.

FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
at all. What happens five years from now when we switch to some other
implementation that doesn't have LSNs?

I don't mind if you expose some other way to inquire about LSNs, but
let's *not* embed it into the wire protocol. Not even as an option.

This position also obviates the need to complain about having a GUC
that changes the protocol-level behavior, which is also a seriously
bad idea.

regards, tom lane

On 08/08/2014 03:54 AM, Tom Lane wrote:
> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>> Hi all
>> To support transparent client-side failover in BDR, it's necessary to
>> know what the LSN of a node was at the time a transaction committed and
>> keep track of that in the client/proxy.
>
>> I'm thinking about adding a new message type in the protocol that gets
>> sent immediately before CommandComplete, containing the LSN of the
>> commit. Clients would need to enable the sending of this message with a
>> GUC that they set when they connect, so it doesn't confuse clients that
>> aren't expecting it or aware of it.
>
> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
> at all. What happens five years from now when we switch to some other
> implementation that doesn't have LSNs?

Everyone who's relying on them already via pg_stat_replication, etc, breaks.

They're _already_ exposed to users. That ship has sailed.

That's not to say I'm stuck to LSNs as the way to do this, just that I
don't think that particular argument is relevant.

> I don't mind if you expose some other way to inquire about LSNs, but
> let's *not* embed it into the wire protocol. Not even as an option.

Well, the underlying need here is to have the client able to keep track
of what point in the server's time-line it must see on a replica before
it proceeds to use that replica.

So if the client does some work on server A, then for some reason needs
to / must use server B, it can ask server B "are you replayed up to the
last transaction I performed on server A yet?" and wait until it is.

That's useful for streaming replication (to permit consistent queries
against read replicas) but it's much more so for BDR, where it's
necessary to avoid a variety of multi-master replication anomalies and
conflicts.

I considered LSNs to be the logical mechanism for this as they're
already user-visible, exposed in pg_stat_replication, they can already
be used for just this purpose by hand (just with an extra round-trip), etc.

An obvious alternative is to merge the commit timestamp work, then
expose the timestamp of the last commit replayed in pg_stat_replication.
Then all the client needs to keep track of is the server time of the
last commit.

> This position also obviates the need to complain about having a GUC
> that changes the protocol-level behavior, which is also a seriously
> bad idea.

Well, I'd prefer to be able to negotiate with the server and establish
requirements during the protocol handshake.

As far as I know there isn't an explicit protocol negotiation with
capabilities fields (just a plain version field), but we do have the
startup packet's 'options' field. So I was thinking that requesting the
setting of a PGC_BACKEND GUC in the startup packet would be a logical
way for the client to request use of a protocol extension.

Looking at ProcessStartupPacket(...) in postmaster.c I see that there's
room for special-casing options. Do you think it would be more
appropriate to add a new connection option that's sent by a client to
request reporting of commit timestamps / LSNs / whatever by the server
at commit time?

If not, do you have an alternative suggestion? I can't imagine that
extending the CommandComplete message is a desirable option.

It seems like it'd be useful to expose this as a read-only GUC anyway,
so I don't really see why a PGC_BACKEND GUC isn't exactly the right
thing to use for this, but I'm happy to listen to suggestions.

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

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> On 08/08/2014 03:54 AM, Tom Lane wrote:
>> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
>> at all. What happens five years from now when we switch to some other
>> implementation that doesn't have LSNs?

> Everyone who's relying on them already via pg_stat_replication, etc, breaks.
> They're _already_ exposed to users. That ship has sailed.

They're exposed to replication tools, yeah, but embedding them in the
wire protocol would be moving the goalposts a long way past that. As an
example of something that doubtless seemed like a good idea at the time,
consider the business about how an INSERT command completion tag includes
the OID of the inserted row. We're stuck with that obsolete idea
*forever* because it's embedded in the protocol for all clients.

>> This position also obviates the need to complain about having a GUC
>> that changes the protocol-level behavior, which is also a seriously
>> bad idea.

> Well, I'd prefer to be able to negotiate with the server and establish
> requirements during the protocol handshake.

Sure, but a GUC is not that. The problem with a GUC for changing
wire-level behavior is that it might be set by code far removed from
the wire, possibly breaking lower code levels that expected different
behavior. The multitude of ways that we offer for setting GUCs is
an active evil in this particular context.

regards, tom lane

On 08/07/2014 11:42 PM, Robert Haas wrote:
> I doubt whether it makes sense to do this without a broader
> understanding of how the client-side failover mechanism would work.
> If we're going to add something like this, it should include libpq
> support for actually doing something useful with it.

I'm currently interested in targeting PgBouncer and PgJDBC, not libpq,
though I can see that exposing helpers for it in libpq could be useful.

The goal here is to permit a client to safely switch from one server to
another - either in a multimaster async replication system like BDR, or
routing read-only queries to hot standbys with streaming replication -
and know for sure that its last commit is visible on the server it is
now connected to.

For hot standby that means it can avoid running queries that won't see
the latest work it did if the standby is lagging - deciding to run them
on the upstream instead, or wait, as appropriate.

For BDR it'll permit the client to safely perform transparent failover
to another node and resume write operations without risking conflicts
with its own prior transactions . (I wrote some explanations about this
on -general in the thread here:
http://www.postgresql.org/message-id/84184AEF-887D-49DF-8F47-6377B1D6EE9F@gmail.com
).

Broadly, what I'm thinking of is:

* Whenever a client issues a transaction that gets a txid assigned, and
that tx commits, the server reports the LSN that includes the commit.

* The client keeps track of which server it is connected to using the
server's (sysid, timelineid, databaseoid) or a similar identifier -
probably specific to the replication protocol in use, unless something
generic proves practical.

* When the client has to switch to a new server or chooses to do so, it
checks pg_stat_replication or pg_replication_slots, finds the server it
was previously connected to, and checks to see if the new server has
replayed up to the last write transaction this client performed on the
previous server. If not, it can make a policy-driven decision: wait
until replay catchup, wait for a while then bail out, etc.

This is admittedly all a bit hand-wavey. I'm looking at ways to do it,
not a firm implementation plan.

Notably, the LSN (and timelineID) aren't the only way to keep track of
the replay progress of a server and check it from another server. If the
commit timestamps work is merged and the timestamp of the last replayed
commit record is exposed in pg_replication_slots, the client could use
the server-reported commit timestamp to the same effect.

In the above you'll note that the client has to make some choices. The
client might be picking different servers for failover, read load
spreading, or other things I haven't thought of. It might be retaining
the old connection and making new ones it wants to be consistent up to a
certain point on the old connection (read spreading), or it might be
dropping the old connection and making a new one (failover). If the new
server to connect to isn't caught up yet it might want to wait
indefinitely, wait a short while, or bail out immediately and try a
different server. There's a lot of client/application specific policy
going to be involved here, so I'm not sure it makes sense to try to make
it transparent in libpq. I can see it being useful to expose some tools
in libpq for it, without a doubt, so clients can do these sorts of
things usefully.

(There's also another whole new question: how do you pick which
alternative server to connect to? But that's not really within the scope
of this.)

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

On 08/08/2014 09:02 AM, Tom Lane wrote:
> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>> On 08/08/2014 03:54 AM, Tom Lane wrote:
>>> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
>>> at all. What happens five years from now when we switch to some other
>>> implementation that doesn't have LSNs?
>
>> Everyone who's relying on them already via pg_stat_replication, etc, breaks.
>> They're _already_ exposed to users. That ship has sailed.
>
> They're exposed to replication tools, yeah, but embedding them in the
> wire protocol would be moving the goalposts a long way past that. As an
> example of something that doubtless seemed like a good idea at the time,
> consider the business about how an INSERT command completion tag includes
> the OID of the inserted row. We're stuck with that obsolete idea
> *forever* because it's embedded in the protocol for all clients.

That makes sense.

>> Well, I'd prefer to be able to negotiate with the server and establish
>> requirements during the protocol handshake.
>
> Sure, but a GUC is not that. The problem with a GUC for changing
> wire-level behavior is that it might be set by code far removed from
> the wire, possibly breaking lower code levels that expected different
> behavior. The multitude of ways that we offer for setting GUCs is
> an active evil in this particular context.

I'd value your input and suggestions then.

I thought this is what PGC_BACKEND GUCs were for - set them in the
startup packet and you can't mess with them afterwards. I can see that
the ability of someone to cause that to be set in (e.g.) PGOPTIONS could
be a problem though.

AFAIK we don't _have_ a fancy negotiation system in the protocol, with
back-and-forth exchanges of capabilities information.

So is the appropriate thing to do here to set a non-GUC 'options' field
in the startup packet?

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

Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> On 08/08/2014 09:02 AM, Tom Lane wrote:
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>>> Well, I'd prefer to be able to negotiate with the server and establish
>>> requirements during the protocol handshake.

>> Sure, but a GUC is not that. The problem with a GUC for changing
>> wire-level behavior is that it might be set by code far removed from
>> the wire, possibly breaking lower code levels that expected different
>> behavior. The multitude of ways that we offer for setting GUCs is
>> an active evil in this particular context.

> I thought this is what PGC_BACKEND GUCs were for - set them in the
> startup packet and you can't mess with them afterwards. I can see that
> the ability of someone to cause that to be set in (e.g.) PGOPTIONS could
> be a problem though.

Right. AFAICS it's mostly irrelevant whether or not the setting can be
changed intrasession; what *is* important is that it not be turned on
if the lowest level(s) of the client software stack can't handle it.
It's not beyond the realm of credibility that there could be
security-grade bugs arising from client software receiving messages it
didn't understand. And what that means is that anything that is
accessible to higher levels of the stack is suspect. We've provided
enough pass-through behaviors that I'm not sure anything in the current
connection options API would be safe.

> AFAIK we don't _have_ a fancy negotiation system in the protocol, with
> back-and-forth exchanges of capabilities information.

Maybe it's time to invent that. It would be positively foolish to
create any such behavior without a protocol version bump anyway.

Although as I said, I don't think embedding knowledge of LSNs at the
protocol level is a good thing to begin with. Is it really necessary that
this information be pushed to the client on every commit, as opposed to
the client asking for it occasionally?

regards, tom lane

On Fri, Aug 8, 2014 at 9:50 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 08/08/2014 03:54 AM, Tom Lane wrote:
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>>> Hi all
>>> To support transparent client-side failover in BDR, it's necessary to
>>> know what the LSN of a node was at the time a transaction committed and
>>> keep track of that in the client/proxy.
>>
>>> I'm thinking about adding a new message type in the protocol that gets
>>> sent immediately before CommandComplete, containing the LSN of the
>>> commit. Clients would need to enable the sending of this message with a
>>> GUC that they set when they connect, so it doesn't confuse clients that
>>> aren't expecting it or aware of it.
>>
>> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
>> at all. What happens five years from now when we switch to some other
>> implementation that doesn't have LSNs?
>
> Everyone who's relying on them already via pg_stat_replication, etc, breaks.
>
> They're _already_ exposed to users. That ship has sailed.
>
> That's not to say I'm stuck to LSNs as the way to do this, just that I
> don't think that particular argument is relevant.
>
>> I don't mind if you expose some other way to inquire about LSNs, but
>> let's *not* embed it into the wire protocol. Not even as an option.
>
> Well, the underlying need here is to have the client able to keep track
> of what point in the server's time-line it must see on a replica before
> it proceeds to use that replica.
>
> So if the client does some work on server A, then for some reason needs
> to / must use server B, it can ask server B "are you replayed up to the
> last transaction I performed on server A yet?" and wait until it is.

ISTM that the proper solution to that problem is the introduction of
new synchronous replication mode which makes the transaction wait for
its WAL to be replayed by the standby. If this mode is used, a client
doesn't need to track the LSN of each transaction and check whether
the committed transaction has already replayed by the standby or not.

Regards,

--
Fujii Masao

On 08/08/2014 10:58 AM, Fujii Masao wrote:
> ISTM that the proper solution to that problem is the introduction of
> new synchronous replication mode which makes the transaction wait for
> its WAL to be replayed by the standby. If this mode is used, a client
> doesn't need to track the LSN of each transaction and check whether
> the committed transaction has already replayed by the standby or not.

I'm not convinced of that.

That pushes the penalty onto the writer - which now has to wait until
replicas catch up. It has to pay this for every commit, even if actually
failing over to another node is unlikely.

It'd be better to just enable sync rep instead, or it would if we had
all-nodes sync rep.

IMO any waiting needs to be done on the other side, i.e. "Wait until I
am caught up before proceeding" rather than "wait for the other end to
catch up before returning".

Doing it the way you describe would make it nearly useless for enabling
client-side failover in BDR, where half the point is that it can deal
with high latency or intermittently available links to downstream replicas.

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

On Fri, Aug 8, 2014 at 11:58 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Fri, Aug 8, 2014 at 9:50 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> ISTM that the proper solution to that problem is the introduction of
> new synchronous replication mode which makes the transaction wait for
> its WAL to be replayed by the standby. If this mode is used, a client
> doesn't need to track the LSN of each transaction and check whether
> the committed transaction has already replayed by the standby or not.
Don't you need to combine that with the possibility to wait for N
targets instead of 1 in synchronous_standby_names? You may want to be
sure that the commit is done on a series of standbys before
considering any further operations after this transaction commit.
--
Michael

On 08/08/2014 09:51 AM, Tom Lane wrote:

>> AFAIK we don't _have_ a fancy negotiation system in the protocol, with
>> back-and-forth exchanges of capabilities information.
>
> Maybe it's time to invent that. It would be positively foolish to
> create any such behavior without a protocol version bump anyway.

I was hoping it'd be easier to sneak a new message type in without a
full protocol bump. As you can imagine that makes it a ... rather larger
job.

Still, if it's unsafe to do it that way...

> Although as I said, I don't think embedding knowledge of LSNs at the
> protocol level is a good thing to begin with.

As I said upthread, it need not necessarily be an LSN. A commit
timestamp would do the job too, if information about the last-replayed
commit timestamp was accessible on the downstream node.

It needs to be a sequence identifier that can be matched against
pg_replication_slots / pg_stat_replication or passed to a function on
the downstream end to say "wait until we're replayed to this point".

For streaming replication there's only one upstream, so there's no need
to identify it. For BDR you'd also have to identify the upstream node of
interest - probably by slot ID, or by (sysid, tlid, dboid) tuple.

In the end, it can be an opaque magic cookie. It really doesn't matter,
so long as what the client receives is a value it can pass to another Pg
instance and say "wait until you've replayed up to this, please" or
"have you replayed up to this yet?".

> Is it really necessary that this information be pushed to the client
on every commit, as opposed to the client asking for it occasionally?

I think so, yes, though I'd be glad to be proved wrong.

For the purpose of transparent failover (BDR) at least, the server
currently being written to can go away at any moment, and you should
know exactly what you're up to in order to make it safe to continue on
another server.

Consider, for a multi-master configuration where two servers replicate
to each other:

On a connection to server1:

INSERT INTO bird(id, parrot)
VALUES (1, 'African Grey');

[client grabs magic cookie for server replay state]

INSERT INTO bird(id, parrot)
VALUES (2, 'Lorikkeet');

[server1 sends changes to server2, which is behind on replay
and still working on catching up]

[server1 dies abruptly]

[client drops connection to server1, connects to server2]

-- Correct spelling
UPDATE bird
SET parrot = 'Lorikeet'
WHERE id = 2;

If the INSERT from server1 hasn't replayed on server2 yet this will
fail. Other anomalies can be worse and cause lost updates, etc.

To protect against this the client needs a way to wait, after connecting
to server2, until it's caught up with the state of server1. That's what
I'm talking about here. In this case, if you used a periodic progress
indicator requested by the client, you'd still get the same error,
because you'd wait until the first INSERT but not the second.

So yes, the client needs this info at every commit.

That means that enabling client-side fail-over won't be free, especially
for lots of small transactions. It'll be cheaper if Pg can push the info
with the commit confirmation instead of the client having to request it
afterwards though.

(Note that the client risks waiting forever if server1 didn't send the
required commits before it died, but that's where application policy
decisions come in).

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

On 2014-08-07 21:02:54 -0400, Tom Lane wrote:
> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> > On 08/08/2014 03:54 AM, Tom Lane wrote:
> >> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
> >> at all. What happens five years from now when we switch to some other
> >> implementation that doesn't have LSNs?
>
> > Everyone who's relying on them already via pg_stat_replication, etc, breaks.
> > They're _already_ exposed to users. That ship has sailed.
>
> They're exposed to replication tools, yeah, but embedding them in the
> wire protocol would be moving the goalposts a long way past that. As an
> example of something that doubtless seemed like a good idea at the time,
> consider the business about how an INSERT command completion tag includes
> the OID of the inserted row. We're stuck with that obsolete idea
> *forever* because it's embedded in the protocol for all clients.

I don't think we really need to embed it at that level. And it doesn't
have to be always on - only clients that ask for it need to get the
answer. Something like COMMIT WITH (report_commit_lsn ON); or similar
might do the trick?

Greetings,

Andres Freund

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

On 08/10/2014 12:54 AM, Andres Freund wrote:
> On 2014-08-07 21:02:54 -0400, Tom Lane wrote:
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>>> On 08/08/2014 03:54 AM, Tom Lane wrote:
>>>> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
>>>> at all. What happens five years from now when we switch to some other
>>>> implementation that doesn't have LSNs?
>>
>>> Everyone who's relying on them already via pg_stat_replication, etc, breaks.
>>> They're _already_ exposed to users. That ship has sailed.
>>
>> They're exposed to replication tools, yeah, but embedding them in the
>> wire protocol would be moving the goalposts a long way past that. As an
>> example of something that doubtless seemed like a good idea at the time,
>> consider the business about how an INSERT command completion tag includes
>> the OID of the inserted row. We're stuck with that obsolete idea
>> *forever* because it's embedded in the protocol for all clients.
>
> I don't think we really need to embed it at that level. And it doesn't
> have to be always on - only clients that ask for it need to get the
> answer. Something like COMMIT WITH (report_commit_lsn ON); or similar
> might do the trick?

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

Applications could also then request the commit option via a driver that
couldn't cope with it - which I think was one of Tom's concerns re using
a GUC, too.

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

On 2014-08-10 08:50:58 +0800, Craig Ringer wrote:
> On 08/10/2014 12:54 AM, Andres Freund wrote:
> > On 2014-08-07 21:02:54 -0400, Tom Lane wrote:
> >> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> >>> On 08/08/2014 03:54 AM, Tom Lane wrote:
> >>>> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
> >>>> at all. What happens five years from now when we switch to some other
> >>>> implementation that doesn't have LSNs?
> >>
> >>> Everyone who's relying on them already via pg_stat_replication, etc, breaks.
> >>> They're _already_ exposed to users. That ship has sailed.
> >>
> >> They're exposed to replication tools, yeah, but embedding them in the
> >> wire protocol would be moving the goalposts a long way past that. As an
> >> example of something that doubtless seemed like a good idea at the time,
> >> consider the business about how an INSERT command completion tag includes
> >> the OID of the inserted row. We're stuck with that obsolete idea
> >> *forever* because it's embedded in the protocol for all clients.
> >
> > I don't think we really need to embed it at that level. And it doesn't
> > have to be always on - only clients that ask for it need to get the
> > answer. Something like COMMIT WITH (report_commit_lsn ON); or similar
> > might do the trick?
>
> Wouldn't that force client drivers - libpq, psqlODBC, PgJDBC, etc - to
> all watch for explicit "COMMIT"s sent by the application and rewrite them?

Any application doing such "transparent" failover would need to have a
driver that's aware of all that anyway. They need to learn about the
transaction boundaries, the commit command and such. I personally think
this should mean that that feature requires an explicit API call for
transaction control.

Greetings,

Andres Freund

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

On Sat, Aug 9, 2014 at 12:54 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-08-07 21:02:54 -0400, Tom Lane wrote:
>> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>> > On 08/08/2014 03:54 AM, Tom Lane wrote:
>> >> FWIW, I think it's a seriously bad idea to expose LSNs in the protocol
>> >> at all. What happens five years from now when we switch to some other
>> >> implementation that doesn't have LSNs?
>>
>> > Everyone who's relying on them already via pg_stat_replication, etc, breaks.
>> > They're _already_ exposed to users. That ship has sailed.
>>
>> They're exposed to replication tools, yeah, but embedding them in the
>> wire protocol would be moving the goalposts a long way past that. As an
>> example of something that doubtless seemed like a good idea at the time,
>> consider the business about how an INSERT command completion tag includes
>> the OID of the inserted row. We're stuck with that obsolete idea
>> *forever* because it's embedded in the protocol for all clients.
>
> I don't think we really need to embed it at that level. And it doesn't
> have to be always on - only clients that ask for it need to get the
> answer. Something like COMMIT WITH (report_commit_lsn ON); or similar
> might do the trick?

And what does that actually do? Send back a result-set, or a new
protocol message?

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.
However, based on my experiences at EnterpriseDB, I would be extremely
wary of extending the wire protocol. As soon as we do that, it
requires updates to a really phenomenal amount of other software.
Software using libpq may be more or less able to ignore the
difference, as long as they have a new-enough version of libpq (which
is a significant proviso). But any driver that has its own
implementation of the wire protocol (and I think there is at least one
and maybe several important ones that do) needs updating, and anything
that acts as middleware (pgpool, pgbouncer) does too. And it's not
just a matter of the maintainers making the appropriate changes
(though that does need to happen); it's also about everyone who is
using the new server version getting new versions of that other
software also.

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.

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

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

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

On 2014-08-14 12:21:38 -0400, Robert Haas wrote:
> On Sat, Aug 9, 2014 at 12:54 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > I don't think we really need to embed it at that level. And it doesn't
> > have to be always on - only clients that ask for it need to get the
> > answer. Something like COMMIT WITH (report_commit_lsn ON); or similar
> > might do the trick?
>
> And what does that actually do? Send back a result-set, or a new
> protocol message?

What I was thinking of was to return "COMMIT X/X" instead of
"COMMIT". Since that's only sent when COMMIT WITH (report_commit_lsn ON)
was set it won't break clients/libraries that don't need it.

Greetings,

Andres Freund

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

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-08-14 12:21:38 -0400, Robert Haas wrote:
>> And what does that actually do? Send back a result-set, or a new
>> protocol message?

> What I was thinking of was to return "COMMIT X/X" instead of
> "COMMIT". Since that's only sent when COMMIT WITH (report_commit_lsn ON)
> was set it won't break clients/libraries that don't need it.

Au contraire: it will break any piece of code that is expecting a COMMIT
command tag to look like exactly "COMMIT" and not "COMMIT something".
If you think there isn't any such, a look into libpq should disabuse you
of that notion. (Admittedly, libpq's instance is only in the protocol-V2
code paths, but I'm sure that similar code exists elsewhere client-side.)

The risk still remains, therefore, that one layer of the client-side
software stack might try to enable this feature even though another
layer is not prepared for it. Changing the command tag might actually
be *more* dangerous than a new protocol message, rather than less so,
because command tags are usually exposed at multiple layers of the stack
--- libpq for instance happily returns them up to its caller. So it
will be somewhere between difficult and impossible to be sure that one
has fixed everything that needs fixing.

And, again, I think that controlling this via something as widely
changeable as a GUC is sheer folly, potentially even reaching the point
of being a security bug. (Applications that fail to recognize when
their transactions have committed would be broken in very nasty ways.)

regards, tom lane

On 2014-08-15 09:54:01 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2014-08-14 12:21:38 -0400, Robert Haas wrote:
> >> And what does that actually do? Send back a result-set, or a new
> >> protocol message?
>
> > What I was thinking of was to return "COMMIT X/X" instead of
> > "COMMIT". Since that's only sent when COMMIT WITH (report_commit_lsn ON)
> > was set it won't break clients/libraries that don't need it.
>
> Au contraire: it will break any piece of code that is expecting a COMMIT
> command tag to look like exactly "COMMIT" and not "COMMIT something".

Well, if your code doesn't support it. Don't use it.

> The risk still remains, therefore, that one layer of the client-side
> software stack might try to enable this feature even though another
> layer is not prepared for it.

Well, then the user will have to fix that. It's not like the feature
will magically start to be used by itself.

One alternative would be to expose a pg_get_last_commit_lsn(); function
that'd return the the last commit's lsn stored in a static variable if
set. That'll increase the window in which the connection can break, but
that window already exists.

> And, again, I think that controlling this via something as widely
> changeable as a GUC is sheer folly, potentially even reaching the point
> of being a security bug. (Applications that fail to recognize when
> their transactions have committed would be broken in very nasty ways.)

*I*'ve never suggested making this depend on a guc. I think that'd a
major PITA.

Greetings,

Andres Freund

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

On Fri, Aug 15, 2014 at 9:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> On 2014-08-14 12:21:38 -0400, Robert Haas wrote:
>>> And what does that actually do? Send back a result-set, or a new
>>> protocol message?
>
>> What I was thinking of was to return "COMMIT X/X" instead of
>> "COMMIT". Since that's only sent when COMMIT WITH (report_commit_lsn ON)
>> was set it won't break clients/libraries that don't need it.
>
> Au contraire: it will break any piece of code that is expecting a COMMIT
> command tag to look like exactly "COMMIT" and not "COMMIT something".
> If you think there isn't any such, a look into libpq should disabuse you
> of that notion. (Admittedly, libpq's instance is only in the protocol-V2
> code paths, but I'm sure that similar code exists elsewhere client-side.)

Well, I remember debating this with you once before, when we were
deciding whether to make SELECT INTO and CREATE TABLE AS return row
counts in the command tag. That change went into 9.0 and, while I
think we may have gotten maybe one complaint about it, on the whole I
believe it went pretty smoothly. If we're going to expose this
information, I believe that exposing it through the command tag is
about an order of magnitude safer than trying to return it through a
new protocol message or a result-set. I *have* seen code that parses
command tags and cares about their exact contents, but I think nearly
all code passes them through as uninterpreted strings. Contrariwise,
there is a whole LOT of code that cares whether PQresultStatus()
returns PGRES_COMMAND_OK vs. PGRES_TUPLES_OK; if we made the latter
happen for some commits, a huge amount of stuff would break. And
adding a new protocol message is a disaster for middleware, but most
if not all of that middleware will happily pass on a revised command
tag without batting an eyelash.

All that having been said, I'm not convinced that we should do this at
all unless we've got a libpq implementation of client-side failover so
that people can actually use this without having to put all of the
logic in their application. But if we are going to do it, the command
tag seems like the least-risky option of those proposed thus far by a
considerable margin.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Aug 15, 2014 at 9:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Au contraire: it will break any piece of code that is expecting a COMMIT
>> command tag to look like exactly "COMMIT" and not "COMMIT something".

> Well, I remember debating this with you once before, when we were
> deciding whether to make SELECT INTO and CREATE TABLE AS return row
> counts in the command tag. That change went into 9.0 and, while I
> think we may have gotten maybe one complaint about it, on the whole I
> believe it went pretty smoothly.

I think it's a serious, serious mistake to equate the number of clients
that deal with COMMIT specially with the number that have special logic
for (or even use at all) SELECT INTO/CREATE TABLE AS. So I don't find
that argument to have any merit.

The precedent that seems more relevant to me is our disastrous attempt to
put in server-side autocommit behavior, back in 7.3. We thought that that
wouldn't break too much client code; we were wrong. And IMO a large part
of the reason we were wrong was that we exposed the switch as a GUC,
whereby anybody could twiddle it regardless of whether the relevant
client-side layer(s) would cope.

> All that having been said, I'm not convinced that we should do this at
> all unless we've got a libpq implementation of client-side failover so
> that people can actually use this without having to put all of the
> logic in their application.

There's that, too. The whole proposal is a solution in search of a
problem at the moment, or maybe better to say that it's 1% of a solution
with no clear path to getting the other 99% done.

regards, tom lane

On Mon, Aug 18, 2014 at 12:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Aug 15, 2014 at 9:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Au contraire: it will break any piece of code that is expecting a COMMIT
>>> command tag to look like exactly "COMMIT" and not "COMMIT something".
>
>> Well, I remember debating this with you once before, when we were
>> deciding whether to make SELECT INTO and CREATE TABLE AS return row
>> counts in the command tag. That change went into 9.0 and, while I
>> think we may have gotten maybe one complaint about it, on the whole I
>> believe it went pretty smoothly.
>
> I think it's a serious, serious mistake to equate the number of clients
> that deal with COMMIT specially with the number that have special logic
> for (or even use at all) SELECT INTO/CREATE TABLE AS. So I don't find
> that argument to have any merit.
>
> The precedent that seems more relevant to me is our disastrous attempt to
> put in server-side autocommit behavior, back in 7.3. We thought that that
> wouldn't break too much client code; we were wrong. And IMO a large part
> of the reason we were wrong was that we exposed the switch as a GUC,
> whereby anybody could twiddle it regardless of whether the relevant
> client-side layer(s) would cope.

1. That seems like a false parallel, because that sounds like it was
an actual behavior change, not just a reporting change.

2. I agree that it's not good to have this get controlled by a GUC.
If the behavior change is big enough that it's going to break clients,
adding a GUC isn't a sufficient remedy. If it's not, adding a GUC is
unnecessary.

3. I really doubt people are relying on the COMMIT command tag to know
the transaction status. Surely the right way to get that information
is from the transaction state code in the ReadyForQuery message; isn't
the whole point of having that there that it avoids the need for
fine-grained knowledge of what other protocol messages mean?
Moreover, even if somebody is (rather oddly, IMV) checking for
command_tag == "COMMIT", it's not going to be a difficult change to
check for command_tag == "COMMIT" or substr(command_tag,0,7) ==
"COMMIT ". That's a level of incompatibility that would certainly
deserve mention in the release notes, but it doesn't seem worth
worrying more about than that.

>> All that having been said, I'm not convinced that we should do this at
>> all unless we've got a libpq implementation of client-side failover so
>> that people can actually use this without having to put all of the
>> logic in their application.
>
> There's that, too. The whole proposal is a solution in search of a
> problem at the moment, or maybe better to say that it's 1% of a solution
> with no clear path to getting the other 99% done.

Yeah, and I think that's the much more worrying problem.

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

Robert Haas wrote:

> Moreover, even if somebody is (rather oddly, IMV) checking for
> command_tag == "COMMIT", it's not going to be a difficult change to
> check for command_tag == "COMMIT" or substr(command_tag,0,7) ==
> "COMMIT ". That's a level of incompatibility that would certainly
> deserve mention in the release notes, but it doesn't seem worth
> worrying more about than that.

I agree. If an application fails its test for a new major PG release
because the received command tag is no longer identical to "COMMIT",
it's not going to be a subtle failure --- people are going to notice
pretty much immediately, I expect.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 15 Aug 2014 14:54, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2014-08-14 12:21:38 -0400, Robert Haas wrote:
> >> And what does that actually do? Send back a result-set, or a new
> >> protocol message?
>
> > What I was thinking of was to return "COMMIT X/X" instead of
> > "COMMIT". Since that's only sent when COMMIT WITH (report_commit_lsn ON)
> > was set it won't break clients/libraries that don't need it.
>
> Au contraire: it will break any piece of code that is expecting a COMMIT
> command tag to look like exactly "COMMIT" and not "COMMIT something".

Two comments. Firstly, we can't freeze everything forever. This seems like
it would be the least of people's issues to deal with for an upgrade. We
should search for the cleanest solution, not bolt on features for fear that
integrating them where they make the most sense might break someone's code
somewhere.

I fear putting out anywhere but in the commit message would have race
conditions. Having it in the commit message guarantees the client never has
to deal with strange states like " I know this transaction committed but I
know when" which would make clients vastly simpler.

But secondly there will *always* be race conditions in this. This is the
same problem as our hokey synchronous commit which commits transactions but
then hides them from the client. All it does is move the uncertainty around.

The long term solution will be something more like 2PC. At the beginning of
your transaction you get an identifier for the transaction and store that
in a transaction manager. The transaction manager knows how to determine
which transcribe are committed on which nodes and how to resolve conflicts.

On 18 Aug 2014 20:05, "Greg Stark" <stark(at)mit(dot)edu> wrote:
>Having it in the commit message guarantees the client never has to deal
with strange states like " I know this transaction committed but I know
when"

Sigh. Typing on the phone. "But I *don't* know when"

On 08/19/2014 01:03 AM, Robert Haas wrote:
> 2. I agree that it's not good to have this get controlled by a GUC.
> If the behavior change is big enough that it's going to break clients,
> adding a GUC isn't a sufficient remedy. If it's not, adding a GUC is
> unnecessary.

There's plenty of agreement on "not a GUC" - but what about alternatives?

I mentioned setting an option in the startup packet, to be processed
directly not set as a GUC like some of the other startup packet options
already are, but didn't see any real response there.

I presume the concern there is that, while it's not a GUC as far as the
server is concerned, libpq at least will still send a value for it in
the startup packet if it's defined in

PGOPTIONS='-c protocol_affecting_option_here=on'

so while it isn't a GUC to the server, clients can still twiddle it w/o
making specific new API calls.

I'm not entirely sure why the answer isn't what we excuse other
problematic or potential user-foot-gun behaviour with: "Well, don't do
that".

> 3. I really doubt people are relying on the COMMIT command tag to know
> the transaction status. Surely the right way to get that information
> is from the transaction state code in the ReadyForQuery message; isn't
> the whole point of having that there that it avoids the need for
> fine-grained knowledge of what other protocol messages mean?

I wouldn't assume anything about how people are using the protocol,
after some of the things I've seen in client code and client drivers
recently. Especially where I've fixed version detection code.

If it can be done badly, it's going to be done badly.

> Moreover, even if somebody is (rather oddly, IMV) checking for
> command_tag == "COMMIT", it's not going to be a difficult change to
> check for command_tag == "COMMIT" or substr(command_tag,0,7) ==
> "COMMIT ". That's a level of incompatibility that would certainly
> deserve mention in the release notes, but it doesn't seem worth
> worrying more about than that.

I'm not convinced the relnotes get read by a lot of users. They're very
important for professional and/or serious admins an devs, but the number
of questions I see about stuff that's already highlighted in the
relnotes for a version suggests that it's not somewhere Joe Average goes
to find out what's going on.

Unfortunately.

As for whether relnoting it would be enough - that depends a great deal
on how exactly different clients failed. With the number of direct
protocol implementations out there, that's pretty hard to determine.

I'm somewhat confused by why messing with the commit command tag for all
clients is more acceptable than adding a new protocol message, enabled
only by clients that explicitly request it - and no, not by a GUC, I got
that message.

>> There's that, too. The whole proposal is a solution in search of a
>> problem at the moment, or maybe better to say that it's 1% of a solution
>> with no clear path to getting the other 99% done.
>
> Yeah, and I think that's the much more worrying problem.

It was just an RFC, and I certainly got the C, which I appreciate.

OTOH, knowing if/how inter-node catchup can be requested by a client is
a pre-req for designing anything else useful in this area IMO.

That part of the discussion has Tom's input to the effect that LSNs
shouldn't be exposed, but I don't think anybody's made any suggestions
about what could be acceptable or could work instead.

Server-reported timestamps would work if commit timestamp tracking is
merged.

As for being 1% of the problem, I disagree. I've already outlined some
simple use cases where having this information reported already provides
the client with all it needs. It'd only be a tiny part of fully
transparent client side failover, sure, but that's not something it's
realistic to aim for right from the start. Reporting of a position
indicator that can be used to see if replicas have replayed up to that
position is useful for simpler things too, and to allow clients
themselves to provide failover as part of application logic.

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

On Tue, Aug 19, 2014 at 1:21 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> There's plenty of agreement on "not a GUC" - but what about alternatives?

It could be a new protocol message. Currently there are no transaction
oriented protocol messages (other than the "transaction status" in
ReadyForQuery). But would it not make sense to have TransactionBegin,
TransactionCommit, and TransactionAbort in the protocol? Would that
make it easier for the client-side failover to keep track of what
transactions are pending or committed and need to be verified after a
failover?

--
greg

On 2014-08-19 08:21:10 +0800, Craig Ringer wrote:
> On 08/19/2014 01:03 AM, Robert Haas wrote:
> > 2. I agree that it's not good to have this get controlled by a GUC.
> > If the behavior change is big enough that it's going to break clients,
> > adding a GUC isn't a sufficient remedy. If it's not, adding a GUC is
> > unnecessary.
>
> There's plenty of agreement on "not a GUC" - but what about alternatives?

What's the problem with the COMMIT WITH (report_lsn on) I've proposed?
Reporting the LSN in the command tag? Anything doing transparent
failover needs to be aware of transaction boundaries anyway.

Greetings,

Andres Freund

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

On Tue, Aug 19, 2014 at 7:21 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-08-19 08:21:10 +0800, Craig Ringer wrote:
>> On 08/19/2014 01:03 AM, Robert Haas wrote:
>> > 2. I agree that it's not good to have this get controlled by a GUC.
>> > If the behavior change is big enough that it's going to break clients,
>> > adding a GUC isn't a sufficient remedy. If it's not, adding a GUC is
>> > unnecessary.
>>
>> There's plenty of agreement on "not a GUC" - but what about alternatives?
>
> What's the problem with the COMMIT WITH (report_lsn on) I've proposed?
> Reporting the LSN in the command tag? Anything doing transparent
> failover needs to be aware of transaction boundaries anyway.

So something like transparent failover doesn't work when a client is
working in auto commit mode? That sounds not good.

Just idea. What about using NoticeResponse message to report LSN?
It can be sent basically anytime and this idea doesn't break current
wire protocol.

Regards,

--
Fujii Masao

On 2014-08-19 19:59:51 +0900, Fujii Masao wrote:
> On Tue, Aug 19, 2014 at 7:21 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-08-19 08:21:10 +0800, Craig Ringer wrote:
> >> On 08/19/2014 01:03 AM, Robert Haas wrote:
> >> > 2. I agree that it's not good to have this get controlled by a GUC.
> >> > If the behavior change is big enough that it's going to break clients,
> >> > adding a GUC isn't a sufficient remedy. If it's not, adding a GUC is
> >> > unnecessary.
> >>
> >> There's plenty of agreement on "not a GUC" - but what about alternatives?
> >
> > What's the problem with the COMMIT WITH (report_lsn on) I've proposed?
> > Reporting the LSN in the command tag? Anything doing transparent
> > failover needs to be aware of transaction boundaries anyway.
>
> So something like transparent failover doesn't work when a client is
> working in auto commit mode? That sounds not good.

I don't think transparent failover + autocommit is a sensible
combination.

> Just idea. What about using NoticeResponse message to report LSN?
> It can be sent basically anytime and this idea doesn't break current
> wire protocol.

I think that'd be horrible from multiple perspectives: a) how to discern
them from regular notice messages b) It's not sent in the same protocol
level message as the COMMIT message. Thus there's scenarios where you
only have the commit, but not the LSN.

Greetings,

Andres Freund

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

On Tue, Aug 19, 2014 at 8:22 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-08-19 19:59:51 +0900, Fujii Masao wrote:
>> On Tue, Aug 19, 2014 at 7:21 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > On 2014-08-19 08:21:10 +0800, Craig Ringer wrote:
>> >> On 08/19/2014 01:03 AM, Robert Haas wrote:
>> >> > 2. I agree that it's not good to have this get controlled by a GUC.
>> >> > If the behavior change is big enough that it's going to break clients,
>> >> > adding a GUC isn't a sufficient remedy. If it's not, adding a GUC is
>> >> > unnecessary.
>> >>
>> >> There's plenty of agreement on "not a GUC" - but what about alternatives?
>> >
>> > What's the problem with the COMMIT WITH (report_lsn on) I've proposed?
>> > Reporting the LSN in the command tag? Anything doing transparent
>> > failover needs to be aware of transaction boundaries anyway.
>>
>> So something like transparent failover doesn't work when a client is
>> working in auto commit mode? That sounds not good.
>
> I don't think transparent failover + autocommit is a sensible
> combination.
>
>> Just idea. What about using NoticeResponse message to report LSN?
>> It can be sent basically anytime and this idea doesn't break current
>> wire protocol.
>
> I think that'd be horrible from multiple perspectives: a) how to discern
> them from regular notice messages

You can implement your own protocol upon existing messages like
replication is done.

b) It's not sent in the same protocol
> level message as the COMMIT message. Thus there's scenarios where you
> only have the commit, but not the LSN.

Hmm.. you can change the code so that the message with LSN is sent
as soon as COMMIT message is sent, if required.

Regards,

--
Fujii Masao

On 08/19/2014 06:21 PM, Andres Freund wrote:
> What's the problem with the COMMIT WITH (report_lsn on) I've proposed?
> Reporting the LSN in the command tag? Anything doing transparent
> failover needs to be aware of transaction boundaries anyway.

Tom's objection to a GUC applies there too - a client app can send that
when the underlying driver doesn't expect to get the results.

I'm not completely convinced that's a problem - oh dear, the app breaks.
The answer to so many other things in Pg is "well, don't do that then"
that I don't see this as overly different.

However, granting that it is a problem, the same objection to a GUC
applies to this too.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2014-08-19 21:47:26 +0800, Craig Ringer wrote:
> On 08/19/2014 06:21 PM, Andres Freund wrote:
> > What's the problem with the COMMIT WITH (report_lsn on) I've proposed?
> > Reporting the LSN in the command tag? Anything doing transparent
> > failover needs to be aware of transaction boundaries anyway.
>
> Tom's objection to a GUC applies there too - a client app can send that
> when the underlying driver doesn't expect to get the results.

I don't really think this is true. With a GUC it's set for the whole
session or even users. With such a option to COMMIT it'd only set when
issued by something that actually does transparent failover (i.e. the
underlying driver).

Greetings,

Andres Freund

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