Thread: [subxacts] Aborting a function
[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"
> 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 WHEREkey = $1; ELSE RAISE EXCEPTION ''Unrecoverable error''; END IF ELSE SUBCOMMIT: END IF; END; ' language plpgsql;
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)
> 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.
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
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
openprimary_source', with description about how to resolve error specific the DB application/template a user
mightbe 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