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
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?
--
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/