Re: psql or pgbouncer bug?

Tom Molesworth <tom@audioboundary.com> writes:
> I don't know anything about psql internals, but at a guess the sequence
> is this:
> * 'begin' is sent to server
> * Connection is dropped
> * Connection is reset, but 'begin' is not resent
> * Next statement (the update) is sent to the server, executes immediately
> * Rollback gives error since there was no corresponding begin

> Seems to be trivially easy to reproduce by connecting via psql, then
> killing that connection before issuing the 'begin; update' sequence
> (against postgres directly, no pgbouncer needed).

Yeah, confirmed here.  A simple example is:

regression=# select 2+2; select 4+4;
 ?column?
----------
        4
(1 row)

 ?column?
----------
        8
(1 row)


If I now kill -9 the connected backend and repeat, I get this instead:

regression=# select 2+2; select 4+4;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
 ?column?
----------
        8
(1 row)


So that explains Jakub's observed behavior without having to make any
strenuous assumptions about the connection being dropped at just the
right instant --- any time while he was typing the line would do it.

> If anything, it's an
> issue with psql settings? Maybe it should stop on connection drop rather
> than attempting reconnect and continuing with further statements.

The auto-reconnect behavior is long-established and desirable.  What's
not desirable is continuing with any statements remaining on the same
line, I think.  We need to flush the input buffer on reconnect.

            regards, tom lane