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