exception handling support in pgSQL

[P***@trilogy.com]

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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Jeff]

Post by P***@trilogy.com
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!

Post by P***@trilogy.com
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 <***@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 ***@postgresql.org)

[P***@trilogy.com]

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 <***@torgo.978.org>
Sent by: pgsql-general-***@postgresql.org
15/08/2004 07:00 PM


To: ***@trilogy.com
cc: "pgSQL General" <pgsql-***@postgresql.org>
Subject: Re: [GENERAL] exception handling support in pgSQL

Post by P***@trilogy.com
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!

Post by P***@trilogy.com
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 <***@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 ***@postgresql.org)

[Tom Lane]

Post by P***@trilogy.com
Have I done something wrong?

Yes, misspelled the condition name.

Post by P***@trilogy.com
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

[P***@trilogy.com]

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 <***@sss.pgh.pa.us>
Sent by: pgsql-general-***@postgresql.org
16/08/2004 12:33 AM


To: ***@trilogy.com
cc: Jeff <***@torgo.978.org>, "pgSQL General"
<pgsql-***@postgresql.org>
Subject: Re: [GENERAL] exception handling support in pgSQL

Post by P***@trilogy.com
Have I done something wrong?

Yes, misspelled the condition name.

Post by P***@trilogy.com
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