Re: function within a function/rollbacks/exception handling - Mailing list pgsql-general

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
******************************