Oracle does not roll back any transaction unless explicitly requested by
the client application. If there are errors while executing statements
inside a transaction, their effect is rolled back, not the whole
transaction. The application can then decide if the successful part of
the transaction is rolled back as a whole, or committed as a whole...
This is contrasting with postgreSQL behavior of rolling back the
transaction automatically after any error.
This feature is often requested because it's very useful, especially in
big imports (where you don't want to roll back your whole import because
of 2 invalid lines).
Of course others say that input validation is the application's
responsibility, which is also a valid point, but I can tell you there
are other valid usages of this feature, like complicated transactions
where you want to take one course of action or other depending on the
success/failure of a query, which is only possible using hacks in
postgres right now, and doesn't even always work (like for a duplicate
key insert, you can't 100% make sure it won't fail). The suggested
solution on this list is to be prepared to repeat the transaction, but
this leads to more complicated application code.
The main reason why this feature is painful to implement in Postgres is
the lack of nested transactions. Until they are not implemented, chunk
your error prone transactions as small as you can, filter out invalid
data, and be prepared to repeat the cycle.
Cheers,
Csaba.
On Mon, 2003-07-14 at 10:34, Mike Mascari wrote:
> Jörg Schulz wrote:
>
> >>... I have this feeling the reason Oracle gives this result may
> >>be again because transactions have been switched off!
> >
> > This snippet comes from the Oracle console:
> > (table name is "a" not "test" / messages are in german)
> >
> ...
>
> > SQL> select * from a;
> >
> > A
> > ----------
> > 1
> > 3
> > 4
> > 2
>
> Presumably Oracle is not rolling back a duplicate key violation,
> allowing the transaction to continue. This is an often requested
> feature not present in PostgreSQL.
>
> Mike Mascari
> mascarm@mascari.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>