Re: psql or pgbouncer bug?

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