Re: function within a function/rollbacks/exception handling

On Tue, Nov 8, 2011 at 9:13 AM, Lori Corbani <Lori(dot)Corbani(at)jax(dot)org> wrote:
> Richard,
>
> I manage to find one comment about an implicit rollback in a section of
> the developer's guide when porting from Oracle-to-Postgres:  "when an
> exception is caught by an EXECPTION clause, all database changes since
> the block's BEGIN are automatically rolled back"
>
> Do you know of any other place in the documentation this discusses the
> implicit rollback in more detail?  Or do you know of a good online site
> that contains some good examples or best-practices for these
> function-to-function calls?
>
> We are starting to port our Sybase database (200 stored procedures) over
> to Postgres and I am finding the online Postgres documentation and the
> Douglas book a bit lacking in some of the more specific examples that I
> am interested in finding.
>
> Thanks.
> Lori
>
>
> ________________________________________
> From: Lori Corbani [lec(at)informatics(dot)jax(dot)org]
> Sent: Tuesday, November 08, 2011 8:46 AM
> To: Richard Huxton
> Cc: Lori Corbani; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] function within a function/rollbacks/exception handling
>
> Richard,
>
> I manage to find one comment about an implicit rollback in a section of
> the developer's guide when porting from Oracle-to-Postgres:  "when an
> exception is caught by an EXECPTION clause, all database changes since
> the block's BEGIN are automatically rolled back"
>
> Do you know of any other place in the documentation this discusses the
> implicit rollback in more detail?  Or do you know of a good online site
> that contains some good examples or best-practices for these
> function-to-function calls?
>
> We are starting to port our Sybase database (200 stored procedures) over
> to Postgres and I am finding the online Postgres documentation and the
> Douglas book a bit lacking in some of the more specific examples that I
> am interested in finding.

Implicit rollback is a fundamental underpinning of transactions in
SQL. Any error will abort either A. the entire transaction or B. all
activity since the last savepoint.

In all languages except plpgsql savepoints are explicitly set (with
SAVEPOINT command) and you restore to the savepoint with ROLLBACK TO.

Savepoints in plpgsql are implicitly created anytime you enter a
BEGIN/END block with an EXCEPTION handler(s) defined. Unlike vanilla
SQL savepoints, plpgsql savepoints can nest so that each EXCEPTION
block you enter is adding a error handler onto a stack (which is much
more similar to exceptions in the vein of java or C++). Whether you
call functions from within functions or not is totally immaterial to
error handling generally; you can have multiple nested handlers in a
single function, or none at all.

merlin