Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 1:13 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> *david(dot)g(dot)johnston(at)gmail(dot)com <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:*
>
> 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-IDENTIFIER
> S
>
>
> Thanks. That section makes a valiant attempt to distinguish between
> "identifier" (as a token in a SQL statement or, say PL/pgSQL source code)
> and "name" as what the identifier denotes. But (I think) it slips up here:
>
> «
> A convention often used is to write key words in upper case and names in
> lower case, e.g.:
>
> UPDATE my_table SET a = 5;
> »
>
> It should be « to write key words in upper case and unquoted identifiers
> in lower case », yes?
>
>
I would say: ... , and identifiers without quotes and in lower case

>
> The situation is analogous with setting the search_path. I want to talk
> about schemas. Schemas have names. So in SQL syntax, I must denote these
> names by writing identifiers.
>

I think this very example shows why holding that position as an absolute is
problematic.

> It could have been decided that the proper way to display a search_path is
> by listing the schema names (just as \d does for tables). But it was
> decided, instead, to denote the path by the list of identifiers that denote
> the schema names. This doesn't present a huge usability challenge. But it
> is, nevertheless, a rule that you have to learn (which I had) and then
> remember (which I didn't).
>
>
An "identifier" is supposed to exist in the system and when you use it that
existence leads you to the object that is named. search_path accepts
labels that aren't true identifiers because they don't have to exist in the
system.

I'm not saying you are totally wrong and that a different choice somewhere
along in this could have been better, but there is a unique aspect about
search_path and it has manifested in its own unique behavior. Pointing out
it is confusing is fine, but as you haven't actually suggested a reasoned
alternative nor, when you started this conversation, seemed like you had
taken the time to resolve your initial confusions via the documentation, it
is hard to want to spend much time here trying to improve things.

I've enjoyed this conversation precisely because it forces me to dig
deeper, think more critically, and understand the reasons behind the
system's design better. Yet in terms of being able to properly use
search_path to achieve a goal the couple of paragraphs in the documentation
are sufficient for all practical purposes I can see. And do not, with any
frequency, seem to generate questions from our users.

David J.