Thread: PL/pgSQL: EXCEPTION NOSAVEPOINT
This was motivated by the SELECT INTO EXACT discussion at http://archives.postgresql.org/pgsql-patches/2005-07/msg00559.php. The idea is to allow a PL/pgSQL exception to not automatically rollback the work done by the current block. The benefit is that exception handling can be used as a program flow control technique, without invoking transaction management mechanisms. This also adds additional means to enhanced Oracle PL/SQL compatibility. The patch implements an optional NOSAVEPOINT keyword after the EXCEPTION keyword that begins the exception handler definition. Here is an excerpt from the patched documentation: --------beginning of excerpt----------------------- If NOSAVEPOINT is not specified then a transaction savepoint is established immediately prior to the execution of statements. If an exception is raised then the effects of statements on the database are rolled back to this savepoint. If NOSAVEPOINT is specified then no savepoint is established. In this case a handled exception does not roll back the effects of statements. An unhandled exception, however, will still propagate out as usual, and any database effects may or may not be rolled back, depending on the characteristics of the enclosing block(s). Tip: Establishing a savepoint can be expensive. If you do not need the ability rollback the block's effect on the database, then either use the NOSAVEPOINT option, or avoid the EXCEPTION clause altogether. --------end of excerpt----------------------- Implementation question: In pl_exec.c the new option causes the "BeginInternalSubTransaction," "ReleaseCurrentSubTransaction," and "RollbackAndReleaseCurrentSubTransaction" function calls to be skipped. However, the corresponding "MemoryContextSwitchTo" and related calls are still performed. Should these calls also be dependent on the new option? Would that be more correct, and/or a performance improvement?
Attachment
Matt Miller <mattm@epx.com> writes:
> The idea is to allow a PL/pgSQL exception to not automatically rollback
> the work done by the current block.
This fundamentally breaks the entire backend. You do not have the
option to continue processing after elog(ERROR); the (sub)transaction
rollback is necessary to clean up inconsistent state.
regards, tom lane
On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote: > > The idea is to allow a PL/pgSQL exception to not automatically > > rollback the work done by the current block. > > This fundamentally breaks the entire backend. Yeah, but besides that, can you quick commit this to HEAD so I don't have to keep track of it locally? Just kidding. > You do not have the > option to continue processing after elog(ERROR); the (sub)transaction > rollback is necessary to clean up inconsistent state. Okay, I'll look at this more closely. Can you give me an example of what can go wrong?
Matt Miller <mattm@epx.com> writes:
> On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote:
>> You do not have the
>> option to continue processing after elog(ERROR); the (sub)transaction
>> rollback is necessary to clean up inconsistent state.
> Okay, I'll look at this more closely. Can you give me an example of
> what can go wrong?
Well, for example, failure to release locks and buffer pins held by an
abandoned query. Memory leaks. Row versions inserted into the database
that will be seen as good because they're marked as being generated by
the outer transaction, rather than coming from a subtransaction that can
be separately marked as aborted. Pretty much everything done by
AbortSubTransaction can be seen as cleanup...
The only way you could get the effect you are after would be to run a
new subtransaction for each executed query; which is not impossible
but the overhead would be appalling :-(
regards, tom lane