[Note: my ISP is having problems with email, so probably a duplicate
of this will be published as soon as their SMTP starts working again]


There's a point that hasn't been raised in discussion of subtransactions
here and it's what to do when a function aborts.

If I have a plpgsql function that goes like

subbegin;
  ... do work ...;
subcommit;

and then the "work" causes a subtransaction abort, what should happen?
Currently the system remains inside the subtransaction that the function
created --- the "subcommit" is never executed and thus the
subtransaction is never closed.  This is wrong.

(We could talk about exception support in plpgsql or other language
handlers but I don't think this is going to happen for 7.5.)


I think the right thing to do is close all subtransactions that the
function may have created, abort the (sub)transaction that created the
function itself, and return control to the application in an aborted
state.

Furthermore, suppose a function is called which creates subtransactions,
and inside that function there's another function which creates more
subtransactions, and inside one of those inner subtransactions there is
an error.  In this case, the right thing to do is abort all
subtransactions created in the second function, and all subtransactions
created in the first function, and then return control to the
application, in an aborted state.

As a simple example, consider this:


create function crashme2() returns int strict language plpgsql as '
begin
   subbegin;
      select foo;         -- aborts the transaction
                          -- did not close the subxact
end;';

create function crashme5() returns int strict language plpgsql as '
 begin subbegin; perform crashme2(); subabort; end; ';


begin;
  subbegin;
    select crashme5();
  subabort;
  subcommit;            -- WARNING: there is no subxact in progress
commit;


The point here is to observe that the transaction is taken back to the
correct nesting level after the function aborts.  The server reacts with
the following error report:

alvherre=#     select crashme5();
ERROR:  no existe la columna "foo"
CONTEXT:  SQL query "SELECT  foo"
PL/pgSQL function "crashme2" line 1 at SQL statement
SQL query "SELECT  crashme2()"
PL/pgSQL function "crashme5" line 1 at perform


This is working with a patch I'm testing now, modelled after an idea of
Bruce.  It works by setting an "abort level" when the function is going
to be called, and unsetting it when the function is done executing.  If
there is a transaction abort, the unset won't happen, and the aborting
code will see the marker defined, close all the levels up to the marker,
and then put that transaction in aborted state.

I have tested it using functions in the targetlist (modifying
ExecEvalFunc) and as rangevars (ExecInitFunctionScan and
ExecEndFunctionScan).  Maybe ExecEvalOper needs to do the same, if an
operator can be defined as dependant on a function which uses subxacts.

Comments, opinions please?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Use it up, wear it out, make it do, or do without"

Thu, 8 Jul 2004 15:42:26 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
[subxacts] Aborting a function
> create function crashme2() returns int strict language plpgsql as '
> begin
>    subbegin;
>       select foo;         -- aborts the transaction
>                           -- did not close the subxact
> end;';

I'm not sure I follow. Are you saying that the following code or
something similar will never work?

create function insertOrUpdateTabX(int, text) returns as '
BEGIN
  SUBBEGIN;
    INSERT INTO tab (key, col) VALUES ($1, $2);

  GET DIAGNOSTICS status = SQLSTATE;
  IF (status != 000000) THEN
    -- Got an error, determine what it is
    SUBABORT;

    IF (status = 23505) THEN
      -- This entry already exists. Update the value instead
      UPDATE tab SET col = $2 WHERE key = $1;
    ELSE
      RAISE EXCEPTION ''Unrecoverable error'';
    END IF
  ELSE
    SUBCOMMIT:
  END IF;

END;
' language plpgsql;



Thu, 08 Jul 2004 16:52:04 -0400
Rod Taylor <pg@rbt.ca>
Re: [subxacts] Aborting a function
On Thu, Jul 08, 2004 at 04:52:04PM -0400, Rod Taylor wrote:
> > create function crashme2() returns int strict language plpgsql as '
> > begin
> >    subbegin;
> >       select foo;         -- aborts the transaction
> >                           -- did not close the subxact
> > end;';
> 
> I'm not sure I follow. Are you saying that the following code or
> something similar will never work?

Right.  The problem is that plpgsql stops executing code as soon as an
error is found; the control returns to the main loop by way of a longjmp
in the error reporting mechanism.

I was quite dissappointed, as you will undoubtly be, when I found this
out :-(

Some sort of modification will be needed to the error processing
mechanism, but this is beyond me at this point ...  I don't have any
idea how could this be.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Licensee shall have no right to use the Licensed Software
for productive or commercial use. (Licencia de StarOffice 6.0 beta)

Thu, 8 Jul 2004 17:08:51 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: [subxacts] Aborting a function
> Some sort of modification will be needed to the error processing
> mechanism, but this is beyond me at this point ...  I don't have any
> idea how could this be.

So.. If you cannot rollback a subtransaction within a plpgsql function
(all function types or just that one?) then there is no point is
allowing them to be created either.

I say disallow sub-transactions within plpgsql functions until it works
as expected.


Thu, 08 Jul 2004 17:41:22 -0400
Rod Taylor <pg@rbt.ca>
Re: [subxacts] Aborting a function
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> (We could talk about exception support in plpgsql or other language
> handlers but I don't think this is going to happen for 7.5.)

Au contraire ... I think it *must* happen, and indeed that
subtransaction support in plpgsql is not different from exceptions.
Per Honza Pazdziora's recent post:

>> The syntax of handling exceptions is (in PL/SQL):
>> 
>> 	BEGIN
>> 		some code, for example a bunch of SQL commands;
>> 	EXCEPTION
>> 		WHEN nameofexception THEN
>> 			handle the exception, maybe ROLLBACK;
>> 	END;

This should be exactly how you invoke a subtransaction in plpgsql.
Anything else will be too messy to contemplate...

			regards, tom lane

Sat, 10 Jul 2004 01:16:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [subxacts] Aborting a function 
On 07/10/04:27/6, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > (We could talk about exception support in plpgsql or other language
> > handlers but I don't think this is going to happen for 7.5.)
> 
> Au contraire ... I think it *must* happen, and indeed that
> subtransaction support in plpgsql is not different from exceptions.
> Per Honza Pazdziora's recent post:
> 
> >> The syntax of handling exceptions is (in PL/SQL):
> >> 
> >> 	BEGIN
> >> 		some code, for example a bunch of SQL commands;
> >> 	EXCEPTION
> >> 		WHEN nameofexception THEN
> >> 			handle the exception, maybe ROLLBACK;
> >> 	END;

Shortly, does that snippet imply that an extension author will have the
ability to catch and "resolve" ERRORs, and have access to the error data?


[I was planning on bringing this up at a later time, but this thread seems
directly related to a feature that I would like plpy to have.]

Does the above snippet mean what I think it means? That is, will one be able to
trap ERRORs without being required to raise it like one would do now
--siglongjmp'ing Warn_restart, and such. Will one have access to the currently
static ErrorData in elog.c? This would seem necessary if one is going to
be able to test anything about exception that occurred('nameofexception').
This is what I would hope from looking at the above snippet of PL/PgSQL code,
but I hope such a feature would be available independent of subxacts(tho, not
within the context of a PL/PgSQL procedure, I suppose).

Putting it in the context of PL/Py:

try:
    r = Postgres.Relation("primary_source")
except Postgres.Error:
    r = someOtherObjectTheProvidesTheNecessaryInfoWithACompatibleInterface
#          ---  or, perhaps  ---
    raise MyDBAppException(
        "More appropriate error message than 'failed to
        open primary_source', with description about how to resolve error
        specific the DB application/template a user might be using",
        detail="some good details"
        hint="a good hint"
    )
...

In Python it is quite common to do something like that, rather than testing
ahead of the action.

I've whined about this a couple times on #postgresql, both times the responses
raised the same good question, "What would you do with the error when you get
it?". I think that the best response to that is that depends on the user's
application, and the context of his/her usage.

It should also be noted that I have, with _apparent_ success, caught an
ERROR, and continued on my merry way without raising it. To say the
least, it was done without elegance as the client is likely to get
the ERROR message from errstart. That and the fact that I cannot access the
static ErrorData is what kept me from keeping that functionality.
(I basically stored and restored the state of a couple globals which may have
been changed by an elog(ERROR), ie, InError)

-- 
Regards,
        James William Pye
Mon, 12 Jul 2004 04:21:14 -0700
James William Pye <flaw@rhid.com>
Re: [subxacts] Aborting a function