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

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

Sean Chittenden <sean@chittenden.org> writes:

Does turnning autocommit off enter you into a transaction? Am I
smoking something or does that seems broken?

I don't like it either, but Bruce is objecting to changing it.

regards, tom lane

Sean Chittenden wrote:

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.

OK, but why does my suggestion not work:

SET autocommit = ON;
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.

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

Yep, we don't like special cases and that is why we avoided it. Just
explaining the special case causes all sorts of confusion, as you have
seen from my emails.

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

Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Does turnning autocommit off enter you into a transaction? Am I
smoking something or does that seems broken?

I don't like it either, but Bruce is objecting to changing it.

Tom, do you want to special case autocommit? I think that would be OK.

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

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.

OK, but why does my suggestion not work:

SET autocommit = ON;
COMMIT;

Hrm... if I changed the DBI layer for Ruby to have:

db['AutoCommit'] = true

use 'SET autocommit = ON; COMMIT;' I think I'd be breaking tons of
applications where they wouldn't be expecting the commit.

Yep, we don't like special cases and that is why we avoided it. Just
explaining the special case causes all sorts of confusion, as you have
seen from my emails.

Yup, exceptions aren't elegant, but since there's only one way of
SET'ting variables and this one is very key to transactions, I don't
know of another way than possibly creating a parallel command to SET
that'd avoid this rollback/commit silliness... but that seems like a
step backwards and is why I'd think an exception would be good. -sc

--
Sean Chittenden

Sean Chittenden wrote:

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.

OK, but why does my suggestion not work:

SET autocommit = ON;
COMMIT;

Hrm... if I changed the DBI layer for Ruby to have:

db['AutoCommit'] = true

use 'SET autocommit = ON; COMMIT;' I think I'd be breaking tons of
applications where they wouldn't be expecting the commit.

Actually, the current approved way is:

BEGIN; SET autocommit = ON; COMMIT;

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

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.

OK, but why does my suggestion not work:

SET autocommit = ON;
COMMIT;

Hrm... if I changed the DBI layer for Ruby to have:

db['AutoCommit'] = true

use 'SET autocommit = ON; COMMIT;' I think I'd be breaking tons of
applications where they wouldn't be expecting the commit.

Actually, the current approved way is:

BEGIN; SET autocommit = ON; COMMIT;

db.transaction do |dbh|
db.do('DELETE FROM tbl WHERE id = 5')
db['AutoCommit'] = true
end

Because there wasn't a commit given, that shouldn't actually delete
the rows found, but by tossing that AutoCommit in there, it should and
will generate a nifty warning if AutoCommit sends the above
BEGIN/SET/COMMIT. -sc

--
Sean Chittenden

Sean Chittenden wrote:

db.transaction do |dbh|
db.do('DELETE FROM tbl WHERE id = 5')
db['AutoCommit'] = true
end

Because there wasn't a commit given, that shouldn't actually delete
the rows found, but by tossing that AutoCommit in there, it should and
will generate a nifty warning if AutoCommit sends the above
BEGIN/SET/COMMIT. -sc

You can't be setting autocommit willy-nilly. What I was going to
suggest is that we allow 'SET autocommit' only at the start of a
transaction, and then have it take effect immediately. If you try
autocommit when a transaction is already in progress from a previous
statement, we throw an error.

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Does turnning autocommit off enter you into a transaction? Am I
smoking something or does that seems broken?

Tom, do you want to special case autocommit? I think that would be OK.

No, I don't like that either ... in general I do not think SET's
behavior should vary depending on which variable is being set.

regards, tom lane

db.transaction do |dbh|
db.do('DELETE FROM tbl WHERE id = 5')
db['AutoCommit'] = true
end

Because there wasn't a commit given, that shouldn't actually
delete the rows found, but by tossing that AutoCommit in there, it
should and will generate a nifty warning if AutoCommit sends the
above BEGIN/SET/COMMIT. -sc

You can't be setting autocommit willy-nilly. What I was going to
suggest is that we allow 'SET autocommit' only at the start of a
transaction, and then have it take effect immediately. If you try
autocommit when a transaction is already in progress from a previous
statement, we throw an error.

But that'd result in at least two transactions per connection because
in my database class wrapper I turn autocommit off. Under any kind of
load or performance situations, that's pretty unacceptable. Granted
there's nothing that would need to be flushed to disk (hopefully), it
still strikes me that there would have to be some locking involved and
that would degrade the performance of the entire system.

If you're throwing an error in the middle of a transaction just
because of 'SET autocommit', aren't you already making an exception
and one that degrades the performance of the entire system as a
result?

I just saw Tom's post and it seems like something has to give
someplace... I'm not a fan of the idea of creating the special case,
don't get me wrong, but is there a reasonable alternative? -sc

--
Sean Chittenden

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Does turnning autocommit off enter you into a transaction? Am I
smoking something or does that seems broken?

Tom, do you want to special case autocommit? I think that would be OK.

No, I don't like that either ... in general I do not think SET's
behavior should vary depending on which variable is being set.

Yep, this is where we got lost before. You don't want to special case
SET variables, but you _do_ want to special case SET at the start of a
transaction. Did you see my timeout example? How is that supposed to
be handled cleanly?

SET statement_timeout = 20;
query generates error;
SET statement_timeout = 0;
COMMIT;

If the first SET doesn't start a transaction and isn't part of the
transaction, I don't see how to do this. Maybe:

BEGIN;
SET statement_timeout = 20;
query generates error;
SET statement_timeout = 0;
COMMIT;

So then you have to train people that their initial SET isn't part of
the transaction, though the later one is. Yuck.

I think we diverted from the spec when we went with making SET
rollbackable and now we are seeing the problems caused.

Why exactly did you want the initial SET to not be part of the
transaction?

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

Sean Chittenden wrote:

db.transaction do |dbh|
db.do('DELETE FROM tbl WHERE id = 5')
db['AutoCommit'] = true
end

Because there wasn't a commit given, that shouldn't actually
delete the rows found, but by tossing that AutoCommit in there, it
should and will generate a nifty warning if AutoCommit sends the
above BEGIN/SET/COMMIT. -sc

You can't be setting autocommit willy-nilly. What I was going to
suggest is that we allow 'SET autocommit' only at the start of a
transaction, and then have it take effect immediately. If you try
autocommit when a transaction is already in progress from a previous
statement, we throw an error.

But that'd result in at least two transactions per connection because
in my database class wrapper I turn autocommit off. Under any kind of
load or performance situations, that's pretty unacceptable. Granted
there's nothing that would need to be flushed to disk (hopefully), it
still strikes me that there would have to be some locking involved and
that would degrade the performance of the entire system.

You would never see a performance hit. It doesn't dirty any buffers or
anything. Heck, a SET with autocommit on is already in its own
transaction.

If you're throwing an error in the middle of a transaction just
because of 'SET autocommit', aren't you already making an exception
and one that degrades the performance of the entire system as a
result?

I think if we special case autocommit we have to force it to start a
transaction.

I just saw Tom's post and it seems like something has to give
someplace... I'm not a fan of the idea of creating the special case,
don't get me wrong, but is there a reasonable alternative? -sc

I am willing to special case autocommit because it is so tied to
transactions anyway.

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

Does turnning autocommit off enter you into a transaction? Am I
smoking something or does that seems broken?

Tom, do you want to special case autocommit? I think that would be OK.

No, I don't like that either ... in general I do not think SET's
behavior should vary depending on which variable is being set.

Yep, this is where we got lost before. You don't want to special case
SET variables, but you _do_ want to special case SET at the start of a
transaction. Did you see my timeout example? How is that supposed to
be handled cleanly?

SET statement_timeout = 20;
query generates error;
SET statement_timeout = 0;
COMMIT;

If the first SET doesn't start a transaction and isn't part of the
transaction, I don't see how to do this. Maybe:

BEGIN;
SET statement_timeout = 20;
query generates error;
SET statement_timeout = 0;
COMMIT;

So then you have to train people that their initial SET isn't part of
the transaction, though the later one is. Yuck.

I think we diverted from the spec when we went with making SET
rollbackable and now we are seeing the problems caused.

Why exactly did you want the initial SET to not be part of the
transaction?

Is having an exception all that bad? What other tunables should be
outside of the reach of transactions? Maybe an exception should be
applied to a class of SET tunables. -sc

--
Sean Chittenden

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Why exactly did you want the initial SET to not be part of the
transaction?

Primarily because existing client-side libraries issue lots of SETs
while starting a connection.

We may just have to say "those guys are broken if you turn off
autocommit in postgresql.conf", but I am looking for a way around it.
If SET didn't start a transaction then they wouldn't be broken...

regards, tom lane

Sean Chittenden wrote:

Why exactly did you want the initial SET to not be part of the
transaction?

Is having an exception all that bad? What other tunables should be
outside of the reach of transactions? Maybe an exception should be
applied to a class of SET tunables. -sc

I am fine with exceptions _if_ we force them to start a transaction,
meaning they are their own transactions basically.

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

But that'd result in at least two transactions per connection because
in my database class wrapper I turn autocommit off. Under any kind of
load or performance situations, that's pretty unacceptable. Granted
there's nothing that would need to be flushed to disk (hopefully), it
still strikes me that there would have to be some locking involved and
that would degrade the performance of the entire system.

You would never see a performance hit. It doesn't dirty any buffers or
anything. Heck, a SET with autocommit on is already in its own
transaction.

Alright, that's good to hear... I'd think there'd be some locking
that'd have to take place to start/commit the transaction which
wouldn't necessarily be tied to IO performance so much as CPU
performance... but I'm not that knowledgable of the internals.

Regardless, however, it's still a usability issue, IMHO.

I just saw Tom's post and it seems like something has to give
someplace... I'm not a fan of the idea of creating the special case,
don't get me wrong, but is there a reasonable alternative? -sc

I am willing to special case autocommit because it is so tied to
transactions anyway.

Does postgresql support subtransactions? I thought only OODBs had
this and that relational DBs don't by and large, PostgreSQL included.
I personally am not haphazardous with setting autocommit, but users
who use any kind of interface are likely to screw things up and
scratch their head wondering why. Why would this have to be in its
own transaction? -sc

--
Sean Chittenden

Sean Chittenden wrote:

But that'd result in at least two transactions per connection because
in my database class wrapper I turn autocommit off. Under any kind of
load or performance situations, that's pretty unacceptable. Granted
there's nothing that would need to be flushed to disk (hopefully), it
still strikes me that there would have to be some locking involved and
that would degrade the performance of the entire system.

You would never see a performance hit. It doesn't dirty any buffers or
anything. Heck, a SET with autocommit on is already in its own
transaction.

Alright, that's good to hear... I'd think there'd be some locking
that'd have to take place to start/commit the transaction which
wouldn't necessarily be tied to IO performance so much as CPU
performance... but I'm not that knowledgable of the internals.

Regardless, however, it's still a usability issue, IMHO.

Yep.

I just saw Tom's post and it seems like something has to give
someplace... I'm not a fan of the idea of creating the special case,
don't get me wrong, but is there a reasonable alternative? -sc

I am willing to special case autocommit because it is so tied to
transactions anyway.

Does postgresql support subtransactions? I thought only OODBs had
this and that relational DBs don't by and large, PostgreSQL included.
I personally am not haphazardous with setting autocommit, but users
who use any kind of interface are likely to screw things up and
scratch their head wondering why. Why would this have to be in its
own transaction? -sc

We want subtransactions but we don't have them yet.

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

Thomas Lockhart wrote:

...

I think if we special case autocommit we have to force it to start a
transaction.

Be aware that "SET AUTOCOMMIT" does *not* start a transaction in other
systems (at least in Ingres, where I first ran into the feature).

This case is illustrating a general issue with trying to bracket
variables within transactions; the "special case" is that if a
transaction is not open then the change should be global across
transactions.

Any counterexamples would argue for two separate behaviors, not for
shoehorning everything into one, uh, shoe.

I am fine with special casing autocommit. Is that what you are
suggesting?

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