Thread: Attaching error cursor position to invalid constant values

I'm fooling around with getting the parser to report an error cursor
location if input conversion fails for a constant in a SQL command.
For instance:

regression=# select 42 = 'foo';
ERROR:  invalid input syntax for integer: "foo"
LINE 1: select 42 = 'foo';                   ^
regression=# select '2000-jax-01'::date;
ERROR:  invalid input syntax for type date: "2000-jax-01"
LINE 1: select '2000-jax-01'::date;              ^
regression=# 

This seems like it'd be a pretty useful thing to have in long queries,
but in short queries it looks a bit like overkill.  And it affects
the expected output of a whole lot of the regression tests.

Does anyone think this might be "too chatty"?
        regards, tom lane

Tom Lane wrote:
>I'm fooling around with getting the parser to report an error cursor
>location if input conversion fails for a constant in a SQL command.

>This seems like it'd be a pretty useful thing to have in long queries,
>but in short queries it looks a bit like overkill.  And it affects

I wanted to have this many times, actually I considered it a bug that
it didn't report the position.
-- 
Sincerely,          Stephen R. van den Berg.
Several ways to kill a programmer:  kill -15, fair trial; kill -1, death by
hanging; kill -2, suicide; kill -3, euthanasia; kill -9, execution.

On Sun, Aug 31, 2008 at 6:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm fooling around with getting the parser to report an error cursor
> location if input conversion fails for a constant in a SQL command.
...
>
> This seems like it'd be a pretty useful thing to have in long queries,
> but in short queries it looks a bit like overkill.  And it affects
> the expected output of a whole lot of the regression tests.
>

This is a cool idea, but as you say it's pretty pointless for one-liner queries.

What about implementing some kind of cutoff point for query length.
As in, "Display the cursor if the query is < $whatever characters
long"?  I'm thinking most regression test queries would fall well
below a reasonable value of $whatever.

Cheers,
BJ

"Brendan Jurd" <direvus@gmail.com> writes:

> On Sun, Aug 31, 2008 at 6:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm fooling around with getting the parser to report an error cursor
>> location if input conversion fails for a constant in a SQL command.
> ...
>>
>> This seems like it'd be a pretty useful thing to have in long queries,
>> but in short queries it looks a bit like overkill.  And it affects
>> the expected output of a whole lot of the regression tests.
>
> This is a cool idea, but as you say it's pretty pointless for one-liner queries.
>
> What about implementing some kind of cutoff point for query length.
> As in, "Display the cursor if the query is < $whatever characters
> long"?  I'm thinking most regression test queries would fall well
> below a reasonable value of $whatever.

I don't think it's pointless at all from the server's point of view. The
server doesn't know what kind of tool is being used to view the results. If
it's a gooey tool then the tool will want to know where the error was so it
can highlight it or position the cursor.

Perhaps there could be a psql option to control whether to show the error
position and perhaps that setting could be based on the length of the query or
how many lines are in it but that seems unnecessarily baroque.

It doesn't seem outrageously chatty to me. Better to err on the side of being
too helpful to the user than not helpful enough.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!

Gregory Stark <stark@enterprisedb.com> writes:
> "Brendan Jurd" <direvus@gmail.com> writes:
>> What about implementing some kind of cutoff point for query length.

> Perhaps there could be a psql option to control whether to show the error
> position and perhaps that setting could be based on the length of the query or
> how many lines are in it but that seems unnecessarily baroque.

Yeah, if we were to do something like that it should clearly be a
client-side decision.

> It doesn't seem outrageously chatty to me. Better to err on the side of being
> too helpful to the user than not helpful enough.

Currently, for the things we show an error cursor for, there's no lower
limit on query length, eg

regression=# select nosuchcol from int4_tbl;
ERROR:  column "nosuchcol" does not exist
LINE 1: select nosuchcol from int4_tbl;              ^
regression=# 

and I don't recall having heard any complaints about that.  I was just a
bit shell-shocked by the number of regression test diffs my patch
generated.  But on looking closer, the reason is the intentional testing
of bad values in a lot of the datatype-specific tests.  So that's
probably not a good indicator of how chatty it'll seem to regular users.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> and I don't recall having heard any complaints about that.  I was just a
> bit shell-shocked by the number of regression test diffs my patch
> generated.  But on looking closer, the reason is the intentional testing
> of bad values in a lot of the datatype-specific tests.  So that's
> probably not a good indicator of how chatty it'll seem to regular users.

The only logical argument I see here for why this case is any different is
that since the constant is present in the original query and it's repeated in
the error the user has enough information to immediately identify the source
of the error. That's not true for other types of errors where the site of the
error isn't obvious from the error message.

But even for constants it's not entirely true either. You could have something
like:

select 'foo'+0, 'foo'+0.0;

and when you get the error the you would have to deduce which constant is in
error based on the type it describes. That could be quite a bit more complex
than this example.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
 

Tom Lane wrote:
> Does anyone think this might be "too chatty"?

No.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com