Обсуждение: regclass error reports improperly downcased

psql (9.3.1)
Type "help" for help.

decibel@decina.cashnetusa=# SELECT 'Moo'::regclass;
ERROR:  relation "moo" does not exist at character 8

Obviously in this example it doesn't matter, but this can definitely confuse users.

Same in 9.1 and HEAD.
-- 
Jim Nasby, Lead Data Architect   (512) 569-9461

Jim Nasby <jnasby@enova.com> writes:
> decibel@decina.cashnetusa=# SELECT 'Moo'::regclass;
> ERROR:  relation "moo" does not exist at character 8

That's doing what it's supposed to.  Compare

regression=# select 'Moo'::regclass;
ERROR:  relation "moo" does not exist
LINE 1: select 'Moo'::regclass;              ^
regression=# select '"Moo"'::regclass;
ERROR:  relation "Moo" does not exist
LINE 1: select '"Moo"'::regclass;              ^

The regclass input converter applies the same case-folding rules as
the SQL parser does, ie, fold unless double-quoted.
        regards, tom lane

On 11/7/13 6:41 PM, Tom Lane wrote:
> Jim Nasby <jnasby@enova.com> writes:
>> decibel@decina.cashnetusa=# SELECT 'Moo'::regclass;
>> ERROR:  relation "moo" does not exist at character 8
>
> That's doing what it's supposed to.  Compare
>
> regression=# select 'Moo'::regclass;
> ERROR:  relation "moo" does not exist
> LINE 1: select 'Moo'::regclass;
>                 ^
> regression=# select '"Moo"'::regclass;
> ERROR:  relation "Moo" does not exist
> LINE 1: select '"Moo"'::regclass;
>                 ^
>
> The regclass input converter applies the same case-folding rules as
> the SQL parser does, ie, fold unless double-quoted.

Ahh, duh. Hrm... I ran across this because someone here got confused by this:

SELECT pg_total_relation_size( schema_name || '.' || relname ) FROM pg_stat_all_tables
ERROR: relation "moo" does not exist

Obviously the problem is that they needed to use quote_ident(), but I was hoping to make the error less confusing to
dealwith.
 

Perhaps we can add a hint? Something to the effect of "Do you need to use double-quotes or quote_ident()?"
-- 
Jim Nasby, Lead Data Architect   (512) 569-9461

Jim Nasby <jnasby@enova.com> writes:
> Ahh, duh. Hrm... I ran across this because someone here got confused by this:

> SELECT pg_total_relation_size( schema_name || '.' || relname ) FROM pg_stat_all_tables
> ERROR: relation "moo" does not exist

Personally I'd do that like
   select pg_total_relation_size(oid) from pg_class where ...

and avoid fooling with regclass conversion at all.
        regards, tom lane

On 11/8/13 2:21 PM, Tom Lane wrote:
> Jim Nasby <jnasby@enova.com> writes:
>> Ahh, duh. Hrm... I ran across this because someone here got confused by this:
>
>> SELECT pg_total_relation_size( schema_name || '.' || relname ) FROM pg_stat_all_tables
>> ERROR: relation "moo" does not exist
>
> Personally I'd do that like
>
>        select pg_total_relation_size(oid) from pg_class where ...
>
> and avoid fooling with regclass conversion at all.

Yeah, that's what I did in this case. I'm just trying to make it more obvious to users that make this mistake.

Is anyone opposed to some kind of hint?
-- 
Jim Nasby, Lead Data Architect   (512) 569-9461

Jim Nasby <jnasby@enova.com> writes:
> Is anyone opposed to some kind of hint?

Would depend on the text of the hint.  I'm a bit dubious that we can
come up with something that's not wildly inappropriate in other scenarios.
        regards, tom lane