function within a function/rollbacks/exception handling

Hello

2011/11/7 Lori Corbani <Lori.Corbani@jax.org>:

I have a function, call it 'functionMain'.  And I have several tables that each have trigger functions.  Each trigger function needs to call 'functionMain' (with different parameters).

table A => trigger function A ==> functionMain
table B => trigger function B ==> functionMain
table C => trigger function C ==> functionMain

'functionMain' returns VOID (runs an insert statement). and has an exception/raise exception block.

An insert transaction for table A is launched (insertA), trigger function A is called,
'functionMain' is called and 'functionMain' fails.  Hence, trigger function A needs to rollback.

Questions:

a) I am assuming that the trigger functions should use 'PERFORM functionMain(....)'?

b) if 'functionMain' fails, then 'funtionMain' automatically performs an implicit rollback, correct?

c) if 'functionMain' fails, should the trigger function also contain an exception handler
  or will the rollback from 'functionMain' cascade up to the original transaction (insertA)?

PostgreSQL function does not do rollback by self. You should to do it
manually after exception. When functionMain fails, then trigger fails
too.

If trigger contains a exception handler, then it start a
subtransaction on begin block execution and rollback this
subtransaction when exception is raised.

Regards

Pavel Stehule

On 07/11/11 19:18, Lori Corbani wrote:

I have a function, call it 'functionMain'. And I have several tables that each have trigger functions. Each trigger function needs to call 'functionMain' (with different parameters).

table A => trigger function A ==> functionMain
table B => trigger function B ==> functionMain
table C => trigger function C ==> functionMain

'functionMain' returns VOID (runs an insert statement). and has an exception/raise exception block.

An insert transaction for table A is launched (insertA), trigger function A is called,
'functionMain' is called and 'functionMain' fails. Hence, trigger function A needs to rollback.

Questions:

a) I am assuming that the trigger functions should use 'PERFORM functionMain(....)'?

If you don't want the result, yes.

b) if 'functionMain' fails, then 'funtionMain' automatically performs an implicit rollback, correct?

c) if 'functionMain' fails, should the trigger function also contain an exception handler
or will the rollback from 'functionMain' cascade up to the original transaction (insertA)?

Unless you catch the exception, it will roll back the whole transaction,
so "yes" to b + c. If it helps to visualise what happens, exceptions are
actually implemented using savepoints in plpgsql.

--
Richard Huxton
Archonet Ltd

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

Richard Huxton wrote:

On 07/11/11 19:18, Lori Corbani wrote:

I have a function, call it 'functionMain'. And I have several tables
that each have trigger functions. Each trigger function needs to call
'functionMain' (with different parameters).

table A => trigger function A ==> functionMain
table B => trigger function B ==> functionMain
table C => trigger function C ==> functionMain

'functionMain' returns VOID (runs an insert statement). and has an
exception/raise exception block.

An insert transaction for table A is launched (insertA), trigger
function A is called,
'functionMain' is called and 'functionMain' fails. Hence, trigger
function A needs to rollback.

Questions:

a) I am assuming that the trigger functions should use 'PERFORM
functionMain(....)'?

If you don't want the result, yes.

b) if 'functionMain' fails, then 'funtionMain' automatically performs
an implicit rollback, correct?

c) if 'functionMain' fails, should the trigger function also contain
an exception handler
or will the rollback from 'functionMain' cascade up to the
original transaction (insertA)?

Unless you catch the exception, it will roll back the whole transaction,
so "yes" to b + c. If it helps to visualise what happens, exceptions are
actually implemented using savepoints in plpgsql.

--

Lori E. Corbani
Scientific Software Engineer
The Jackson Laboratory
600 Main Street
Bar Harbor, ME 04609 USA
(207) 288-6425 (V)
******************************
lori.corbani@jax.org
http://www.informatics.jax.org
******************************

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@informatics.jax.org]
Sent: Tuesday, November 08, 2011 8:46 AM
To: Richard Huxton
Cc: Lori Corbani; pgsql-general@postgresql.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.

Thanks.
Lori

Richard Huxton wrote:

On 07/11/11 19:18, Lori Corbani wrote:

I have a function, call it 'functionMain'. And I have several tables
that each have trigger functions. Each trigger function needs to call
'functionMain' (with different parameters).

table A => trigger function A ==> functionMain
table B => trigger function B ==> functionMain
table C => trigger function C ==> functionMain

'functionMain' returns VOID (runs an insert statement). and has an
exception/raise exception block.

An insert transaction for table A is launched (insertA), trigger
function A is called,
'functionMain' is called and 'functionMain' fails. Hence, trigger
function A needs to rollback.

Questions:

a) I am assuming that the trigger functions should use 'PERFORM
functionMain(....)'?

If you don't want the result, yes.

b) if 'functionMain' fails, then 'funtionMain' automatically performs
an implicit rollback, correct?

c) if 'functionMain' fails, should the trigger function also contain
an exception handler
or will the rollback from 'functionMain' cascade up to the
original transaction (insertA)?

Unless you catch the exception, it will roll back the whole transaction,
so "yes" to b + c. If it helps to visualise what happens, exceptions are
actually implemented using savepoints in plpgsql.

--

Lori E. Corbani
Scientific Software Engineer
The Jackson Laboratory
600 Main Street
Bar Harbor, ME 04609 USA
(207) 288-6425 (V)
******************************
lori.corbani@jax.org
http://www.informatics.jax.org
******************************

On Tuesday, November 08, 2011 7:13:03 am Lori Corbani 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?

http://www.postgresql.org/docs/9.0/interactive/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING

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

--
Adrian Klaver
adrian.klaver@gmail.com

On Tue, Nov 8, 2011 at 9:13 AM, Lori Corbani <Lori.Corbani@jax.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@informatics.jax.org]
Sent: Tuesday, November 08, 2011 8:46 AM
To: Richard Huxton
Cc: Lori Corbani; pgsql-general@postgresql.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

Hello

2011/11/8 Lori Corbani <lec@informatics.jax.org>:

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.

I am not sure if you will find what you need :(

PostgreSQL has different model of exception handling inside procedures
than other databases - it is based on fact, so Pg has only functions
(not procedures) - void function is not equalent to procedures in
sybase. This model is more simple - you don't need to thinking about
COMMITs or ROLLBACKs inside PL - this is done outside procedures. This
model has some advantages and some disadvantages - and mainly it is
different

Regards

Pavel Stehule