Thread: exception handling support in pgSQL
Hi! I am a developer working for a company which typically deploys apps on Oracle. We would like to move to postgres for obvious reasons. However, most of our legacy apps use the exception handling support provided by PL/SQL. Instead of porting each of these procedures by hand, we would like to add exception handling support to pgSQL if possible. I looked into the code for the pgSQL library, and as I understand it, we can put support for user defined exceptions (something we use a lot) within the pgSQL library by intercepting the pgSQL call to SPI and moving to the handler locally. However, it seems that for system level exceptions (like zero_divide) we would have to go into SPI where it is executing the query plan. Is this correct? or can we put the support for these in the library itself somehow? If we have to make changes in SPI (or even pgSQL), would the postgres community be interested in accepting these changes into the core? Finally, I was trying to understand the code for the library itself, and it seems to use a namespace stack. Is this stack used for variable scope resolution? Exceptions (user defined) would also require scope resolution. Therefore, would we have to implement it within this stack ? or can we put in another stack for the exception resolution? thanks paraM
On Aug 15, 2004, at 1:57 AM, Paramveer.Singh@trilogy.com wrote: > Instead of porting each of these procedures by hand, > we would like to add exception handling support to pgSQL if possible. > Today is your lucky day! 8.0 adds exceptions to plpgsql! 8.0 however is in beta. But testers are greatly wanted! > I looked into the code for the pgSQL library, and as I understand it, > we can put support for user defined exceptions (something we use a lot) > within the pgSQL library by intercepting the I'm not sure we have user defined exceptions yet.. perhaps you could work on implementing them.. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
that's cool!
one big stumbling block resolved.!
Next I think I would like to look at the remaining exception handling functionality in PL/SQL
One is clearly user defined exceptions.
The PL/SQL documentation says that one must delare user defined exceptions in the DECLARE block
like:
DECLARE
my_excep EXCEPTION;
BEGIN
....
and the ONLY way a user defined exception can be raised is through the raise exception command;
RAISE my_excep;
This makes me think that user-defined exceptions can be handled in the pgSQL library without going into SPI.
Whenever we get a 'RAISE xyz' command to execute, we simply compute where the handler is and bracnch off there.
There are some scoping issues as well, which I think can also be resolved in the library.
Would the postgres dev team think this is a good architechture? or should we move user defined exceptions into the core as well?
Lastly, I installed 8.0 to test the exception handling.
The function I used is given as follows.
I extracted ERRCODE_DIVISION_BY_ZERO from plpgsql/src/plerrcodes.h
but the function does not compile.
The error that I get is:
test=# select * from foo(10);
ERROR: division by zero
CONTEXT: SQL query "insert into temp values(19/0)"
PL/pgSQL function "foo" line 2 at SQL statement
Have I done something wrong? Exception codes are also absent from the 8.0 documentation (which looks like it needs an upgrade).
*********************************************
drop function foo(integer);
create function foo(integer) returns integer
as '
begin
insert into temp values(19/0);
return 1200;
exception
when ERRCODE_DIVISION_BY_ZERO then
return 11;
end;
' language 'plpgsql';
*********************************************
thanks
paraM
| Jeff <threshar@torgo.978.org> Sent by: pgsql-general-owner@postgresql.org 15/08/2004 07:00 PM | To: Paramveer.Singh@trilogy.com cc: "pgSQL General" <pgsql-general@postgresql.org> Subject: Re: [GENERAL] exception handling support in pgSQL |
On Aug 15, 2004, at 1:57 AM, Paramveer.Singh@trilogy.com wrote:
> Instead of porting each of these procedures by hand,
> we would like to add exception handling support to pgSQL if possible.
>
Today is your lucky day! 8.0 adds exceptions to plpgsql!
8.0 however is in beta. But testers are greatly wanted!
> I looked into the code for the pgSQL library, and as I understand it,
> we can put support for user defined exceptions (something we use a lot)
> within the pgSQL library by intercepting the
I'm not sure we have user defined exceptions yet..
perhaps you could work on implementing them..
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Paramveer.Singh@trilogy.com writes: > Have I done something wrong? Yes, misspelled the condition name. > Exception codes are also absent from the 8.0 > documentation (which looks like it needs an upgrade). One wonders if you've actually read the 8.0 documentation... see http://developer.postgresql.org/docs/postgres/errcodes-appendix.html in particular the statement : The PL/pgSQL condition name for each error code is the same as the : phrase shown in the table, with underscores substituted for spaces. For : example, code 22012, DIVISION BY ZERO, has condition name : DIVISION_BY_ZERO. Condition names can be written in either upper or : lower case. The example given under http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING also seems reasonably clear about the spelling of this particular condition name. As for your original question --- I'm not that eager to try to emulate Oracle's approach to user-defined exceptions. The syntax they use is amazing ugly (pragma exception_init?) and we could not be very compatible in any case, because (a) we use SQLSTATEs not SQLCODEs for error IDs and (b) there doesn't seem to be a very close mapping between their error IDs and ours. Also, AFAICS their approach does not let a RAISE command insert any parameter values into error messages, which is mighty restrictive. We could probably build something based on the same idea of declaring names that can be referenced in RAISE and EXCEPTION, but I think we should deliberately not adopt exactly their syntax for it. One possibility is that the declaration identifies the SQLSTATE to use, perhaps declare myerr exception('XP012'); and generalize RAISE to RAISE name 'message string' [, value [, ...]] where the "name" can either be one of the level values we used to allow (for backwards compatibility) or it can be a built-in or user-defined condition name. This would provide enough info for RAISE to do the correct elog call. EXCEPTION clauses in the same block could also use "myerr" as a condition name to trap this error. regards, tom lane
Hi !
As Tom suggested, I augmented the declare statements to allow for
my_excep exception;
// I haven't mapped them to sqlstates yet.
in a way that the user defined exception also goes into the namestack to allow for resolution/scoping,
then I augmented the raise statement to allow
raise my_excep;
now, the problem that I am facing is how to generate a 'my_excep' exception when I get to
exec_stmt_raise in plpgsql/src/pl_exec.c
I looked into the PG_TRY and other macros and ran the database through gdb, but I could not figure
out how the database creates and throws exception when they occur.(in an attempt to copy it)
Can anyone point this out to me?
thanks
paraM
| Tom Lane <tgl@sss.pgh.pa.us> Sent by: pgsql-general-owner@postgresql.org 16/08/2004 12:33 AM | To: Paramveer.Singh@trilogy.com cc: Jeff <threshar@torgo.978.org>, "pgSQL General" <pgsql-general@postgresql.org> Subject: Re: [GENERAL] exception handling support in pgSQL |
Paramveer.Singh@trilogy.com writes:
> Have I done something wrong?
Yes, misspelled the condition name.
> Exception codes are also absent from the 8.0
> documentation (which looks like it needs an upgrade).
One wonders if you've actually read the 8.0 documentation... see
http://developer.postgresql.org/docs/postgres/errcodes-appendix.html
in particular the statement
: The PL/pgSQL condition name for each error code is the same as the
: phrase shown in the table, with underscores substituted for spaces. For
: example, code 22012, DIVISION BY ZERO, has condition name
: DIVISION_BY_ZERO. Condition names can be written in either upper or
: lower case.
The example given under
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
also seems reasonably clear about the spelling of this particular
condition name.
As for your original question --- I'm not that eager to try to emulate
Oracle's approach to user-defined exceptions. The syntax they use is
amazing ugly (pragma exception_init?) and we could not be very
compatible in any case, because (a) we use SQLSTATEs not SQLCODEs for
error IDs and (b) there doesn't seem to be a very close mapping between
their error IDs and ours. Also, AFAICS their approach does not let a
RAISE command insert any parameter values into error messages, which is
mighty restrictive.
We could probably build something based on the same idea of declaring
names that can be referenced in RAISE and EXCEPTION, but I think we
should deliberately not adopt exactly their syntax for it.
One possibility is that the declaration identifies the SQLSTATE to use,
perhaps
declare myerr exception('XP012');
and generalize RAISE to
RAISE name 'message string' [, value [, ...]]
where the "name" can either be one of the level values we used to allow
(for backwards compatibility) or it can be a built-in or user-defined
condition name. This would provide enough info for RAISE to do the
correct elog call. EXCEPTION clauses in the same block could also use
"myerr" as a condition name to trap this error.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings