Thread: AW: [HACKERS] TRANSACTIONS
> Jose Soares <jose@sferacarta.com> writes: > > ------------------------------------------------------- > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > > ------------------------------------------------------- > > connect hygea.gdb; > > create table temp(a int); > > insert into temp values (1); > > insert into temp values (1000000000000000000000000000000000); > > commit; > > select * from temp; > > > arithmetic exception, numeric overflow, or string truncation > > > A > > =========== > > 1 > > > I would like to know what the Standard says and who is in the rigth path > > PostgreSQL or the others, considering the two examples reported below. > > I think those other guys are unquestionably failing to > conform to SQL92. > 6.10 general rule 3.a says All others also throw an error for this statement, and thus conform. As you can see from the select only the first row is inserted. I think the numeric is only an example of an error, it could also be any other error, like "duplicate key" or the like. > ...... > > and 3.3.4.1 says > > The phrase "an exception condition is raised:", followed by the > name of a condition, is used in General Rules and elsewhere to > indicate that the execution of a statement is unsuccessful, ap- > plication of General Rules, other than those of Subclause 12.3, > "<procedure>", and Subclause 20.1, "<direct SQL statement>", may > be terminated, diagnostic information is to be made available, > and execution of the statement is to have no effect on SQL-data or Note here, that they say "the statement", which does not say anything about other statements in the same transaction. > schemas. The effect on <target specification>s and SQL descriptor > areas of an SQL-statement that terminates with an exception condi- > tion, unless explicitly defined by this International Standard, is > implementation-dependent. > > I see no way that allowing the transaction to commit after an overflow > can be called consistent with the spec. Of course it can not commit this single statement that was in error. All he wants is to commit all other statements, before and after the error statement inside this same transaction. Andreas
Zeugswetter Andreas SB wrote: > > > Jose Soares <jose@sferacarta.com> writes: > > > ------------------------------------------------------- > > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > > > ------------------------------------------------------- > > > connect hygea.gdb; > > > create table temp(a int); > > > insert into temp values (1); > > > insert into temp values (1000000000000000000000000000000000); > > > commit; > > > select * from temp; > > > > > arithmetic exception, numeric overflow, or string truncation > > > > > A > > > =========== > > > 1 > > > > > I would like to know what the Standard says and who is in the rigth path > > > PostgreSQL or the others, considering the two examples reported below. > > > > I think those other guys are unquestionably failing to > > conform to SQL92. > > 6.10 general rule 3.a says > > All others also throw an error for this statement, and thus conform. > As you can see from the select only the first row is inserted. > I think the numeric is only an example of an error, it could also be > any other error, like "duplicate key" or the like. > > > ...... > > > > and 3.3.4.1 says > > > > The phrase "an exception condition is raised:", followed by the > > name of a condition, is used in General Rules and elsewhere to > > indicate that the execution of a statement is unsuccessful, ap- > > plication of General Rules, other than those of Subclause 12.3, > > "<procedure>", and Subclause 20.1, "<direct SQL statement>", may > > be terminated, diagnostic information is to be made available, > > and execution of the statement is to have no effect on SQL-data > or > > Note here, that they say "the statement", which does not say anything about > other statements in the same transaction. > > > schemas. The effect on <target specification>s and SQL descriptor > > areas of an SQL-statement that terminates with an exception > condi- > > tion, unless explicitly defined by this International Standard, > is > > implementation-dependent. > > > > I see no way that allowing the transaction to commit after an overflow > > can be called consistent with the spec. > > Of course it can not commit this single statement that was in error. > All he wants is to commit all other statements, before and after the > error statement inside this same transaction. > Isn't the intention of a transaction that it is atomic, i.e. either all statements pass or none of them? (see section 5.4 in the standard). Wim
Wim Ceulemans wrote: > Zeugswetter Andreas SB wrote: > > > > > Jose Soares <jose@sferacarta.com> writes: > > > > ------------------------------------------------------- > > > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2: > > > > ------------------------------------------------------- > > > > connect hygea.gdb; > > > > create table temp(a int); > > > > insert into temp values (1); > > > > insert into temp values (1000000000000000000000000000000000); > > > > commit; > > > > select * from temp; > > > > > > > arithmetic exception, numeric overflow, or string truncation > > > > > > > A > > > > =========== > > > > 1 > > > > > > > I would like to know what the Standard says and who is in the rigth path > > > > PostgreSQL or the others, considering the two examples reported below. > > > > > > I think those other guys are unquestionably failing to > > > conform to SQL92. > > > 6.10 general rule 3.a says > > > > All others also throw an error for this statement, and thus conform. > > As you can see from the select only the first row is inserted. > > I think the numeric is only an example of an error, it could also be > > any other error, like "duplicate key" or the like. > > > > > ...... > > > > > > and 3.3.4.1 says > > > > > > The phrase "an exception condition is raised:", followed by the > > > name of a condition, is used in General Rules and elsewhere to > > > indicate that the execution of a statement is unsuccessful, ap- > > > plication of General Rules, other than those of Subclause 12.3, > > > "<procedure>", and Subclause 20.1, "<direct SQL statement>", may > > > be terminated, diagnostic information is to be made available, > > > and execution of the statement is to have no effect on SQL-data > > or > > > > Note here, that they say "the statement", which does not say anything about > > other statements in the same transaction. > > > > > schemas. The effect on <target specification>s and SQL descriptor > > > areas of an SQL-statement that terminates with an exception > > condi- > > > tion, unless explicitly defined by this International Standard, > > is > > > implementation-dependent. > > > > > > I see no way that allowing the transaction to commit after an overflow > > > can be called consistent with the spec. > > > > Of course it can not commit this single statement that was in error. > > All he wants is to commit all other statements, before and after the > > error statement inside this same transaction. > > > > Isn't the intention of a transaction that it is atomic, i.e. either all > statements pass or none of them? (see section 5.4 in the standard). > > There's another problem, in the following example the transaction il failed but the transation it is not automatically rolledback, it remains instead in an "ABORT STATE" waitting for an explicit ROLLBACK or COMMIT. If I'm using transactions from a client program I don't know what's happened to the back end. first example: ^^^^^^^^^^ prova=> begin work; BEGIN prova=> create table tmp(a int); ERROR: Relation 'tmp' already exists prova=> drop table tmp; NOTICE: (transaction aborted): all queries ignored until end of transaction block *ABORT STATE* prova=> commit; END ----------------------------------------------------------------------- What is happening ? Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a COMMIT/ROLLBACK ? Why PostgreSQL allows a COMMIT in this case ? second example: ^^^^^^^^ prova=> begin; BEGIN prova=> create table tmp(a int); CREATE prova=> create table tmp(a int); ERROR: Relation 'tmp' already exists prova=> select * from tmp; ERROR: mdopen: couldn't open tmp: No such file or directory prova=> commit; END prova=> select * from tmp; ERROR: tmp: Table does not exist. ----------------------------------------------------------------------- What is happening ? Apparently the transaction was successful but the TMP table doesn't exist after a successful COMMIT. Why PostgreSQL allows a COMMIT in this case ? Why in this case PostgreSQL doesn't show the: NOTICE: (transaction aborted): all queries ignored until end of transaction block *ABORT STATE* -- Jose' Soares Bologna, Italy Jose@sferacarta.com
On Thu, 24 Feb 2000, Jose Soares wrote: > NOTICE: (transaction aborted): all queries ignored until end of transaction block > > *ABORT STATE* > Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a > COMMIT/ROLLBACK ? The PostgreSQL transaction paradigm seems to be that if you explicitly start a transaction, you get to explicitly end it. This is of course at odds with SQL, but it seems internally consistent to me. I hope that one of these days we can offer the other behaviour as well. > Why PostgreSQL allows a COMMIT in this case ? Good question. I assume it doesn't actually commit though, does it? I think a CHECK_IF_ABORTED (sp?) before calling the commit utility routine would be appropriate. Anyone? -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut wrote:
On Thu, 24 Feb 2000, Jose Soares wrote:Seems that PostgreSQL has a basically difference from other databases, it has two operation modes> NOTICE: (transaction aborted): all queries ignored until end of transaction block
>
> *ABORT STATE*> Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a
> COMMIT/ROLLBACK ?The PostgreSQL transaction paradigm seems to be that if you explicitly
start a transaction, you get to explicitly end it. This is of course at
odds with SQL, but it seems internally consistent to me. I hope that one
of these days we can offer the other behaviour as well.> Why PostgreSQL allows a COMMIT in this case ?
Good question. I assume it doesn't actually commit though, does it? I
think a CHECK_IF_ABORTED (sp?) before calling the commit utility routine
would be appropriate. Anyone?
"transaction mode" and "non-transaction mode".
If you want initialize a transaction in PostgreSQL you must declare it by using the BEGIN WORK
statement and an END/ABORT/ROLLBACK/COMMIT statement to terminate the transaction and switch from "transaction mode" to "non-transaction mode".
The SQL92 doesn't have such statement like BEGIN WORK because when you initialize a connection to a database you are all the time in transaction mode.
Should it be the real problem with transactions ?
--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden************
--
Jose' Soares
Bologna, Italy Jose@sferacarta.com