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

Re: psql or pgbouncer bug?

From: Tom Molesworth <tom(at)audioboundary(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: psql or pgbouncer bug?
Date: 2010-05-24 15:22:31
Message-ID: 4BFA99B7.9010705@audioboundary.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi Jakub,

On 24/05/10 08:52, Jakub Ouhrabka wrote:
> > 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.
>
> So if I understand it correctly, if I need correct transaction 
> behaviour in psql even in case of disconnection the only safe way is 
> to use one statement per line.

You'd have to pay close attention to the responses if you go for that 
option, personally I wouldn't recommend it - much safer to use \set 
autocommit false, and that way you'll only ever get transactions 
committed when you explicitly issue a commit.

Since the connection could drop at any point during a psql session, the 
following sequence would also end up with some unwanted steps committed 
automatically:

begin;
update table set col = X;
-- connection drops after above two statements complete - not important 
whether they're on separate lines --
update table set col = Y; -- this statement will use current autocommit 
behaviour
rollback; -- "no transaction in progress" message if autocommit was enabled

If you happen to miss the reconnection message during the above 
sequence, you'll inadvertently be back in autocommit mode - so the 3rd 
statement will be committed immediately.

Compare this to:

\set autocommit false
update table set col = X;
update table set col = Y;
rollback;

If the connection drops at any point before or after those statements, 
the new connection will still be in transactional (manual commit) mode, 
so there's no chance of any of the above statements being committed 
(either the rollback on disconnect, or the explicit rollback will take 
place).

Personally I always use '\set autocommit false' under psql, since it's 
closer in behaviour to the Perl DBI ->connect(... { AutoCommit => 0 }) 
behaviour I'm used to. I'd definitely never risk using 'begin' in psql 
with multiple statements.

Tom


In response to

Responses

pgsql-bugs by date

Next:From: Randy SolomonsonDate: 2010-05-24 16:48:07
Subject: BUG #5470: EXTRACT(epoch from ...) missing last digit
Previous:From: Tom LaneDate: 2010-05-24 13:50:55
Subject: Re: psql or pgbouncer bug?

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