regclass error reports improperly downcased

On Fri, Nov 8, 2013 at 12:29 AM, Jim Nasby <jnasby@enova.com> wrote:

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.

I would actually say *not* downcasing would be more likely to 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 what
they've done wrong.

--
greg

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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 deal with.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers