BUG #17635: The behavior of quote_ident differs from what the doc specifies

The following bug has been logged on the website:

Bug reference:      17635
Logged by:          Bryn Llewellyn
Email address:      bryn@yugabyte.com
PostgreSQL version: 14.5
Operating system:   macOS Big Sur Version 11.7.
Description:

SUMMARY
-------

The quote_ident doc here:

https://www.postgresql.org/docs/11/functions-string.html#FUNCTIONS-STRING-OTHER

says this:

«
Return the given string suitably quoted to be used as an identifier in an
SQL statement string. Quotes are added only if necessary... 
»

But tests show that the returned text is often double-quoted when this is
NOT necessary.

ENVIRONMENT
-----------

 select version(); →

 PostgreSQL 14.5 (Homebrew) on x86_64-apple-darwin20.6.0,
 compiled by Apple clang version 13.0.0 (clang-1300.0.29.30), 64-bit

I'm using a Mac Book with macOS Big Sur Version 11.7.

TESCASE
-------

Try this using psql. It works in the cluster that you get immediately
following a use of initdb. But you can run it from any starting point as
long as you connect as a role that allows creating a temporary function.

«
create function pg_temp.f()
  returns table(z text)
  language plpgsql
as $body$
declare
  j                   int    not null := 0;
  n                   text   not null := '';
  name_texts constant text[] not null :=
    array['农民',    'bønder',   'фермеры',   'किसानों',   'dog house'];

  /*
    Here, "expected" means what empirical tests have shown and not
    what the PG doc tells you to expect.
  */
  expected_identfier_texts constant text[] not null :=
    array['"农民"', '"bønder"', '"фермеры"', '"किसानों"', '"dog house"'];

  stmt constant text not null := 'deallocate ?';
begin
  deallocate all;
  for j in array_lower(name_texts, 1)..array_upper(name_texts, 1) loop
    n := name_texts[j];
    assert quote_ident(n) = expected_identfier_texts[j], 'Actual !=
expected';
    assert quote_ident(n) = format('%I', n),             'quote_ident() !=
format()';

    begin
      execute replace(stmt, '?', n);
    exception
      when invalid_sql_statement_name then
        z := 'Does not need double quotes: '||n; return next;
      when syntax_error then
        z := 'Needs double quotes:         '||n; return next;
    end;
  end loop;
end;
$body$;

\t on
\o output.txt
select pg_temp.f();
\o
\t off
»

Here is output.txt:

«
 Does not need double quotes: 农民
 Does not need double quotes: bønder
 Does not need double quotes: фермеры
 Does not need double quotes: किसानों
 Needs double quotes:         dog house
»


This shows that (double) quotes are (sometimes) added when they are NOT
necessary.

It looks like double quotes are ALWAYS added whenever the input name text
contains any non-ASCII(7) character -- irrespective of whether the input
breaks the usual rules for when it can be used "as is" in the role of an SQL
identifier text.

SIMPLE FIX: TREAT THIS AS JUST A DOC BUG
----------------------------------------

Use new wording like this:

«
The input is the text of an object name (like you see, for example, in
pg_class.relname). The output is the text of the SQL identifier, for use
within the text of an SQL statement or PL/pgSQL source code, to designate
the object with the given name. Sometimes, the text of the input name can be
used "as is" as the text of the corresponding SQL identifier. For example,
if a pg_class query shows relname (for a table) like this:

 some_table

then this SQL statement text is legal and correct:

 drop table some_table;

However, this SQL statement text is also legal and correct:

 drop table "some_table";

Here, the double quotes are neither necessary nor harmful. In contrast, if a
pg_class query shows relname like this:

 some table

then the correct SQL statement text requires the use of double-quoting:

 drop table "some_table";

The SQL identifier text that quote_ident returns is always a legal spelling
of an SQL identifier that will correctly designate the object that the input
names. Sometimes, the input text is returned "as is". But often, surrounding
double quotes are added when they are not needed. This happens whenever the
input text contains a non-ASCII(7) character. Here is a simple example:

 select quote_ident('bønder'); → "bønder"

However, the SQL statement text that uses the text of the name "as is" runs
without error and has the intended effect:

 create table bønder(n int);

Embedded quotes are properly doubled.

The rules that specify when double-quoting is essential are given in section
[4.1.1. Identifiers and Key
Words](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS).
»

The account of quote_ident would benefit from an x-ref to the account of the
format function to say that both functions have the same purpose (to form
the text of a legal and correct SQL identifier from the text of the name of
an object that is to be designated). And it should say that the behavior
w.r.t. over-zealous double-quoting is identical. Correspondingly, the
account of the format function would benefit from an x-ref to this account
of quote_ident.

PREFERRED FIX: TREAT THE DOC AS THE SPEC OF WHAT'S INTENDED AND FIX THE CODE
TO MATCH IT
----------------------------------------------------------------------------------------

The key part of the specified behavior is "Quotes are added ONLY if
necessary" (my emphasis). If the quote_ident behavior is changed to meet
this, then it would be trivial for a user to implement a language SQL
function thus:

  function is_exotic(name in text) returns boolean

Here, "exotic" denotes the kind of name text that requires double-quoting to
form the SQL identifier text that denotes it. (It would be useful to define
this term in the PG doc and to define, say, "simple" for the kind of name
text that does not require, but does allow, double-quoting to form the SQL
identifier text that denotes it.)

A very large body of anecdotal evidence shows that development shops outlaw
the use of exotic names. If quote_ident behaved as the current doc
specifies, then it woud be trivial to police this rule:

 select exists(
     select 1
     from pg_class
     where relname != quote_ident(relname)
   )::text as "exotic name(s) found";

(Here, pg_class is an example. Other catalog tables would have to be queried
too.)

As it happens, the current PG uses only ASCII(7) name texts and so this
query returns false. If a user creates an object thus (still using only
ASCII(7):

 create table "my table"(n int);

then this query:

 select relname as "exotic names"
 from pg_class
 where relname != quote_ident(relname)
 order by 1;

produces this result:

  exotic names 
 --------------
  my table

However, it's obviously prone to false negatives, as this shows:

 create table bønder(n int);

 select relname as "exotic names"
 from pg_class
 where relname != quote_ident(relname)
 order by 1;

This is the new result.

  exotic names 
 --------------
  bønder
  my table

But the name with the text bønder is NOT exotic.