Re: SET autocommit begins transaction?

> > 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.

Problem is that through various DB APIs such as DBI, you can't
garuntee to the user doing development that that it's the 1st command
that they're performing.

> 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;

Hrm... SET autocommit is an interesting one then because unlike the
other components that can be used, autocommit directly speaks to the
style of transactions.

How about this logic:

*) SET autocommit will not start a transaction

*) If a SET autocommit is issued inside of a transaction, its value can be rolled back.

I can't think of an instance where that'd be the wrong thing and that
should be a small change to make.

> 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.

I'm inclined to think that SET needs an exception for autocommit... I
don't like exceptions, but I can't think of another SET that you'd do
where you wouldn't want to roll it back. Eh? -sc

--
Sean Chittenden