Re: psql or pgbouncer bug?

Hi Joshua,

On 23/05/10 00:45, Joshua Tolley wrote:
> 2010/5/22 Tom Molesworth<tom(at)audioboundary(dot)com>:
>
>> 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). 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.
>>
> Does PostgreSQL log anything more detailed? What if you update some
> other table similarly? It looks like some sort of data corruption on
> the table you're updating.
>
Surely this is normal, expected behaviour - exactly the same as you'd
get from the mysql commandline client, for example? Perhaps my
explanation wasn't clear - here's an example session:

$ psql
psql (8.4.3, server 8.4.2)
Type "help" for help.

tom=# \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'PostgreSQL 8.4.3 on i486-pc-linux-gnu, compiled by GCC
gcc-4.4.real (Debian 4.4.3-3) 4.4.3, 32-bit'
DBNAME = 'tom'
USER = 'tom'
HOST = '/var/run/postgresql'
PORT = '5432'
ENCODING = 'UTF8'
tom=# create table test1 (id int, name varchar);
CREATE TABLE
tom=# insert into test1 values (1,'test');
INSERT 0 1
tom=# select name from test1;
name
------
test
(1 row)

At this point, identify the psql session via 'select * from
pg_stat_activity' or other means (in a different psql session), and
terminate the connection (kill PID). Now issue the following commands:

tom=# begin; update test1 set name = 'updated';
FATAL: terminating connection due to administrator command
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.
UPDATE 1
tom=# rollback;
NOTICE: there is no transaction in progress
ROLLBACK
tom=# select name from test1;
name
---------
updated
(1 row)

Since the default autocommit setting is enabled (at least under Ubuntu +
Debian psql 8.4 variants that I've tried), then on reconnect autocommit
is also enabled, and there is no 'begin', that never made it to the
server - the next statement is issued and committed immediately.

A simple way to avoid this in psql is to use '\set autocommit false'
instead of using 'begin' under autocommit - at least, that's always the
way I've done transactions there, issuing a separate 'begin' that could
get lost on connection drop just seems too risky to be worth considering.

Note that I'm not the original submitter - so I could be missing the
point entirely here!

Tom