Thread: using functions to generate custom error messages

[I just sent this using the wrong e-mail address, so assuming it
won't show up, here it comes again...]

Hello,

Would it be considered good practice to use functions to
generate custom error messages ?

The only odd thing about it I can see is that constraints have
to be coded into the function, whether or not they've already
been included in the table itself.

Here's an example.

CREATE TABLE amelie (
    rating    int CHECK (rating <= 10)
);


The results of using just this :

hypatia=# INSERT INTO amelie VALUES ('11');
ERROR:  ExecAppend: rejected due to CHECK constraint amelie_rating


Here is the function with the constraint included again (phrased
differently):

CREATE FUNCTION maxten () RETURNS OPAQUE AS '
     BEGIN
         IF NEW.rating > 10 THEN
             RAISE EXCEPTION ''Alert ! Please enter a number
between 0 and 10.'';
         END IF;

         RETURN NEW;
     END;
' LANGUAGE 'plpgsql';


And the trigger:

CREATE TRIGGER trigtest BEFORE INSERT OR UPDATE
     ON amelie
     FOR EACH ROW
     EXECUTE PROCEDURE maxten();


The results of this:

hypatia=# INSERT INTO amelie VALUES ('11');
ERROR:  Alert ! Please enter a number between 0 and 10.

My goal with this is to stick with the principle of keeping
"business logic" in the DBMS, avoiding elaborate error checking
and other unnecessary verbosity in my Python CGI scripts. I can
easily insert the function generated ERROR message into an xhtml
template and display it in the web browser.

I do understand that there are other ways/places to do error
checking, in the CGI script or in JavaScript, but this is
how/where I'd like to do it.

BTW, does anyone know why \df doesn't find the function "maxten"
that I created ?


hypatia=# \df maxten

                List of functions
  Result data type | Name | Argument data types
------------------+------+---------------------
(0 rows)


Cheers,
Joel

Joel,

> Would it be considered good practice to use functions to generate
> custom error messages ?

Depends on what you mean by "good practice".   The "best practice",
arguably,  would be to do this in some kind of middleware.   If,
however, your project is too lightweight to have middleware, then using
functions to do so can be pretty good.

An alternate, perhaps much simpler approach, would be to intercept the
database error messages and transform them into custom error messages
through text parsing or an error lookup table.

> The only odd thing about it I can see is that constraints have to be
> coded into the function, whether or not they've already been included
> in the table itself.

Absolutely.  In fact, if you take this approach, you might drop the
table constraints as redundant -- provided that you manage your
checking functions well.

> I do understand that there are other ways/places to do error
> checking, in the CGI script or in JavaScript, but this is how/where
> I'd like to do it.

Absolutety.  The drawbacks to this approach, however, are:
1) managing all these testing triggers
2) performance loss if the triggers are significantly slower than
constraints.

> BTW, does anyone know why \df doesn't find the function "maxten" that
> I created ?

Currently, \df does not list any function which returns OPAQUE.  I'm
not sure of the reason for this.

-Josh Berkus

> Joel,
>
> > Would it be considered good practice to use functions to generate
> > custom error messages ?
>
> Depends on what you mean by "good practice".   The "best practice",
> arguably,  would be to do this in some kind of middleware. If,
> however, your project is too lightweight to have middleware, then using
> functions to do so can be pretty good.
>
> An alternate, perhaps much simpler approach, would be to intercept the
> database error messages and transform them into custom error messages
> through text parsing or an error lookup table.
>

There is one drawback to this, the way Postgres raises the exception.  Even
if a particular query would generate multiple exceptions, only one is
returned.  This could create a situation that would annoy users... they
enter data, are given an error, fix that and then a different error appears.
It would be far superior to return all errors at once.


> > The only odd thing about it I can see is that constraints have to be
> > coded into the function, whether or not they've already been included
> > in the table itself.
>
> Absolutely.  In fact, if you take this approach, you might drop the
> table constraints as redundant -- provided that you manage your
> checking functions well.
>

This is true and especially important on heavily loaded servers.  I would
recommend, however, that one only remove the table/row constraints AFTER
debugging to help expose any bugs in your own functions.

> > I do understand that there are other ways/places to do error
> > checking, in the CGI script or in JavaScript, but this is how/where
> > I'd like to do it.
>
> Absolutely.  The drawbacks to this approach, however, are:
> 1) managing all these testing triggers
> 2) performance loss if the triggers are significantly slower than
> constraints.
>

This is true, however there are the benefits of:
1) NOT managing constraints in (possibly) multiple application in an attempt
to generate meaningful errors for users
2) performance increase in NOT performing those constraint checks in the
applications (granted probably not enough to offset the overhead of trigger
procedures but should be mentioned in fairness).

On Friday, November 15, 2002, at 11:23 , Josh Berkus wrote:

> Joel,
>
>> Would it be considered good practice to use functions to generate
>> custom error messages ?
>
> Depends on what you mean by "good practice".   The "best practice",
> arguably,  would be to do this in some kind of middleware.   If,
> however, your project is too lightweight to have middleware, then using
> functions to do so can be pretty good.


> An alternate, perhaps much simpler approach, would be to intercept the
> database error messages and transform them into custom error messages
> through text parsing or an error lookup table.

This is an interesting suggestion. Perhaps I could do something
with it. Pity that "Trigger Functions" don't accept arguments,
becoming reusable like regular functions.

>> The only odd thing about it I can see is that constraints have to be
>> coded into the function, whether or not they've already been included
>> in the table itself.
>
> Absolutely.  In fact, if you take this approach, you might drop the
> table constraints as redundant -- provided that you manage your
> checking functions well.

A bit scary, this bit.


>> I do understand that there are other ways/places to do error
>> checking, in the CGI script or in JavaScript, but this is how/where
>> I'd like to do it.
>
> Absolutety.  The drawbacks to this approach, however, are:
> 1) managing all these testing triggers

Yes !

> 2) performance loss if the triggers are significantly slower than
> constraints.
I don't see this as a problem for my particular application.


>> BTW, does anyone know why \df doesn't find the function "maxten" that
>> I created ?
>
> Currently, \df does not list any function which returns OPAQUE.  I'm
> not sure of the reason for this.

Weird thing this. Shouldn't there be a simple command like
"\duf" or something to simply display user created functions
along with their associated tables ?


Overall it seems to me that we have missing mechanism to deal
with this situation. The alternatives appear to be redundant
code, excessive code, or unnecessary code in "middleware".

> -Josh Berkus

On Saturday, November 16, 2002, at 07:09 , Rod Kreisler wrote:
>
>> Joel,
>>
>>> Would it be considered good practice to use functions to generate
>>> custom error messages ?
>>
>> Depends on what you mean by "good practice".   The "best practice",
>> arguably,  would be to do this in some kind of middleware. If,
>> however, your project is too lightweight to have middleware,
>> then using
>> functions to do so can be pretty good.
>>
>> An alternate, perhaps much simpler approach, would be to intercept the
>> database error messages and transform them into custom error messages
>> through text parsing or an error lookup table.
>>
>
> There is one drawback to this, the way Postgres raises the
> exception.  Even
> if a particular query would generate multiple exceptions, only one is
> returned.  This could create a situation that would annoy users... they
> enter data, are given an error, fix that and then a different
> error appears.
> It would be far superior to return all errors at once.

Seems a bit of (very) careful coding would be called for to
avoid this. And given this potential problem you describe,
perhaps trapping errors with triggers and functions may be a
superior solution.

>
>>> The only odd thing about it I can see is that constraints have to be
>>> coded into the function, whether or not they've already been included
>>> in the table itself.
>>
>> Absolutely.  In fact, if you take this approach, you might drop the
>> table constraints as redundant -- provided that you manage your
>> checking functions well.
>>
>
> This is true and especially important on heavily loaded
> servers.  I would
> recommend, however, that one only remove the table/row
> constraints AFTER
> debugging to help expose any bugs in your own functions.

Personally I'm just going to leave them there. Redundant code
true, but could be handy someday for someone trying to figure
out what's what, given that I've got 30+ tables in my project so
far.

>>> I do understand that there are other ways/places to do error
>>> checking, in the CGI script or in JavaScript, but this is how/where
>>> I'd like to do it.
>>
>> Absolutely.  The drawbacks to this approach, however, are:
>> 1) managing all these testing triggers
>> 2) performance loss if the triggers are significantly slower than
>> constraints.
>>
>
> This is true, however there are the benefits of:
> 1) NOT managing constraints in (possibly) multiple application
> in an attempt
> to generate meaningful errors for users
> 2) performance increase in NOT performing those constraint
> checks in the
> applications (granted probably not enough to offset the
> overhead of trigger
> procedures but should be mentioned in fairness).

This would depend on how and where the triggers are used & how
often. Plus there's the speed of recent CPUs. Anyway, I'm all
for brevity on CGI scripts.


Cheers,
Joel