Re: Doing better at HINTing an appropriate column within errorMissingColumn()

On Fri, Mar 28, 2014 at 4:47 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> Peter Geoghegan wrote:
>> With the addition of LATERAL subqueries, Tom fixed up the mechanism
>> for keeping track of which relations are visible for column references
>> while the FROM clause is being scanned. That allowed
>> errorMissingColumn() to give a more useful error to the one produced
>> by the prior coding of that mechanism, with an errhint sometimes
>> proffering: 'There is a column named "foo" in table "bar", but it
>> cannot be referenced from this part of the query'.
>>
>> I wondered how much further this could be taken. Attached patch
>> modifies contrib/fuzzystrmatch, moving its Levenshtein distance code
>> into core without actually moving the relevant SQL functions too. That
>> change allowed me to modify errorMissingColumn() to make more useful
>> suggestions as to what might have been intended under other
>> circumstances, like when someone fat-fingers a column name.
>
>> [local]/postgres=# select * from orders o join orderlines ol on o.orderid = ol.orderids limit 1;
>> ERROR: 42703: column ol.orderids does not exist
>> LINE 1: ...* from orders o join orderlines ol on o.orderid = ol.orderid...
>> ^
>> HINT: Perhaps you meant to reference the column "ol"."orderid".
>
> This sounds like a mild version of DWIM:
> http://www.jargondb.org/glossary/dwim
>
> Maybe it is just me, but I get uncomfortable when a program tries
> to second-guess what I really want.

It's not really DWIM, because the backend is still throwing an error.
It's just trying to help you sort out the error, along the way.
Still, I share some of your discomfort. I see Peter's patch as an
example of a broader class of things that we could do - but I'm not
altogether sure that we want to do them. There's a risk of adding not
only CPU cycles but also clutter. If we do things that encourage
people to crank the log verbosity down, I think that's going to be bad
more often than it's good. It strains credulity to think that this
patch alone would have that effect, but there might be quite a few
similar improvements that are possible. So I think it would be good
to consider how far we want to go in this direction and where we think
we might want to stop. That's not to say, let's not ever do this,
just, let's think carefully about where we want to end up.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company