Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 9:49 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> adrian(dot)klaver(at)aklaver(dot)com wrote:
>
> Not sure what your point is?
>
>
> Try these two:
>
>
>
>
>
>
> *set timezone = 'America/New_York';show timezone;set timezone =
> "America/New_York";show timezone;*
> Neither causes an error. The "show", in each case, prints the bare value
> with no quotes. It never struck me try try double quotes around
> the timezone argument. I'm shocked that they are silently accepted here and
> seem to have the same effect (in this syntax setting) as single quotes. (I
> realize that quoting is required.) This comes as shock. It seems to be a
> "solution" to problem that I don't have—and it brings confusion. Try this:
>
>
>
> *set search_path = '"x"'; -- Hard to read. It's double-quoted x surrounded
> by single quotes.show search_path;*
> This is the result:
>
>
>
>
> * search_path ------------- """x"""*
> That's a run of three double quotes each side of x. (For sport, try a
> single-quoted x surrounded by double-quotes.) I have no idea what this
> means—or why it's allowed. But the fact that the quoting has different
> effects in my different examples led me to say what I did.
>

It is the documented way to represent a double-quote in an identifier.
Just like '''' (4 single quotes in a row) is a literal that contains just a
single quote.

"Quoted identifiers can contain any character, except the character with
code zero. (To include a double quote, write two double quotes.)"

At the level of discussion you want to have when you encounter unfamiliar
syntax please read the syntax chapter for the related concept (expression
identifiers).

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

>
> When I read "string" in doc, then I understand that a manifest string
> constant (like the timezone setting that I used) must be single-quoted in
> SQL statements and the like. Not double-quoted.
>

In an expression a string contained in single quotes is a literal, a string
contained in double quotes is considered a name/identifier.

SET guc = value;

value is not defined to be some expression. It is defined to be its own
unique thing.

I will agree that you seem to have uncovered at least an inconsistency or a
lack of documentation. I'm still unsure exactly what needs to be done
here, or if I too am missing something. I think part of the answer is that
you can put the value of an identifier anywhere you are expecting a plain
literal. But you cannot put a plain literal in places that are expecting
identifiers. SET is looking for literal values, which it stores, then
interprets as identifiers during use. A query doesn't "store" things for
later use so it looks for and requires actual identifier syntax (no single
quotes) - and more generally uses expressions which likewise care about the
difference.

David J.