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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs