Postgres Pro
Downloads
Home   >   mailing lists

Thread: Support for detailed description of errors cased by trigger-violations

Support for detailed description of errors cased by trigger-violations

From
Andreas Joseph Krogh
Date:
Hi.
 
When working with Oracle it is possible to catch constraint-violations caused by triggers using JDBC, but it seems this isn't possible using PG, see this thread: https://github.com/impossibl/pgjdbc-ng/issues/111#issuecomment-62276464
 
For check of FK-violations the protocol supports this fine, with details about which table, column etc. causing the violation.
Is there any work going on or are there any plans to support similar info for violations caused by triggers?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Re: Support for detailed description of errors cased by trigger-violations

From
Tom Lane
Date:
Andreas Joseph Krogh <andreas@visena.com> writes:
> Hi.   When working with Oracle it is possible to catch constraint-violations 
> caused by triggers using JDBC, but it seems this isn't possible using PG, see 
> this thread: 
> https://github.com/impossibl/pgjdbc-ng/issues/111#issuecomment-62276464

I'm not exactly following the point.  The complaint seems to be that
   RAISE EXCEPTION 'ID must be less then 10';

doesn't send anything except the given primary message and a generic
SQLSTATE.  Well, duh: it's not supposed to.  There are a bunch of options
you can supply in RAISE to populate additional fields of the error report.
For example, you could add
   USING SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME

if you wanted the report to name the table the trigger is attached to.

So it seems to me this is lazy plpgsql programming, not a missing feature.
It would only be a missing feature if you think plpgsql should try to
auto-populate these fields; but I'd be against that because it would
require too many assumptions about exactly what the function might be
complaining about.
        regards, tom lane

Re: Support for detailed description of errors cased by trigger-violations

From
Andreas Joseph Krogh
Date:
På lørdag 08. november 2014 kl. 23:39:50, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> Hi. �� When working with Oracle it is possible to catch constraint-violations
> caused by triggers using JDBC, but it seems this isn't possible using PG, see
> this thread:
> https://github.com/impossibl/pgjdbc-ng/issues/111#issuecomment-62276464

I'm not exactly following the point.  The complaint seems to be that

    RAISE EXCEPTION 'ID must be less then 10';

doesn't send anything except the given primary message and a generic
SQLSTATE.  Well, duh: it's not supposed to.  There are a bunch of options
you can supply in RAISE to populate additional fields of the error report.
For example, you could add

    USING SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME

if you wanted the report to name the table the trigger is attached to.

So it seems to me this is lazy plpgsql programming, not a missing feature.
It would only be a missing feature if you think plpgsql should try to
auto-populate these fields; but I'd be against that because it would
require too many assumptions about exactly what the function might be
complaining about.

regards, tom lane
 
This is fantastic, thanks Tom!
It indeed was sloppy plpgsql programming. I didn't know about these extra arguments of RAISE making it possible to fine-tune the error-report from triggers.
 
Very nice and thanks again for making me look the right place.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment