BUG #16991: regclass is not case sensitive causing "relation does not exist" error
The following bug has been logged on the website:
Bug reference: 16991
Logged by: Federico Caselli
Email address: cfederico87@gmail.com
PostgreSQL version: 13.2
Operating system: any
Description:
Hi,
Casting the name of an object to regclass using `name_col::regclass` is very
useful when querying the pg_catalog view to find the relevant information.
This casting does not work if the name of the object to cast is case
sensitive when it comes from a column.
Example using indexes to illustrate:
```sql
begin;
create table example(id serial, value text);
create index "CaseSensitiveIndex" on example (value, id);
create index not_case_sensitive on example (id, value);
select pg_relation_size(indexrelname::regclass) from pg_stat_all_indexes
where indexrelname = 'not_case_sensitive';
select pg_relation_size(indexrelname::regclass) from pg_stat_all_indexes
where indexrelname = 'CaseSensitiveIndex';
rollback;
```
In this example the case the first select using the insensitive
"not_case_sensitive" is correctly casted to regclass, while the second use
using "CaseSensitiveIndex" fails with the error "SQL Error [42P01]: ERROR:
relation "casesensitiveindex" does not exist" suggesting that the case is
not respected by the regclass casting.
While the example makes little sense, since the column "indexrelid" could be
used directly, in many queries the oid of an object is not immediately
available.
Using the "select oid from pg_class where ..." as suggested in the
documentation here https://www.postgresql.org/docs/current/datatype-oid.html
works, but as mentioned in the documentation is not 100% correct.
It think it would be nice if when casting from a column regclass would use
the case sensitive form, like it's possible when using the literal name
directly, like `select '"CaseSensitiveIndex"'::regclass`
Best,
Federico
PG Bug reporting form <noreply@postgresql.org> writes:
Casting the name of an object to regclass using `name_col::regclass` is very
useful when querying the pg_catalog view to find the relevant information.
This casting does not work if the name of the object to cast is case
sensitive when it comes from a column.
This is acting as designed and documented: regclass input conversion acts
the same as the regular SQL parser does, which includes case folding of
unquoted text. If you have input that should be taken literally, you
can apply quote_ident() to it.
Note that your sample query has a second issue: it takes no account of
schemas, so it'll fail if a view row shows a table that is not in your
search_path. Actually-robust solutions to this problem require something
like
(quote_ident(schemaname) || '.' || quote_ident(indexrelname))::regclass
which illustrates why you don't really want regclass to take its input
literally: it needs to be able to interpret schema-qualified names.
regards, tom lane
Thanks for the reply. Taking into consideration the schema as in your
solution does indeed explain the current behavior and it makes sense.
I did not come across that documented behavior while searching for regclass
in the pg docs. The most relevant page I was able to find was the Object
Identifier Types page that does not mention it.
I’ll look into proposing a change in that docs page to mention it, if
that’s the appropriate location for it.
Thanks
Federico Caselli
Federico <cfederico87@gmail.com> writes:
I did not come across that documented behavior while searching for regclass
in the pg docs. The most relevant page I was able to find was the Object
Identifier Types page that does not mention it.
I’ll look into proposing a change in that docs page to mention it, if
that’s the appropriate location for it.
Hmm ... I *thought* it was documented, but perhaps not. If not,
it's likely that the other reg* types are likewise underdocumented.
regards, tom lane
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style></head><body lang=IT link=blue vlink="#954F72" style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal><span lang=EN-US>It may just be me that did not find it.<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Best,</p><p class=MsoNormal>Federico</p><p class=MsoNormal><o:p> </o:p></p><div style='mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='border:none;padding:0cm'><b>From: </b><a href="mailto:tgl@sss.pgh.pa.us">Tom Lane</a><br><b>Sent: </b>martedì 4 maggio 2021 16:34<br><b>To: </b><a href="mailto:cfederico87@gmail.com">Federico</a><br><b>Cc: </b><a href="mailto:pgsql-bugs@lists.postgresql.org">pgsql-bugs@lists.postgresql.org</a><br><b>Subject: </b>Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error</p></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Federico <cfederico87@gmail.com> writes:</p><p class=MsoNormal>> I did not come across that documented behavior while searching for regclass</p><p class=MsoNormal>> in the pg docs. The most relevant page I was able to find was the Object</p><p class=MsoNormal>> Identifier Types page that does not mention it.</p><p class=MsoNormal>> I’ll look into proposing a change in that docs page to mention it, if</p><p class=MsoNormal>> that’s the appropriate location for it.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Hmm ... I *thought* it was documented, but perhaps not. If not,</p><p class=MsoNormal>it's likely that the other reg* types are likewise underdocumented.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> regards, tom lane</p><p class=MsoNormal><o:p> </o:p></p></div></body></html>
[ redirecting to pgsql-docs list ]
I wrote:
Federico <cfederico87@gmail.com> writes:
I did not come across that documented behavior while searching for regclass
in the pg docs. The most relevant page I was able to find was the Object
Identifier Types page that does not mention it.
I'll look into proposing a change in that docs page to mention it, if
that's the appropriate location for it.
Hmm ... I *thought* it was documented, but perhaps not. If not,
it's likely that the other reg* types are likewise underdocumented.
So what I was remembering was some text in the section about sequence
functions. That probably seemed appropriate when they were the only
real use of regclass; but these days we have regclass-accepting functions
all over, not to mention other OID alias types. I propose the attached
patch to move this info into the "Object Identifier Types" section.
regards, tom lane
Attachments:
improve-docs-about-OID-alias-types-1.patchtext/x-diff; charset=us-ascii; name=improve-docs-about-OID-alias-types-1.patchDownload+99-63
On Tue, 2021-05-04 at 19:18 -0400, Tom Lane wrote:
[ redirecting to pgsql-docs list ]
I wrote:
Federico <cfederico87@gmail.com> writes:
I did not come across that documented behavior while searching for regclass
in the pg docs. The most relevant page I was able to find was the Object
Identifier Types page that does not mention it.
I'll look into proposing a change in that docs page to mention it, if
that's the appropriate location for it.Hmm ... I *thought* it was documented, but perhaps not. If not,
it's likely that the other reg* types are likewise underdocumented.So what I was remembering was some text in the section about sequence
functions. That probably seemed appropriate when they were the only
real use of regclass; but these days we have regclass-accepting functions
all over, not to mention other OID alias types. I propose the attached
patch to move this info into the "Object Identifier Types" section.
That patch looks good to me.
I think it is an improvement to have that discussed under "data types".
Yours,
Laurenz Albe
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style></head><body lang=IT link=blue vlink="#954F72" style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal><span lang=EN-US>The patch is also clear for me, thanks for working on it.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Best,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Federico</span></p><p class=MsoNormal><o:p> </o:p></p><div style='mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='border:none;padding:0cm'><b>From: </b><a href="mailto:laurenz.albe@cybertec.at">Laurenz Albe</a><br><b>Sent: </b>mercoledì 5 maggio 2021 09:32<br><b>To: </b><a href="mailto:tgl@sss.pgh.pa.us">Tom Lane</a>; <a href="mailto:cfederico87@gmail.com">Federico</a><br><b>Cc: </b><a href="mailto:pgsql-docs@lists.postgresql.org">pgsql-docs@lists.postgresql.org</a><br><b>Subject: </b>Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error</p></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>On Tue, 2021-05-04 at 19:18 -0400, Tom Lane wrote:</p><p class=MsoNormal>> [ redirecting to pgsql-docs list ]</p><p class=MsoNormal>> </p><p class=MsoNormal>> I wrote:</p><p class=MsoNormal>> > Federico <cfederico87@gmail.com> writes:</p><p class=MsoNormal>> > > I did not come across that documented behavior while searching for regclass</p><p class=MsoNormal>> > > in the pg docs. The most relevant page I was able to find was the Object</p><p class=MsoNormal>> > > Identifier Types page that does not mention it.</p><p class=MsoNormal>> > > I'll look into proposing a change in that docs page to mention it, if</p><p class=MsoNormal>> > > that's the appropriate location for it.</p><p class=MsoNormal>> > Hmm ... I *thought* it was documented, but perhaps not. If not,</p><p class=MsoNormal>> > it's likely that the other reg* types are likewise underdocumented.</p><p class=MsoNormal>> </p><p class=MsoNormal>> So what I was remembering was some text in the section about sequence</p><p class=MsoNormal>> functions. That probably seemed appropriate when they were the only</p><p class=MsoNormal>> real use of regclass; but these days we have regclass-accepting functions</p><p class=MsoNormal>> all over, not to mention other OID alias types. I propose the attached</p><p class=MsoNormal>> patch to move this info into the "Object Identifier Types" section.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>That patch looks good to me.</p><p class=MsoNormal>I think it is an improvement to have that discussed under "data types".</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Yours,</p><p class=MsoNormal>Laurenz Albe</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>
Laurenz Albe <laurenz.albe@cybertec.at> writes:
On Tue, 2021-05-04 at 19:18 -0400, Tom Lane wrote:
So what I was remembering was some text in the section about sequence
functions. That probably seemed appropriate when they were the only
real use of regclass; but these days we have regclass-accepting functions
all over, not to mention other OID alias types. I propose the attached
patch to move this info into the "Object Identifier Types" section.
That patch looks good to me.
I think it is an improvement to have that discussed under "data types".
Pushed, thanks for reviewing.
regards, tom lane
Thanks for the quick resolution.
Best,
Federico
Ps: sorry for the previous messages in html, I had not realized that the
client I was using were sending the replies like that.
On Wed, 5 May 2021 at 17:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Laurenz Albe <laurenz.albe@cybertec.at> writes:
On Tue, 2021-05-04 at 19:18 -0400, Tom Lane wrote:
So what I was remembering was some text in the section about sequence
functions. That probably seemed appropriate when they were the only
real use of regclass; but these days we have regclass-acceptingfunctions
all over, not to mention other OID alias types. I propose the attached
patch to move this info into the "Object Identifier Types" section.That patch looks good to me.
I think it is an improvement to have that discussed under "data types".Pushed, thanks for reviewing.
regards, tom lane