Thread: proposal: catch warnings
Hello,
PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that
warnings are catchable too. Simply solution's is adding one callback to
error's processing of errors on level WARNING.
typedef struct WarningHandlerCallback
{ bool (*callback) (void *arg, ErrorData *edata); void *arg;
} WarningHandlerCallback;
extern DLLIMPORT WarningHandlerCallback *warning_handler;
Callback function returns true if accept warning and process it. This
function is called from errfinish()
/* * Emit the message to the right places. If warning_handler is
defined, * try use warning_handler. Emit message only if handler don't
accept * message (returns false). Warning handlers are used in PL/pgPSM
language. */ if (elevel == WARNING) { bool handled = false;
if (warning_handler) handled =
(*warning_handler->callback)(warning_handler->arg,edata);
if (!handled) EmitErrorReport(); } else
EmitErrorReport();
It's propably usable only for SQL/PSM implementation, and it's one from two
necessery hacks to core for this PL (second is scrollable cursor's support).
But without this hook I cannot simply distribute plpgpsm.
Any comments?
Best regards
Pavel Stehule
_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote: > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that > warnings are catchable too. Simply solution's is adding one callback to > error's processing of errors on level WARNING. Exceptions are run within their own subtransaction, so the exception handling code runs separately. Does the PSM warning error handler run in the same transaction or a separate subtransaction? Can transaction execution continue afterwards? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
> >On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote: > > > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires >that > > warnings are catchable too. Simply solution's is adding one callback to > > error's processing of errors on level WARNING. > >Exceptions are run within their own subtransaction, so the exception >handling code runs separately. > >Does the PSM warning error handler run in the same transaction or a >separate subtransaction? Can transaction execution continue afterwards? > It's depend. Continue and exit warning handlers run in the same transaction, undo handler has separate subtransaction. It works well. For patterns used in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not found). Transactions are controlled in plpgpsm code (like plpgsql). This patch allows detecting signalled warning (after processing and finishing any statement). Pavel Stehule _________________________________________________________________ Find sales, coupons, and free shipping, all in one place! �MSN Shopping Sales & Deals http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639
On Sun, 2007-01-07 at 11:20 +0100, Pavel Stehule wrote: > > > >On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote: > > > > > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires > >that > > > warnings are catchable too. Simply solution's is adding one callback to > > > error's processing of errors on level WARNING. > > > >Exceptions are run within their own subtransaction, so the exception > >handling code runs separately. > > > >Does the PSM warning error handler run in the same transaction or a > >separate subtransaction? Can transaction execution continue afterwards? > > > > It's depend. Continue and exit warning handlers run in the same transaction, > undo handler has separate subtransaction. It works well. For patterns used > in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not > found). Hmmm. SQLSTATE 02000 NO_DATA doesn't seem to be raised anywhere by the backend, though it is listed by ECPG. Are you thinking of the special variable FOUND, which doesn't raise an exception in PL/pgSQL, or the PostgreSQL PL/pgSQL exception: NO_DATA_FOUND (SQLSTATE P0002) which isn't actually an SQL ERROR at all. (Definitely an exception in PL/SQL?) ISTM that if we have an exception defined like this in PL/pgSQL EXCEPTION WHEN NO_DATA_FOUND THEN blockEND; that we wouldn't need to wrap it in a sub-transaction, because the earlier statements need not be rolled back when it occurs. Perhaps you can scan for this condition in the PSM code, rather than getting the backend to throw a different kind of error? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
> > > > > >Does the PSM warning error handler run in the same transaction or a > > >separate subtransaction? Can transaction execution continue afterwards? > > > > > > > It's depend. Continue and exit warning handlers run in the same >transaction, > > undo handler has separate subtransaction. It works well. For patterns >used > > in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not > > found). > >Hmmm. SQLSTATE 02000 NO_DATA doesn't seem to be raised anywhere by the >backend, though it is listed by ECPG. > >Are you thinking of the special variable FOUND, which doesn't raise an >exception in PL/pgSQL, or the PostgreSQL PL/pgSQL exception: >NO_DATA_FOUND (SQLSTATE P0002) which isn't actually an SQL ERROR at all. >(Definitely an exception in PL/SQL?) > >ISTM that if we have an exception defined like this in PL/pgSQL > > EXCEPTION > WHEN NO_DATA_FOUND THEN > block > END; > >that we wouldn't need to wrap it in a sub-transaction, because the >earlier statements need not be rolled back when it occurs. Perhaps you >can scan for this condition in the PSM code, rather than getting the >backend to throw a different kind of error? > FOUND is only one value, but I have to detect all values from sqlstate classes '02' and '01'. Without backend change I am not able catch warnings from other PL. Hook is general solution. Pavel _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that
> warnings are catchable too.
What in the world does it mean to "catch" a warning? If your intention
is to process arbitrary user-defined code while inside the error
subsystem, I can tell you right now that it's unlikely to work.
> Simply solution's is adding one callback to
> error's processing of errors on level WARNING.
I can't get excited about hooks that are defined in such a way that
there can be only one user of the hook ... if it's useful to you,
it's probably useful to someone else too.
regards, tom lane
What is problem? ANSI SQL has different model of handling exception than postgresql. It doesn't distinguishes between warnings and exception. Simply some sqlstate clases are reservated for warnings and other's for exception. But all sqlstate's (without '00000') can be handled via any CONTINUE, EXIT or UNDO handler. Exceptions are not problem. But I cannot map sqlstates '01xxx' and '02xxx' to exception because they internally do rollback. I have to use different elevel. I cannot trap warnings on plpgpsm level, because in this moment I will lost an possibility of traping warnings from others PL. I can use warning for signaling any safe event (don't need rollaback) from this PL. Currently I save only last warning, but it is possible safe warnings info to any queue. > >What in the world does it mean to "catch" a warning? If your intention >is to process arbitrary user-defined code while inside the error >subsystem, I can tell you right now that it's unlikely to work. > No, it means, it process user-defined code related to some compound statement immediate after some statement which signals sqlstate '01xxx' or '02xxx'. Exceptions are diffrent, they do rollback before call exception's handle. I use callback function only for decision if there is any possible warning handler and for saving struct edata. After excecuton any statement I check this struct and call handler statement. > > Simply solution's is adding one callback to > > error's processing of errors on level WARNING. > >I can't get excited about hooks that are defined in such a way that >there can be only one user of the hook ... if it's useful to you, >it's probably useful to someone else too. > Propably we can use it for faster catch no_data in plpgsql or for enhancing of statement RAISE in future. Any SQL/PSM implementation have to solve this task. Regards Pavel Stehule _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> What is problem? ANSI SQL has different model of handling exception than
> postgresql. It doesn't distinguishes between warnings and exception. Simply
> some sqlstate clases are reservated for warnings and other's for exception.
> But all sqlstate's (without '00000') can be handled via any CONTINUE, EXIT
> or UNDO handler. Exceptions are not problem.
Really? If an EXIT handler fires on a warning, does that mean the
statement giving the warning is aborted midstream, instead of being
allowed to complete?
I think that the model the SQL spec has in mind is that a warning
condition is raised only after the statement has run to completion
(which implies only one such condition per statement BTW). This is
quite at variance with our notion of WARNING. AFAICS you are not going
to be able to implement anything that works sanely if you try to take
control away at the instant of elog(WARNING). You would need to create
some infrastructure for making this happen after the statement giving
the warning is otherwise done --- which will take *much* more extensive
revisions than just hooking into elog.
regards, tom lane
>
>"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > What is problem? ANSI SQL has different model of handling exception than
> > postgresql. It doesn't distinguishes between warnings and exception.
>Simply
> > some sqlstate clases are reservated for warnings and other's for
>exception.
> > But all sqlstate's (without '00000') can be handled via any CONTINUE,
>EXIT
> > or UNDO handler. Exceptions are not problem.
>
>Really? If an EXIT handler fires on a warning, does that mean the
>statement giving the warning is aborted midstream, instead of being
>allowed to complete?
>
Propably I have too bad english. I wrote it. I am sorry. Any warning
handlers are executed after statements. With exit handler it executes
handler statement and leave block. That's all.
>I think that the model the SQL spec has in mind is that a warning
>condition is raised only after the statement has run to completion
>(which implies only one such condition per statement BTW). This is
>quite at variance with our notion of WARNING. AFAICS you are not going
>to be able to implement anything that works sanely if you try to take
>control away at the instant of elog(WARNING). You would need to create
>some infrastructure for making this happen after the statement giving
>the warning is otherwise done --- which will take *much* more extensive
>revisions than just hooking into elog.
>
I understand. Warning infrastructure is implemented in plpgpsm code now.
There is one interest of hook - compatibility. I can simply set if warning
is processed on server side or is sended to client. This decision depends on
application (not only application, it depend on used language).
Do you thing API like?
ErrorData *edata;
push_catch_warning_state(); // new handler and set catching to on
.... EXEC statement ....
if ((edata = catched_warning() != NULL)
{
if (I know warning) process it; clean_warning();
else distribute_warning(); // doesn't mean directly sending to client, maybe
others handlers wait for it
}
pop_catch_warning_state(); // prev. handler and set catch to prev. value
Pavel
_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/