Re: [GENERAL] How to stop implicit rollback on certain errors?
"Ross J. Reedstrom" wrote: > > Hmm, sounds like a vote for nested transactions. The JDBC driver developer > (Peter Mount) was musing that nested transaction would make large object > support easier for him, as well. > > As to the other example of Oracle not forcing a rollback, I have a feeling > that this may be specific to syntax errors in an interactive session. > Implementing this sort of behavior has been discussed recently on the > hackers list, in the context of making it easier to work interactively > inside a transaction. > > I would be surprised if Oracle allows non-syntax errors inside a > transaction to be ignored, or ignores anything in a non-interactive > session. How about testing an example like links, where you provide data > in a format the backend can't handle, (an out of range int or date or > something) and see how Oracle handles that. > Here my test on Oracle: $ sqlplus scott/tiger SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 23:22:31 1999 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> create table test(id int primary key,i numeric(12,3), d date); Table created. SQL> insert into TEST VALUES(1,9.999,'28-FEB-1999'); 1 row created. SQL> insert into TEST VALUES(1,1.999,'29-FEB-1999') ERROR at line 1: ORA-01830: date format picture ends before converting entire input string SQL> insert into TEST VALUES(1,.999,'28-FEB-1999') ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> commit; Commit complete. SQL> select * from test; ID I D -- -- - 1 10 28-FEB-99 SQL> insert into TEST VALUES(1,1.999,'10-JAN-1999') * ERROR at line 1: ORA-1: unique constraint (SCOTT.SYS_C001590) violated SQL> insert into TEST VALUES(2,1.119,'10-MAR-1999'); 1 row created. SQL> select * from test; ID I D -- -- - 1 10 28-FEB-99 2 1.119 10-MAR-99 SQL> rollback; Rollback complete. SQL> select * from test; ID I D -- -- - 1 10 28-FEB-99 SQL> exit Disconnected from Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production Jose' > Who's right? Well, as Peter Eisentraut said, what Postgres implements is > the _definition_ of a transaction: all together, or nothing at all. This > isn't just an arbitrary rule: the validity of the relational calculus > depends on transactional semantics. > > Ross > > -- > Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 > > On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: > > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: > > >Seriously, why do you use a transaction, when you don't want any errors > > >caught? Transactions are defined as everything succeeds or nothing goes. > > >If you want update to succeed anyhow, put it in it's own transaction > > >(i.e., commit before it). > > > > I want errors caught, most errors abort everything but some errors I want > > to try a different update instead, if that doesn't work then only rollback > > everything. > > > > >> I guess that's expected, and I should insert big years using another less > > >> ambiguous format. What is the recommended format? > > > > > >The safest way would be to set a date format with SET DATESTYLE TO and use > > >that, possibly assisted by library formatting routines. > > > > OK. > > > > Link. > > > > > > > > > >
Re: [GENERAL] How to stop implicit rollback on certain errors?
"Ross J. Reedstrom" wrote: > > Hmm, sounds like a vote for nested transactions. The JDBC driver developer > (Peter Mount) was musing that nested transaction would make large object > support easier for him, as well. > > As to the other example of Oracle not forcing a rollback, I have a feeling > that this may be specific to syntax errors in an interactive session. > Implementing this sort of behavior has been discussed recently on the > hackers list, in the context of making it easier to work interactively > inside a transaction. > > I would be surprised if Oracle allows non-syntax errors inside a > transaction to be ignored, or ignores anything in a non-interactive > session. How about testing an example like links, where you provide data > in a format the backend can't handle, (an out of range int or date or > something) and see how Oracle handles that. here the test: SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 21:37:48 1999 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> create table test(i numeric(12,3), d date); Table created. SQL> insert into TEST VALUES(9.999,'28-FEB-1999'); 1 row created. SQL> insert into TEST VALUES(1.999,'29-FEB-1999') * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string SQL> insert into TEST VALUES(.999,'28-FEB-1999') * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> commit; Commit complete. select * from test; I D -- - 10 28-FEB-99 SQL> insert into TEST VALUES(1.999,'10-JAN-1999'); 1 row created. SQL> select * from test; I D -- - 10 28-FEB-99 1.999 10-JAN-99 SQL> rollback; Rollback complete. SQL> select * from test; I D -- - 10 28-FEB-99 Jose' > > Who's right? Well, as Peter Eisentraut said, what Postgres implements is > the _definition_ of a transaction: all together, or nothing at all. This > isn't just an arbitrary rule: the validity of the relational calculus > depends on transactional semantics. > > Ross > > -- > Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 > > On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: > > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: > > >Seriously, why do you use a transaction, when you don't want any errors > > >caught? Transactions are defined as everything succeeds or nothing goes. > > >If you want update to succeed anyhow, put it in it's own transaction > > >(i.e., commit before it). > > > > I want errors caught, most errors abort everything but some errors I want > > to try a different update instead, if that doesn't work then only rollback > > everything. > > > > >> I guess that's expected, and I should insert big years using another less > > >> ambiguous format. What is the recommended format? > > > > > >The safest way would be to set a date format with SET DATESTYLE TO and use > > >that, possibly assisted by library formatting routines. > > > > OK. > > > > Link. > > > > > > > > > >
Re: [GENERAL] How to stop implicit rollback on certain errors?
Hmm, sounds like a vote for nested transactions. The JDBC driver developer (Peter Mount) was musing that nested transaction would make large object support easier for him, as well. As to the other example of Oracle not forcing a rollback, I have a feeling that this may be specific to syntax errors in an interactive session. Implementing this sort of behavior has been discussed recently on the hackers list, in the context of making it easier to work interactively inside a transaction. I would be surprised if Oracle allows non-syntax errors inside a transaction to be ignored, or ignores anything in a non-interactive session. How about testing an example like links, where you provide data in a format the backend can't handle, (an out of range int or date or something) and see how Oracle handles that. Who's right? Well, as Peter Eisentraut said, what Postgres implements is the _definition_ of a transaction: all together, or nothing at all. This isn't just an arbitrary rule: the validity of the relational calculus depends on transactional semantics. Ross -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: > >Seriously, why do you use a transaction, when you don't want any errors > >caught? Transactions are defined as everything succeeds or nothing goes. > >If you want update to succeed anyhow, put it in it's own transaction > >(i.e., commit before it). > > I want errors caught, most errors abort everything but some errors I want > to try a different update instead, if that doesn't work then only rollback > everything. > > >> I guess that's expected, and I should insert big years using another less > >> ambiguous format. What is the recommended format? > > > >The safest way would be to set a date format with SET DATESTYLE TO and use > >that, possibly assisted by library formatting routines. > > OK. > > Link. > > > >
Re: [GENERAL] How to stop implicit rollback on certain errors?
Peter Eisentraut wrote: > On 1999-12-08, Lincoln Yeoh mentioned: > > > begin; > > insert into stuff; > > do some nondatabase things based on last inserted id; > > update a date in stuff; > > commit; > > > > It seems that if the date is out of the database range, everything is > > thrown out. Is it possible to catch the database error and use a null date > > instead, without throwing everything away? > > Yes, use no transaction at all. :) > > Seriously, why do you use a transaction, when you don't want any errors > caught? Transactions are defined as everything succeeds or nothing goes. > If you want update to succeed anyhow, put it in it's own transaction > (i.e., commit before it). I have the same problem using transactions. I want to use transactions anyway and I want to caught only some errors and thrown out some others depends on which kind of error, and at end decide to give an explicit COMMIT or ROLLBACK. I tried other databases and they have a behavior different from PostgreSQL. SOLID for example attends for an explicit COMMIT or ROLLBACK and doesn't have a default rollback, the same with Oracle8i, take a look at this Oracle example: $sqlplus scott/tiger SQL*Plus: Release 8.0.5.0.0 - Production on Thu Dec 9 15:00:47 1999 (c) Copyright 1998 Oracle Corporation. All rights reserved Connected to: Oracle8 Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production SQL> create table a (a int); table created. SQL> insert into a values(1); 1 row created. SQL> insert into a value(2); ERROR at line 1: ORA-00928: missing SELECT keyword SQL> commit work; Commit complete SQL> select * from a; A 1 SQL> And now the same example in PostgreSQL: $ psql prova Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: prova prova=> begin; BEGIN prova=> create table a(a int); CREATE prova=> insert into a values(1); INSERT 1902953 1 prova=> insert into a value(2); ERROR: parser: parse error at or near "value" prova=> commit work; END prova=> select * from a; ERROR: a: Table does not exist. prova=> Who are right. Oracle or PostgreSQL ? Jose'
Re: [GENERAL] How to stop implicit rollback on certain errors?
At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: >Seriously, why do you use a transaction, when you don't want any errors >caught? Transactions are defined as everything succeeds or nothing goes. >If you want update to succeed anyhow, put it in it's own transaction >(i.e., commit before it). I want errors caught, most errors abort everything but some errors I want to try a different update instead, if that doesn't work then only rollback everything. >> I guess that's expected, and I should insert big years using another less >> ambiguous format. What is the recommended format? > >The safest way would be to set a date format with SET DATESTYLE TO and use >that, possibly assisted by library formatting routines. OK. Link.
Re: [GENERAL] How to stop implicit rollback on certain errors?
On 1999-12-08, Lincoln Yeoh mentioned: > begin; > insert into stuff; > do some nondatabase things based on last inserted id; > update a date in stuff; > commit; > > It seems that if the date is out of the database range, everything is > thrown out. Is it possible to catch the database error and use a null date > instead, without throwing everything away? Yes, use no transaction at all. :) Seriously, why do you use a transaction, when you don't want any errors caught? Transactions are defined as everything succeeds or nothing goes. If you want update to succeed anyhow, put it in it's own transaction (i.e., commit before it). > I guess that's expected, and I should insert big years using another less > ambiguous format. What is the recommended format? The safest way would be to set a date format with SET DATESTYLE TO and use that, possibly assisted by library formatting routines. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
