On Wed, Jul 6, 2022 at 1:13 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
david.g.johnston@xxxxxxxxx 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-IDENTIFIERS
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.
