Thread: possible/feasible to specify field and value in error msg?

possible/feasible to specify field and value in error msg?

From
Willy-Bas Loos
Date:
Hi,

I have some complicated query that truncates and fills a table and i get this message:
ERROR:  smallint out of range
STATEMENT: <my huge query>
This is in postgres 8.4
I don't know where the error is, and the query takes rather long. So it is going to be a bit cumbersome for me to debug this.

Would it be possible/feasible to specify, in future versions of postgres:
* what value
* which field (of which table)
* the offending tuple? (possibly truncated to some threshold nr of characters)

I ask because i can imagine that, inside the code that handles this, you might not have access to that information and adding access to it might be inefficient.

I do get the whole query of course, and that is very handy for automated things. But in this case, it doesn't help me.

Cheers,

WBL

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: possible/feasible to specify field and value in error msg?

From
Bruce Momjian
Date:
On Wed, Jul  3, 2013 at 10:54:48AM +0200, Willy-Bas Loos wrote:
> Hi,
> 
> I have some complicated query that truncates and fills a table and i get this
> message:
> ERROR:  smallint out of range
> STATEMENT: <my huge query>
> This is in postgres 8.4
> I don't know where the error is, and the query takes rather long. So it is
> going to be a bit cumbersome for me to debug this.
> 
> Would it be possible/feasible to specify, in future versions of postgres:
> * what value
> * which field (of which table)
> * the offending tuple? (possibly truncated to some threshold nr of characters)
> 
> I ask because i can imagine that, inside the code that handles this, you might
> not have access to that information and adding access to it might be
> inefficient.
> 
> I do get the whole query of course, and that is very handy for automated
> things. But in this case, it doesn't help me.

We will add optional error details in Postgres 9.3:
http://momjian.us/main/blogs/pgblog/2013.html#April_11_2013

I don't know if an out-of-range error would generate the column name.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +



Re: possible/feasible to specify field and value in error msg?

From
Bruce Momjian
Date:
On Wed, Jul  3, 2013 at 11:14:18AM -0400, Bruce Momjian wrote:
> On Wed, Jul  3, 2013 at 10:54:48AM +0200, Willy-Bas Loos wrote:
> > Hi,
> > 
> > I have some complicated query that truncates and fills a table and i get this
> > message:
> > ERROR:  smallint out of range
> > STATEMENT: <my huge query>
> > This is in postgres 8.4
> > I don't know where the error is, and the query takes rather long. So it is
> > going to be a bit cumbersome for me to debug this.
> > 
> > Would it be possible/feasible to specify, in future versions of postgres:
> > * what value
> > * which field (of which table)
> > * the offending tuple? (possibly truncated to some threshold nr of characters)
> > 
> > I ask because i can imagine that, inside the code that handles this, you might
> > not have access to that information and adding access to it might be
> > inefficient.
> > 
> > I do get the whole query of course, and that is very handy for automated
> > things. But in this case, it doesn't help me.
> 
> We will add optional error details in Postgres 9.3:
> 
>     http://momjian.us/main/blogs/pgblog/2013.html#April_11_2013
> 
> I don't know if an out-of-range error would generate the column name.

I just tested this and it doesn't show the offending column name; 
sorry:
test=> CREATE TABLE test(x smallint);CREATE TABLEtest=> \set VERBOSITY verbosetest=> INSERT INTO test VALUES
(10000000);ERROR: 22003: smallint out of rangeLOCATION:  i4toi2, int.c:349
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +



Re: possible/feasible to specify field and value in error msg?

From
Willy-Bas Loos
Date:
On Wed, Jul 3, 2013 at 5:18 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Jul  3, 2013 at 11:14:18AM -0400, Bruce Momjian wrote:
> We will add optional error details in Postgres 9.3:
>
>       http://momjian.us/main/blogs/pgblog/2013.html#April_11_2013
 
I just tested this and it doesn't show the offending column name;
sorry:

        test=> CREATE TABLE test(x smallint);
        CREATE TABLE
        test=> \set VERBOSITY verbose
        test=> INSERT INTO test VALUES (10000000);
        ERROR:  22003: smallint out of range
        LOCATION:  i4toi2, int.c:349



It's great to see that you people care about "userland", judging by the effort that you describe in your article.
In fact you're already doing the thing that i asked about, i see that even the offending tuple is printed (which is new).
And of course it's not necessary to mention the column name when you mention the constraint name.
(BTW: your remark about NOT NULL constraints is not necessary, that error message is very clear:"ERROR:  null value in column "balance" violates not-null constraint" )

This is not a constraint going off, and in this case, none of that applies. But it seems probable to me that some day it will, seeing as you already implemented it for constraints.

Thanks,

Willy-Bas Loos

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: possible/feasible to specify field and value in error msg?

From
Pavel Stehule
Date:
Hello

2013/7/4 Willy-Bas Loos <willybas@gmail.com>:
> On Wed, Jul 3, 2013 at 5:18 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>
>> On Wed, Jul  3, 2013 at 11:14:18AM -0400, Bruce Momjian wrote:
>> > We will add optional error details in Postgres 9.3:
>> >
>> >       http://momjian.us/main/blogs/pgblog/2013.html#April_11_2013
>
>
>>
>> I just tested this and it doesn't show the offending column name;
>> sorry:
>>
>>         test=> CREATE TABLE test(x smallint);
>>         CREATE TABLE
>>         test=> \set VERBOSITY verbose
>>         test=> INSERT INTO test VALUES (10000000);
>>         ERROR:  22003: smallint out of range
>>         LOCATION:  i4toi2, int.c:349
>>
>
>
> It's great to see that you people care about "userland", judging by the
> effort that you describe in your article.
> In fact you're already doing the thing that i asked about, i see that even
> the offending tuple is printed (which is new).
> And of course it's not necessary to mention the column name when you mention
> the constraint name.
> (BTW: your remark about NOT NULL constraints is not necessary, that error
> message is very clear:"ERROR:  null value in column "balance" violates
> not-null constraint" )
>
> This is not a constraint going off, and in this case, none of that applies.
> But it seems probable to me that some day it will, seeing as you already
> implemented it for constraints.

this functionality will be enhanced in future - but it hardly depends
on current constraint and checks implementation - for some kind of
errors we are not able to join a exception with related column -
typically it is domain errors, probably we can to fill DATATYPE field
in this case.

Regards

Pavel Stehule

>
> Thanks,
>
> Willy-Bas Loos
>
> --
> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth