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@xxxxxxxxxxxxxxxxxxx] Sent: Tuesday, November 08, 2011 8:46 AM To: Richard Huxton Cc: Lori Corbani; pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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@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
