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'