Re: psql or pgbouncer bug?

Added to TODO:

Prevent psql from sending remaining single-line multi-statement queries
after reconnection

* http://archives.postgresql.org/pgsql-bugs/2010-05/msg00159.php
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg01283.php

---------------------------------------------------------------------------

Tom Molesworth wrote:
> 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
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com