Thread: SET autocommit begins transaction?
Here's the simplest way of reproducing this:
ways# psql -q template1 pgsql
template1=3D# SET AUTOCOMMIT TO OFF;
template1=3D# 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 WH=
ERE usename =3D '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 transacti=
on 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
conscious and deliberate decission based off of the comments in
src/backend/access/transam/xact.c around lines 1248-1293. In my
reading of the code, I might be confusing the GUC autocommit with the
SET autocommit, but ... this just doesn't seem right because it
forces my application code to do the following:
db =3D MyOrg::Db.connect('init')
db.rollback
db.do('DROP DATABASE my_db_name')
which reads really awkwardly and warrents a comment explaining why I'm
rolling back immediately after I connect. Thoughts/comments? -sc
--=20
Sean Chittenden
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 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
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
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
> > ... > > > 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? I think he means: Ex: SET autocommit TO off; SHOW autocommit; ROLLBACK; # warning about being outside of a transaction BEGIN; SET autocommit TO on; SHOW autocommit; # shows on ROLLBACK; SHOW autocommit; # shows off Only have the SET's in a transaction/rollback-able if they're made inside of a transaction, otherwise leave them as atomic changes. -sc -- Sean Chittenden
Sean Chittenden wrote: > > I am fine with special casing autocommit. Is that what you are > > suggesting? > > I think he means: > > Ex: > SET autocommit TO off; > SHOW autocommit; > ROLLBACK; > # warning about being outside of a transaction > BEGIN; > SET autocommit TO on; > SHOW autocommit; # shows on > ROLLBACK; > SHOW autocommit; # shows off > > Only have the SET's in a transaction/rollback-able if they're made > inside of a transaction, otherwise leave them as atomic changes. -sc But it seems so illogical that SET doesn't start a transaction, but if it is in a transaction, it is rolled back, and this doesn't help our statement_timeout example except to require that they do BEGIN to start the transaction even when autocommit is off. -- 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: > 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... OK, I can work with this. If we assume the startup is the first statement issued, then we could tell them they have to SET autocommit to on first before doing anything else. However, if you believe there could have been other SET's before the startup stuff, my idea doesn't work because autocommit has to be at the start of a transaction. If you think there could have been other non-SET queries before the startup script, then all of our ideas don't work because the SET would already be in a 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
> > > I am fine with special casing autocommit. Is that what you are > > > suggesting? > > > > I think he means: > > > > Ex: > > SET autocommit TO off; > > SHOW autocommit; > > ROLLBACK; > > # warning about being outside of a transaction > > BEGIN; > > SET autocommit TO on; > > SHOW autocommit; # shows on > > ROLLBACK; > > SHOW autocommit; # shows off > > > > Only have the SET's in a transaction/rollback-able if they're made > > inside of a transaction, otherwise leave them as atomic changes. -sc > > But it seems so illogical that SET doesn't start a transaction, but > if it is in a transaction, it is rolled back, and this doesn't help > our statement_timeout example except to require that they do BEGIN > to start the transaction even when autocommit is off. Really? To me that makes perfect sense. Logic: *) Only BEGIN starts a transaction *) Database or session tunables are adjusted with SET *) Only things that happen inside of a transaction are rollback-able *) SET operations that happen outside of a transaction are atomic changes that aren't subject to being rolled back What about that doesn't make sense? Having SET begin a transaction seems like a gross violation of POLS and likely to contradict the spec and cause problems with many applications. -sc -- Sean Chittenden
Sean Chittenden wrote:
> > But it seems so illogical that SET doesn't start a transaction, but
> > if it is in a transaction, it is rolled back, and this doesn't help
> > our statement_timeout example except to require that they do BEGIN
> > to start the transaction even when autocommit is off.
>
> Really? To me that makes perfect sense. Logic:
>
> *) Only BEGIN starts a transaction
I think the above item is the issue. Everything is clear with
autocommit on. With autocommit off, COMMIT/ROLLBACK starts a
transaction, not BEGIN. BEGIN _can_ start a transaction, but it isn't
required:
COMMIT;
SET x=1;
SELECT ...;
SET x=2;
By making SET not start a transaction, x=2 is in a transaction, while
x=1 is not.
> *) Database or session tunables are adjusted with SET
> *) Only things that happen inside of a transaction are rollback-able
> *) SET operations that happen outside of a transaction are atomic
> changes that aren't subject to being rolled back
We have _zero_ cases right now where something happens outside a
transaction. In fact, everything is in a transaction. In fact, with
autocommit on you can't get out of a transaction. The idea of not
having SET start a transaction adds this pseudo state where we accept
SET but are not in a transaction until a non-SET occurs. That's the
strange part for me.
>
> What about that doesn't make sense? Having SET begin a transaction
> seems like a gross violation of POLS and likely to contradict the spec
> and cause problems with many applications. -sc
I think we left the standard when we made SET rollbackable. Maybe that
has to be reopened because if we did that, it would make perfect sense
because all SETs would be outside transactions.
--
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
On Wed, 18 Sep 2002, Bruce Momjian wrote: > Sean Chittenden wrote: > > > But it seems so illogical that SET doesn't start a transaction, but > > > if it is in a transaction, it is rolled back, and this doesn't help > > > our statement_timeout example except to require that they do BEGIN > > > to start the transaction even when autocommit is off. > > > > Really? To me that makes perfect sense. Logic: > > > > *) Only BEGIN starts a transaction > > I think the above item is the issue. Everything is clear with > autocommit on. With autocommit off, COMMIT/ROLLBACK starts a > transaction, not BEGIN. BEGIN _can_ start a transaction, but it isn't > required: AFAICT, according to spec, commit/rollback does not start a transaction, the transcation is started with the first transaction initiating statement when there isn't a current transaction. And, most of the SQL92 commands that start with SET fall into the category of commands that do not initiate transactions.
Stephan Szabo wrote: > On Wed, 18 Sep 2002, Bruce Momjian wrote: > > > Sean Chittenden wrote: > > > > But it seems so illogical that SET doesn't start a transaction, but > > > > if it is in a transaction, it is rolled back, and this doesn't help > > > > our statement_timeout example except to require that they do BEGIN > > > > to start the transaction even when autocommit is off. > > > > > > Really? To me that makes perfect sense. Logic: > > > > > > *) Only BEGIN starts a transaction > > > > I think the above item is the issue. Everything is clear with > > autocommit on. With autocommit off, COMMIT/ROLLBACK starts a > > transaction, not BEGIN. BEGIN _can_ start a transaction, but it isn't > > required: > > AFAICT, according to spec, commit/rollback does not start a transaction, > the transcation is started with the first transaction initiating statement > when there isn't a current transaction. And, most of the SQL92 commands > that start with SET fall into the category of commands that do not > initiate transactions. OK, I am ready to say I was wrong. Most people like that behavior so let's do it. Thanks for listening to me. -- 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
> > What about that doesn't make sense? Having SET begin a transaction > > seems like a gross violation of POLS and likely to contradict the spec > > and cause problems with many applications. -sc > > I think we left the standard when we made SET rollbackable. Maybe that > has to be reopened because if we did that, it would make perfect sense > because all SETs would be outside transactions. Of course, the reason they're rollbackable is: begin; create schema newschema; set search_path = newschema; rollback; create table junk; -- DOH! -- Rod Taylor
Rod Taylor wrote:
> > > What about that doesn't make sense? Having SET begin a transaction
> > > seems like a gross violation of POLS and likely to contradict the spec
> > > and cause problems with many applications. -sc
> >
> > I think we left the standard when we made SET rollbackable. Maybe that
> > has to be reopened because if we did that, it would make perfect sense
> > because all SETs would be outside transactions.
>
> Of course, the reason they're rollbackable is:
>
> begin;
> create schema newschema;
> set search_path = newschema;
> rollback;
>
> create table junk; -- DOH!
And:
set statement_timeout = 20;
query_with_error;
set statement_timeout = 0;
COMMIT;
That will have to change in autocommit off to:
BEGIN;
SET statement_timeout = 20;
query_with_error;
SET statement_timeout = 0;
COMMIT;
I assume that BEGIN does start a transaction. With no BEGIN above, the
big problem is that it will work most of the time, but when/if the query
fails, they will find out they forgot the BEGIN.
--
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
> > Of course, the reason they're rollbackable is: > > > > begin; > > create schema newschema; > > set search_path = newschema; > > rollback; > > > > create table junk; -- DOH! > > And: > > set statement_timeout = 20; > query_with_error; > set statement_timeout = 0; > COMMIT; > > That will have to change in autocommit off to: > > BEGIN; > SET statement_timeout = 20; > query_with_error; > SET statement_timeout = 0; > COMMIT; > > I assume that BEGIN does start a transaction. With no BEGIN above, the > big problem is that it will work most of the time, but when/if the query > fails, they will find out they forgot the BEGIN. Wouldn't it roll back to 0 though because the SET statement_timeout TO 20 was inside of a transaction (assuming the value was 0 before the transaction began)? -sc -- Sean Chittenden
Sean Chittenden wrote: > > > Of course, the reason they're rollbackable is: > > > > > > begin; > > > create schema newschema; > > > set search_path = newschema; > > > rollback; > > > > > > create table junk; -- DOH! > > > > And: > > > > set statement_timeout = 20; > > query_with_error; > > set statement_timeout = 0; > > COMMIT; > > > > That will have to change in autocommit off to: > > > > BEGIN; > > SET statement_timeout = 20; > > query_with_error; > > SET statement_timeout = 0; > > COMMIT; > > > > I assume that BEGIN does start a transaction. With no BEGIN above, the > > big problem is that it will work most of the time, but when/if the query > > fails, they will find out they forgot the BEGIN. > > Wouldn't it roll back to 0 though because the SET statement_timeout TO > 20 was inside of a transaction (assuming the value was 0 before the > transaction began)? -sc Yes, with the BEGIN, it will roll back. With autocommit off, this: > > > > set statement_timeout = 20; > > query_with_error; > > set statement_timeout = 0; > > COMMIT; will not roll back to 0. It will be 20. -- 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
> Yes, with the BEGIN, it will roll back. With autocommit off, this: > > > > > > > set statement_timeout = 20; > > > query_with_error; > > > set statement_timeout = 0; > > > COMMIT; > > will not roll back to 0. It will be 20. But that's the correct/expected behavior, is it not? That's what I'd expect at least. I'd think it's a gotcha for those that aren't good about explicitly calling BEGIN, but most libraries should do that for you, ruby-dbi does and used to be overly zealous about that actually (I just fixed that last night as a matter of fact). -sc -- Sean Chittenden
Sean Chittenden wrote: > > Yes, with the BEGIN, it will roll back. With autocommit off, this: > > > > > > > > > > set statement_timeout = 20; > > > > query_with_error; > > > > set statement_timeout = 0; > > > > COMMIT; > > > > will not roll back to 0. It will be 20. > > But that's the correct/expected behavior, is it not? That's what I'd > expect at least. I'd think it's a gotcha for those that aren't good > about explicitly calling BEGIN, but most libraries should do that for > you, ruby-dbi does and used to be overly zealous about that actually > (I just fixed that last night as a matter of fact). -sc I thought the whole idea of autocommit off was not having to call BEGIN? Now you do if you want your SETs to be in your transaction. That's pretty clear and can be explained in the SGML docs with autocommit. -- 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 it seems so illogical that SET doesn't start a transaction, but > > > > if it is in a transaction, it is rolled back, and this doesn't help > > > > our statement_timeout example except to require that they do BEGIN > > > > to start the transaction even when autocommit is off. > > > > > > Really? To me that makes perfect sense. Logic: > > > > > > *) Only BEGIN starts a transaction > > > > I think the above item is the issue. Everything is clear with > > autocommit on. With autocommit off, COMMIT/ROLLBACK starts a > > transaction, not BEGIN. BEGIN _can_ start a transaction, but it isn't > > required: > > AFAICT, according to spec, commit/rollback does not start a > transaction, the transcation is started with the first transaction > initiating statement when there isn't a current transaction. And, > most of the SQL92 commands that start with SET fall into the > category of commands that do not initiate transactions. Was there any resolution to this or are SET's still starting a new transaction? I haven't seen any commits re: this, iirc. -sc -- Sean Chittenden
Sean Chittenden wrote: > > > > > But it seems so illogical that SET doesn't start a transaction, but > > > > > if it is in a transaction, it is rolled back, and this doesn't help > > > > > our statement_timeout example except to require that they do BEGIN > > > > > to start the transaction even when autocommit is off. > > > > > > > > Really? To me that makes perfect sense. Logic: > > > > > > > > *) Only BEGIN starts a transaction > > > > > > I think the above item is the issue. Everything is clear with > > > autocommit on. With autocommit off, COMMIT/ROLLBACK starts a > > > transaction, not BEGIN. BEGIN _can_ start a transaction, but it isn't > > > required: > > > > AFAICT, according to spec, commit/rollback does not start a > > transaction, the transcation is started with the first transaction > > initiating statement when there isn't a current transaction. And, > > most of the SQL92 commands that start with SET fall into the > > category of commands that do not initiate transactions. > > Was there any resolution to this or are SET's still starting a new > transaction? I haven't seen any commits re: this, iirc. -sc It is still an open item, but I think there was agreement that SET will not start a transaction, and we will document that. -- 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 P O S T G R E S Q L 7 . 3 O P E N I T E M S Current at ftp://momjian.postgresql.org/pub/postgresql/open_items. Source Code Changes ------------------- Schema handling - ready? interfaces? client apps? Drop column handling - ready for all clients, apps? Fix BeOS, QNX4 ports Fix AIX large file compile failure of 2002-09-11 (Andreas) Get bison upgrade on postgresql.org for ecpg only (Marc) Fix vacuum btree bug (Tom) Fix client apps for autocommit = off Change log_min_error_statement to be off by default (Gavin) Fix return tuple counts/oid/tag for rules Add schema dump option to pg_dump Make SET not start a transaction with autocommit off, document it Remove GRANT EXECUTE to all /contrib functions? On Going -------- Security audit Documentation Changes --------------------- Document need to add permissions to loaded functions and languages Move documation to gborg for moved projects
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Sean Chittenden wrote:
>> Was there any resolution to this or are SET's still starting a new
>> transaction? I haven't seen any commits re: this, iirc. -sc
> It is still an open item, but I think there was agreement that SET will
> not start a transaction, and we will document that.
There was? I thought you were resisting it tooth and nail ;-)
If you're willing to accept this behavior, I shall make it happen.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Sean Chittenden wrote: > >> Was there any resolution to this or are SET's still starting a new > >> transaction? I haven't seen any commits re: this, iirc. -sc > > > It is still an open item, but I think there was agreement that SET will > > not start a transaction, and we will document that. > > There was? I thought you were resisting it tooth and nail ;-) > > If you're willing to accept this behavior, I shall make it happen. Sure. I posted this on September 18: > OK, I am ready to say I was wrong. Most people like that behavior so > let's do it. Thanks for listening to me. I took my best shot but most people disagreed, so I am ready to move forward. I only ask that the behavior of SET be documented where we document autocommit so it doesn't trip anyone up. -- 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