Re: [NOVICE] [BUGS] Postgres storing time in strange manner
Out of curiosity: why does -ffast-math break the datetime rounding code? What code bits is this for? Is there a place where -fno-fast-math could be used as a CC option if the CC is gcc? After looking through gcc, using -O and -ffast-math will create broken code, but -O2 -ffast-math _should_ be okay. If it's not, then -O2 -fno-fast-math is likely the correct work around for GCC. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [NOVICE] [BUGS] Postgres storing time in strange manner
Sean Chittenden [EMAIL PROTECTED] writes:
Is there a place where -fno-fast-math
could be used as a CC option if the CC is gcc?
configure is what I had in mind ;-). I can't think of any part of the
code where we'd really want this sort of optimization enabled.
After looking through gcc, using -O and -ffast-math will create broken
code, but -O2 -ffast-math _should_ be okay.
At least in the gcc shipped with Red Hat 7.2, it doesn't seem to matter:
you get the wrong answer regardless of -O level. Here's the test case
I used:
[tgl@rh1 tgl]$ cat bug.c
#include stdio.h
double d18000 = 18000.0;
main() {
int d = d18000 / 3600;
printf(18000.0 / 3600 = %d\n, d);
return 0;
}
[tgl@rh1 tgl]$ gcc bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 5 -- right
[tgl@rh1 tgl]$ gcc -O2 -ffast-math bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 4 -- wrong
-- I get 4 if -ffast-math, -O doesn't affect it
[tgl@rh1 tgl]$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] 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
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 = 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
--
Sean Chittenden
msg04859/pgp0.pgp
Description: PGP signature
Re: [NOVICE] [BUGS] Postgres storing time in strange manner
After looking through gcc, using -O and -ffast-math will create broken
code, but -O2 -ffast-math _should_ be okay.
At least in the gcc shipped with Red Hat 7.2, it doesn't seem to matter:
you get the wrong answer regardless of -O level. Here's the test case
I used:
[tgl@rh1 tgl]$ cat bug.c
#include stdio.h
double d18000 = 18000.0;
main() {
int d = d18000 / 3600;
printf(18000.0 / 3600 = %d\n, d);
return 0;
}
[tgl@rh1 tgl]$ gcc bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 5-- right
[tgl@rh1 tgl]$ gcc -O2 -ffast-math bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 4-- wrong
-- I get 4 if -ffast-math, -O doesn't affect it
[tgl@rh1 tgl]$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)
Heh, chalk this one up as another Linux-ism then 'cause it's not
present in FreeBSD -stable or -current. This actually makes me feel
better about setting an option in the -devel port for turning on
compilation with -O3. -sc
stable$ gcc -v
Using builtin specs.
gcc version 2.95.4 20020320 [FreeBSD]
current$ gcc -v
Using built-in specs.
Configured with: FreeBSD/i386 system compiler
Thread model: posix
gcc version 3.2.1 [FreeBSD] 20020901 (prerelease)
--
Sean Chittenden
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [NOVICE] [BUGS] Postgres storing time in strange manner
Tom Lane writes: Sean Chittenden [EMAIL PROTECTED] writes: Is there a place where -fno-fast-math could be used as a CC option if the CC is gcc? configure is what I had in mind ;-). I can't think of any part of the code where we'd really want this sort of optimization enabled. Today I read that __FAST_MATH__ is defined if -ffast-math is used, so it should be easy to write a test in configure. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] SET autocommit begins transaction?
Sean Chittenden [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Bug #772: Rewriting on multi-record updates is
On Wed, 2002-09-18 at 17:08, Anto Prijosoesilo wrote: I was afraid that you're going to say that (use triggers) :-). I'm trying _not_ to use triggers because rules are more maintainable than triggers here in my environment. At least with rules someone who knows SQL would be able to maintain it with just a little extra training. The PostgreSQL 7.2 Reference Manual, in the section for CREATE RULE seems to imply that the rules are executed per row of the target table. I'm referring to paragraph 2 of the description. Read the last paragraph of that section: It is important to realize that a rule is really a query transformation mechanism, or query macro. The entire query is processed to convert it into a series of queries that include the rule actions. This occurs before evaluation of the query starts. So, conditional rules are handled by adding the rule condition to the WHERE clause of the action(s) derived from the rule. The above description of a rule as an operation that executes for each row is thus somewhat misleading. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger not a rule. Rules are most useful for situations that call for transforming entire queries independently of the specific data being handled. Not that the description is obvious, but it's there. Perhaps you know of a better way (place) to state this? -- Rod Taylor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] SET autocommit begins transaction?
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] SET autocommit begins transaction?
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] SET autocommit begins transaction?
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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] SET autocommit begins transaction?
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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] SET autocommit begins transaction?
... 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [BUGS] SET autocommit begins transaction?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] SET autocommit begins transaction?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] SET autocommit begins transaction?
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] SET autocommit begins transaction?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] SET autocommit begins transaction?
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] SET autocommit begins transaction?
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] SET autocommit begins transaction?
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] SET autocommit begins transaction?
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
