Обсуждение: 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?

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