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

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. +

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. +

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