Re: using functions to generate custom error messages - Mailing list pgsql-novice

From Joel Rodrigues
Subject Re: using functions to generate custom error messages
Date
Msg-id 9151371D-F9EF-11D6-A592-0005024EF27F@Phreaker.net
Whole thread Raw
In response to Re: using functions to generate custom error messages  (Rod Kreisler <rod@23net.net>)
List pgsql-novice
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



pgsql-novice by date:

Previous
From: Joel Rodrigues
Date:
Subject: Re: using functions to generate custom error messages
Next
From: Joel Rodrigues
Date:
Subject: Re: using functions to generate custom error messages