Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1le4KD-0007ih-46 for pgsql-docs@arkaria.postgresql.org; Tue, 04 May 2021 23:19:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1le4Je-0001yQ-3J for pgsql-docs@arkaria.postgresql.org; Tue, 04 May 2021 23:18:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1le4Jd-0001yH-Kc for pgsql-docs@lists.postgresql.org; Tue, 04 May 2021 23:18:57 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1le4JW-0002bU-Cf for pgsql-docs@lists.postgresql.org; Tue, 04 May 2021 23:18:56 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 144NIm9u3976562; Tue, 4 May 2021 19:18:48 -0400 From: Tom Lane To: Federico cc: pgsql-docs@lists.postgresql.org Subject: Re: BUG #16991: regclass is not case sensitive causing "relation does not exist" error In-reply-to: <3904665.1620138892@sss.pgh.pa.us> References: <16991-bcaeaafa17e0a723@postgresql.org> <3896142.1620136761@sss.pgh.pa.us> <3904665.1620138892@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 04 May 2021 10:34:52 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3976531.1620170298.0@sss.pgh.pa.us> Date: Tue, 04 May 2021 19:18:48 -0400 Message-ID: <3976561.1620170328@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3976531.1620170298.1@sss.pgh.pa.us> [ redirecting to pgsql-docs list ] I wrote: > Federico 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="improve-docs-about-OID-alias-types-1.patch"; charset="us-ascii" Content-ID: <3976531.1620170298.2@sss.pgh.pa.us> Content-Description: improve-docs-about-OID-alias-types-1.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 7c341c8e3f..43f99335dc 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4780,10 +4780,14 @@ SELECT * FROM pg_attribute = - 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, myschema.mytable is acceptable input + for regclass (if there is such a table). That value + might be output as myschema.mytable, or + just mytable, depending on the current search path= . The regproc and regoper alias types will on= ly accept input names that are unique (not overloaded), so they are of limited use; for most uses regprocedure or @@ -4792,6 +4796,86 @@ SELECT * FROM pg_attribute operand. = + + 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 Foo (with + upper case F) taking two integer arguments could b= e + entered as ' "Foo" ( int, integer ) '::regprocedure. + The output would look like "Foo"(integer,integer). + Both the function name and the argument type names could be + schema-qualified, too. + + + + Many built-in PostgreSQL functions accept + the OID of a table, or another kind of database object, and for + convenience are declared as taking regclass (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 nextval() function + takes a sequence relation's OID, so you could call it like this: + +nextval('foo') operates on sequence foo +nextval('FOO') same as above +nextval('"Foo"') operates on sequence Foo +nextval('myschema.foo') operates on myschema= .foo +nextval('"myschema".foo') same as above +nextval('foo') searches search path for foo + + + + + + When you write the argument of such a function as an unadorned + literal string, it becomes a constant of type regclass + (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 early binding behavior is usually desirabl= e for + object references in column defaults and views. But sometimes you m= ight + want late binding where the object reference is resol= ved + at run time. To get late-binding behavior, force the constant to be + stored as a text constant instead of regclass: + +nextval('foo'::text) foo is looke= d up at runtime + + The to_regclass() function and its siblings + can also be used to perform run-time lookups. See + . + + + + + Another practical example of use of regclass + is to look up the OID of a table listed in + the information_schema views, which don't supply + such OIDs directly. One might for example wish to call + the pg_relation_size() function, which requires + the table OID. Taking the above rules into account, the correct way + to do that is + +SELECT table_schema, table_name, + pg_relation_size((quote_ident(table_schema) || '.' || + quote_ident(table_name))::regclass) +FROM information_schema.tables +WHERE ... + + The quote_ident() function will take care of + double-quoting the identifiers where needed. The seemingly easier + +SELECT pg_relation_size(table_name) +FROM information_schema.tables +WHERE ... + + is not recommended, because it will fail for + tables that are outside your search path or have names that require + quoting. + + 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 nextval('my_seq'::regclass), PostgreSQL understands that the default expression depends on the sequence - my_seq; the system will not let the sequence be dr= opped - without first removing the default expression. - regrole is the only exception for the property. Constant= s of this - type are not allowed in such expressions. - - - - - 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. + my_seq; then the system will not let the sequence + be dropped without first removing the default expression. The + alternative of nextval('my_seq'::text) does not + create a dependency. + (regrole is an exception to this property. Constants of = this + type are not allowed in stored expressions.) - = Another identifier type used by the system is xid, or tr= ansaction diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5ae8abff0c..c60d98360f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14429,8 +14429,9 @@ SELECT xmltable.* table_to_xml maps the content of the named table, passed as parameter table. The regclass type accepts strings identifying tables using t= he - usual notation, including optional schema qualifications and - double quotes. query_to_xml executes the + usual notation, including optional schema qualification and + double quotes (see for details). + query_to_xml executes the query whose text is passed as parameter query and maps the result set. cursor_to_xml fetches the indicated number of @@ -17316,49 +17317,9 @@ SELECT setval('myseq', 42, false); Next nextvalregclass argument, which is simply the OID of the seque= nce in the pg_class system catalog. You do not have to = look up the OID by hand, however, since the regclass data type's inpu= t - converter will do the work for you. Just write the sequence name encl= osed - in single quotes so that it looks like a literal constant. For - compatibility with the handling of ordinary - SQL names, the string will be converted to lower ca= se - unless it contains double quotes around the sequence name. Thus: - -nextval('foo') operates on sequence foo -nextval('FOO') operates on sequence foo -nextval('"Foo"') operates on sequence Foo - - The sequence name can be schema-qualified if necessary: - -nextval('myschema.foo') operates on myschema= .foo -nextval('"myschema".foo') same as above -nextval('foo') searches search path for foo - - See for more information about - regclass. + converter will do the work for you. See + for details. - - - - When you write the argument of a sequence function as an unadorned - literal string, it becomes a constant of type regclass. - Since this is really just an OID, it will track the originally - identified sequence despite later renaming, schema reassignment, - etc. This early binding behavior is usually desirable= for - sequence references in column defaults and views. But sometimes you = might - want late binding where the sequence reference is reso= lved - at run time. To get late-binding behavior, force the constant to be - stored as a text constant instead of regclass: - -nextval('foo'::text) foo is looke= d up at runtime - - - - - 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. - - - = = @@ -26474,11 +26435,8 @@ postgres=3D# SELECT * FROM pg_walfile_name_offset= (pg_stop_backup()); regclass argument, which is simply the OID of the table = or index in the pg_class system catalog. You do not = have to look up the OID by hand, however, since the regclass data type's= input - converter will do the work for you. Just write the table name enclos= ed in - single quotes so that it looks like a literal constant. For compatib= ility - with the handling of ordinary SQL names, the strin= g - will be converted to lower case unless it contains double quotes arou= nd - the table name. + converter will do the work for you. See + for details. = ------- =_aaaaaaaaaa0--