Thread: 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
<div dir="ltr"><div class="gmail_extra"><br /><div class="gmail_quote">On Fri, Nov 8, 2013 at 12:29 AM, Jim Nasby <span dir="ltr"><<ahref="mailto:jnasby@enova.com" target="_blank">jnasby@enova.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div id=":6vq" style="overflow:hidden">decibel@decina.cashnetusa=# SELECT 'Moo'::regclass;<br /> ERROR: relation "moo" does not exist atcharacter 8<br /><br /> Obviously in this example it doesn't matter, but this can definitely confuse users.</div></blockquote></div><br/></div><div class="gmail_extra">I would actually say *not* downcasing would be more likelyto confuse users. It's actually looking for a relation named "moo". If they actually had one named "Moo" and it said"relation "Moo" does not exist" that would be even more confusing and less likely to result in them figuring out whatthey've done wrong.<br /></div><div class="gmail_extra"><br clear="all" /><br />-- <br />greg<br /></div></div>
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