To improve handling of autocommit off mode, I am proposing a change in
our next release. Right now, if you pass multiple queries in the same
string to the backend:
psql -c 'INSERT INTO test VALUES (1);INSERT INTO test VALUES (2);" template1
the query is considered to be a single transaction. (Of course, this
doesn't make a lot of sense, but historically, this is how PostgreSQL
has behaved.) This causes confusion with autocommit off (and probably
confuses users too):
psql -c 'SET autocommit TO off;SELECT 1;COMMIT" template1
Right now, this will generate an error because of the hack used to get
everything in a single transaction. In our next release, this will work
fine, and sending multiple queries in a single string will be treated as
though each command was issued singly. If you need the old behavior,
you need to use BEGIN/COMMIT around you query:
psql -c 'BEGIN; INSERT INTO test VALUES (1);INSERT INTO test VALUES (2);COMMIT;" template1
(Psql already automatically breaks up queries passed on stdin.)
Is this OK with everyone?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073