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

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

>
> 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
> ******************************
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>