Hello 2011/11/8 Lori Corbani <lec@xxxxxxxxxxxxxxxxxxx>: > > 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@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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
