From: Tom Lane <tgl@sss.pgh.pa.us>
To: Federico <cfederico87@gmail.com>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
Date: Tue, 04 May 2021 19:18:48 -0400
Message-ID: <3976561.1620170328@sss.pgh.pa.us> (raw)
In-Reply-To: <3904665.1620138892@sss.pgh.pa.us>
References: <16991-bcaeaafa17e0a723@postgresql.org>
<3896142.1620136761@sss.pgh.pa.us>
<CAN19dyfQnRwz=+Ffnr4qfVddZDd7m7LKfuX1PmsFhYY5Z7SpQw@mail.gmail.com>
<3904665.1620138892@sss.pgh.pa.us>
[ 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.patch (text/x-diff, 10.8K) downloaddiff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgmlindex 7c341c8e3f..43f99335dc 100644--- a/doc/src/sgml/datatype.sgml+++ b/doc/src/sgml/datatype.sgml@@ -4780,10 +4780,14 @@ SELECT * FROM pg_attribute
</table>
<para>
- All of the OID alias types for objects grouped by namespace accept- schema-qualified names, and will+ All of the OID alias types for objects that are grouped by namespace+ accept schema-qualified names, and will
display schema-qualified names on output if the object would not
be found in the current search path without being qualified.
+ For example, <literal>myschema.mytable</literal> is acceptable input+ for <type>regclass</type> (if there is such a table). That value+ might be output as <literal>myschema.mytable</literal>, or+ just <literal>mytable</literal>, depending on the current search path.
The <type>regproc</type> and <type>regoper</type> alias types will only
accept input names that are unique (not overloaded), so they are
of limited use; for most uses <type>regprocedure</type> or
@@ -4792,6 +4796,86 @@ SELECT * FROM pg_attribute
operand.
</para>
+ <para>+ The input functions for these types also ignore whitespace, and will+ fold upper-case letters to lower case, except within double quotes;+ this is done to make the syntax rules similar to the way object names+ are written in SQL. Conversely, the output functions will use double+ quotes if needed to make the output be a valid SQL identifier. For+ example, the OID of a function named <literal>Foo</literal> (with+ upper case <literal>F</literal>) taking two integer arguments could be+ entered as <literal>' "Foo" ( int, integer ) '::regprocedure</literal>.+ The output would look like <literal>"Foo"(integer,integer)</literal>.+ Both the function name and the argument type names could be+ schema-qualified, too.+ </para>++ <para>+ Many built-in <productname>PostgreSQL</productname> functions accept+ the OID of a table, or another kind of database object, and for+ convenience are declared as taking <type>regclass</type> (or the+ appropriate OID alias type). This means you do not have to look up+ the object's OID by hand, but can just enter its name as a string+ literal. For example, the <function>nextval()</function> function+ takes a sequence relation's OID, so you could call it like this:+<programlisting>+nextval('foo') <lineannotation>operates on sequence <literal>foo</literal></lineannotation>+nextval('FOO') <lineannotation>same as above</lineannotation>+nextval('"Foo"') <lineannotation>operates on sequence <literal>Foo</literal></lineannotation>+nextval('myschema.foo') <lineannotation>operates on <literal>myschema.foo</literal></lineannotation>+nextval('"myschema".foo') <lineannotation>same as above</lineannotation>+nextval('foo') <lineannotation>searches search path for <literal>foo</literal></lineannotation>+</programlisting>+ </para>++ <note>+ <para>+ When you write the argument of such a function as an unadorned+ literal string, it becomes a constant of type <type>regclass</type>+ (or the appropriate type).+ Since this is really just an OID, it will track the originally+ identified object despite later renaming, schema reassignment,+ etc. This <quote>early binding</quote> behavior is usually desirable for+ object references in column defaults and views. But sometimes you might+ want <quote>late binding</quote> where the object reference is resolved+ at run time. To get late-binding behavior, force the constant to be+ stored as a <type>text</type> constant instead of <type>regclass</type>:+<programlisting>+nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at runtime</lineannotation>+</programlisting>+ The <function>to_regclass()</function> function and its siblings+ can also be used to perform run-time lookups. See+ <xref linkend="functions-info-catalog-table"/>.+ </para>+ </note>++ <para>+ Another practical example of use of <type>regclass</type>+ is to look up the OID of a table listed in+ the <literal>information_schema</literal> views, which don't supply+ such OIDs directly. One might for example wish to call+ the <function>pg_relation_size()</function> function, which requires+ the table OID. Taking the above rules into account, the correct way+ to do that is+<programlisting>+SELECT table_schema, table_name,+ pg_relation_size((quote_ident(table_schema) || '.' ||+ quote_ident(table_name))::regclass)+FROM information_schema.tables+WHERE ...+</programlisting>+ The <function>quote_ident()</function> function will take care of+ double-quoting the identifiers where needed. The seemingly easier+<programlisting>+SELECT pg_relation_size(table_name)+FROM information_schema.tables+WHERE ...+</programlisting>+ is <emphasis>not recommended</emphasis>, because it will fail for+ tables that are outside your search path or have names that require+ quoting.+ </para>+
<para>
An additional property of most of the OID alias types is the creation of
dependencies. If a
@@ -4801,19 +4885,13 @@ SELECT * FROM pg_attribute
expression <literal>nextval('my_seq'::regclass)</literal>,
<productname>PostgreSQL</productname>
understands that the default expression depends on the sequence
- <literal>my_seq</literal>; the system will not let the sequence be dropped- without first removing the default expression.- <type>regrole</type> is the only exception for the property. Constants of this- type are not allowed in such expressions.- </para>-- <note>- <para>- The OID alias types do not completely follow transaction isolation- rules. The planner also treats them as simple constants, which may- result in sub-optimal planning.+ <literal>my_seq</literal>; then the system will not let the sequence+ be dropped without first removing the default expression. The+ alternative of <literal>nextval('my_seq'::text)</literal> does not+ create a dependency.+ (<type>regrole</type> is an exception to this property. Constants of this+ type are not allowed in stored expressions.)
</para>
- </note>
<para>
Another identifier type used by the system is <type>xid</type>, or transaction
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex 5ae8abff0c..c60d98360f 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -14429,8 +14429,9 @@ SELECT xmltable.*
<function>table_to_xml</function> maps the content of the named
table, passed as parameter <parameter>table</parameter>. The
<type>regclass</type> type accepts strings identifying tables using the
- usual notation, including optional schema qualifications and- double quotes. <function>query_to_xml</function> executes the+ usual notation, including optional schema qualification and+ double quotes (see <xref linkend="datatype-oid"/> for details).+ <function>query_to_xml</function> executes the
query whose text is passed as parameter
<parameter>query</parameter> and maps the result set.
<function>cursor_to_xml</function> fetches the indicated number of
@@ -17316,49 +17317,9 @@ SELECT setval('myseq', 42, false); <lineannotation>Next <function>nextval</fu
a <type>regclass</type> argument, which is simply the OID of the sequence in the
<structname>pg_class</structname> system catalog. You do not have to look up the
OID by hand, however, since the <type>regclass</type> data type's input
- converter will do the work for you. Just write the sequence name enclosed- in single quotes so that it looks like a literal constant. For- compatibility with the handling of ordinary- <acronym>SQL</acronym> names, the string will be converted to lower case- unless it contains double quotes around the sequence name. Thus:-<programlisting>-nextval('foo') <lineannotation>operates on sequence <literal>foo</literal></lineannotation>-nextval('FOO') <lineannotation>operates on sequence <literal>foo</literal></lineannotation>-nextval('"Foo"') <lineannotation>operates on sequence <literal>Foo</literal></lineannotation>-</programlisting>- The sequence name can be schema-qualified if necessary:-<programlisting>-nextval('myschema.foo') <lineannotation>operates on <literal>myschema.foo</literal></lineannotation>-nextval('"myschema".foo') <lineannotation>same as above</lineannotation>-nextval('foo') <lineannotation>searches search path for <literal>foo</literal></lineannotation>-</programlisting>- See <xref linkend="datatype-oid"/> for more information about- <type>regclass</type>.+ converter will do the work for you. See <xref linkend="datatype-oid"/>+ for details.
</para>
-- <note>- <para>- When you write the argument of a sequence function as an unadorned- literal string, it becomes a constant of type <type>regclass</type>.- Since this is really just an OID, it will track the originally- identified sequence despite later renaming, schema reassignment,- etc. This <quote>early binding</quote> behavior is usually desirable for- sequence references in column defaults and views. But sometimes you might- want <quote>late binding</quote> where the sequence reference is resolved- at run time. To get late-binding behavior, force the constant to be- stored as a <type>text</type> constant instead of <type>regclass</type>:-<programlisting>-nextval('foo'::text) <lineannotation><literal>foo</literal> is looked up at runtime</lineannotation>-</programlisting>- </para>-- <para>- Of course, the argument of a sequence function can be an expression- as well as a constant. If it is a text expression then the implicit- coercion will result in a run-time lookup.- </para>- </note>-
</sect1>
@@ -26474,11 +26435,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
<type>regclass</type> argument, which is simply the OID of the table or index
in the <structname>pg_class</structname> system catalog. You do not have to look up
the OID by hand, however, since the <type>regclass</type> data type's input
- converter will do the work for you. Just write the table name enclosed in- single quotes so that it looks like a literal constant. For compatibility- with the handling of ordinary <acronym>SQL</acronym> names, the string- will be converted to lower case unless it contains double quotes around- the table name.+ converter will do the work for you. See <xref linkend="datatype-oid"/>+ for details.
</para>
<para>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, cfederico87@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error
In-Reply-To: <3976561.1620170328@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox