Thread: function within a function/rollbacks/exception handling
I have a function, call it 'functionMain'. And I have several tables that each have trigger functions. Each trigger functionneeds 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)? Thanks. Lori
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 functionneeds 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 > > Thanks. > Lori > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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 functionneeds 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 ________________________________________ 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
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 ******************************
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 >