Re: SET autocommit begins transaction?

Sean Chittenden wrote:
-- Start of PGP signed section.
> Here's the simplest way of reproducing this:
>
> ways# psql -q template1 pgsql
> template1=# SET AUTOCOMMIT TO OFF;
> template1=# DROP DATABASE my_db_name;
> ERROR: DROP DATABASE: may not be called in a transaction block
>
> 2002-09-18 11:05:19 LOG: query: select getdatabaseencoding()
> 2002-09-18 11:05:19 LOG: query: SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'pgsql'
> 2002-09-18 11:05:30 LOG: query: SET AUTOCOMMIT TO OFF;
> 2002-09-18 11:05:38 LOG: query: DROP DATABASE my_db_name;
> 2002-09-18 11:05:38 ERROR: DROP DATABASE: may not be called in a transaction block
> 2002-09-18 11:05:38 LOG: statement: DROP DATABASE my_db_name;
>
>
> Does turnning autocommit off enter you into a transaction? Am I
> smoking something or does that seems broken? It looks like this was a

Well there is discussion on whether a SET with autocommit off should
start a transaction if it is the first command. Right now it does, and
clearly you have a case where it acts strangely.

What has really made this unchangable is the fact that in 7.3 SET is
rolled back if the transaction aborts, so it is part of the transaction
semantics. If we make SET not start a transaction, then those SET's
wouldn't be rolled back, making a quite confusing case:

SET statement_timeout = 20; -- let's suppose this doesn't start an xact
query_generating_an_error;
SET statement_timeout=0;
COMMIT;

This would not rollback the first SET because it wouldn't be part of
that transaction, causing all sorts of confusion.

I assume the way to code your case is:

> template1=# SET AUTOCOMMIT TO OFF;
> template1=# COMMIT;
> template1=# DROP DATABASE my_db_name;

because in fact the SET doesn't become permanent until the COMMIT is
performed.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073