Thread
Commits
-
Doc: improve and centralize the documentation for OID alias types.
- f33a178a3480 14.0 landed
-
BUG #16991: regclass is not case sensitive causing "relation does not exist" error
The Post Office <noreply@postgresql.org> — 2021-05-04T10:53:56Z
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
-
Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-04T13:59:21Z
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
-
RE: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Federico <cfederico87@gmail.com> — 2021-05-04T14:29:32Z
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
-
Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-04T14:34:52Z
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
-
RE: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Federico <cfederico87@gmail.com> — 2021-05-04T14:43:00Z
<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> -
Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-04T23:18:48Z
[ 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
-
Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Laurenz Albe <laurenz.albe@cybertec.at> — 2021-05-05T07:32:14Z
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
-
RE: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Federico <cfederico87@gmail.com> — 2021-05-05T07:53:41Z
<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> -
Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-05T15:28:31Z
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
-
Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Federico <cfederico87@gmail.com> — 2021-05-05T19:17:49Z
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: > 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 >