Skip site navigation (1) Skip section navigation (2)

Re: psql or pgbouncer bug?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tom Molesworth <tom(at)audioboundary(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: psql or pgbouncer bug?
Date: 2010-05-23 18:38:53
Message-ID: 18470.1274639933@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom Molesworth <tom(at)audioboundary(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: Jehan-Guillaume (ioguix) de RorthaisDate: 2010-05-23 20:11:22
Subject: ERROR: failed to build any 3-way joins
Previous:From: Tom LaneDate: 2010-05-23 16:58:07
Subject: Re: BUG #5468: Pg doesn't send accepted root CA list to client during SSL client cert request

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group