Thread: Error handling in stored functions/procedures

I am currently designing an application which should be accessible from
different interfaces. For this I like to be using stored procedures to
process the contents of form submissions and dialog screens.

After studying the PG database, it seems to me that I can fulfill the
following requirements.

a) The basic contents of the internal data dictionary can be used to
check incoming fields from on their length and permitted contents.

b) With a little extra work, I should be able to define a table which
can be used to check field contents against field masks.

Together with a table reference, the key/value pairs from the submission
can be checked by a table-driven stored procedure which, depending on
the outcome of the test, can return a row which contains an exit status
and an exit message.

Since I do not really like to duplicate (or rewrite functionality), the
following steps should be carried out by the database itself, which has
the possibilities to define and use them. These steps are all kinds of
constraints which can be defined in the CREATE TABLE statement itself.

The big problem I am facing however, is that when an error is
encountered by the database itself, I do not have any influence anymore
on the error message and the flow of control.

Consider the following definition :

  drop table testing;
  create table testing (
          key_nr serial primary key,
          string_value text check (string_value != 'xx'),
          int_value integer check (int_value <= 255 and int_value >= 0)
  );

And the following SQL statement :

  insert into testing (string_value, int_value) values ('xx', 100);

This will yield the following error :

  ERROR:  new row for relation "testing" violates check constraint
  "testing_string_value"

interrupt further processing of the stored procedure, and return
immediately to the client.

Showing a user the previous message does not really give a clue about
what has happened. With some processing it is possible to retrieve the
name of the offending field from this message, but it is impossible to
tell the user what is wrong about the contents that he filled in.

To do this would force the programmer to test and catch all errors, and
translate them to meaningful feedback for the user. Since the raised
error returns to the client, this means that the client is forced to do
one of two things : keep a local translation repository, or call another
stored procedure.

The first solution is error prone due to keeping information about parts
of the database in two different places, the second is essentially a
waste of bandwidth and time, since the problem should have been solved
in the first stored procedure.

Another possibility is of course writing all constraints in stored
procedures and not using the built-in possibilities offered by PG, but
isn't that a waste of my time and a waste of all the work that has been
done by this team ?

What solutions do you use ?

Regards,

Jurgen

> a) The basic contents of the internal data dictionary can be used to
> check incoming fields from on their length and permitted contents.
>
> b) With a little extra work, I should be able to define a table which
> can be used to check field contents against field masks.
You can use column check constraints with regular expressions
for that. No need for an extra table I would assume.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sun, 30 May 2004 20:04:50 +0200
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> > a) The basic contents of the internal data dictionary can be used to
> > check incoming fields from on their length and permitted contents.
> >
> > b) With a little extra work, I should be able to define a table
> > which can be used to check field contents against field masks.
> You can use column check constraints with regular expressions
> for that. No need for an extra table I would assume.

Thanks for the tip.

This then, removes the first part of my explanation, and dumps me
completely in the second part, which is where the biggest problems
reside.

Jurgen

> This then, removes the first part of my explanation, and dumps me
> completely in the second part, which is where the biggest problems
> reside.
AFAICT 7.4 does much better error handling (no, you can't
easily control error handling inside a transaction, though). It
reports errors in a way that can be parsed a lot better thus
allowing for fairly easy translation into meaningful user
messages.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Sun, 30 May 2004 22:08:10 +0200
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> > This then, removes the first part of my explanation, and dumps me
> > completely in the second part, which is where the biggest problems
> > reside.
> AFAICT 7.4 does much better error handling (no, you can't
> easily control error handling inside a transaction, though). It
> reports errors in a way that can be parsed a lot better thus
> allowing for fairly easy translation into meaningful user
> messages.

You mean that the default generated error messages contain some more
information I presume, like the table name and the constraint name ?

I think I noticed that already, and since I had time since yesterday
evening, I thought things over and came to the conclusion that this
mechanism at least gives an escape hatch, since it is possible to give a
name to each constraint, and then use this name as an index to get a
proper error message.

Regards,

Jurgen

> You mean that the default generated error messages contain some more
> information I presume, like the table name and the constraint name ?
Even better, the information is *structured* afaik.

> I think I noticed that already, and since I had time since yesterday
> evening, I thought things over and came to the conclusion that this
> mechanism at least gives an escape hatch, since it is possible to give a
> name to each constraint, and then use this name as an index to get a
> proper error message.
For check constraints you can even name them yourself during
table creation.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346