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
