Re: What are nested transactions then? was Nested

Martijn van Oosterhout said:
> In any case, I don't quite understand the intended semantics of "duplicate
> key shouldn't rollback transaction". If I call a function that inserts a
> duplicate key, should the other effects of the function be rolled back or
> not? Anyone know how MSSQL deals with this?

In Oracle (I believe) that the exception is propogated up to abort the
initiating transaction unless you explictly place the SQL call which
failed in a begin..exception..end block (like below)

begin
-- do some SQL stuff here
exception
when NO_DATA_FOUND then
-- handle the exception
end;

You also have the option to RAISE an exception to abort the transaction
after you handled the error and decided that there is nothing useful you
can do.

Maybe, (don't shoot me) better procedural (pl/pgsql) error/exception
handling ought to be a dealt with before nested transaction support?

John Sidney-Woollett