Re: Seems to be impossible to set a NULL search_path

On 7/5/22 20:55, Bryn Llewellyn wrote:
> //
>> /david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> /
>>> tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>
>>> search_path's value is not a SQL name.  It's a list of SQL
>>> names wrapped in a string ... and the list can be empty.
>>
>> This doesn't seem to be correct - wrapping them in single quotes in
>> the SET command ends up behaving as if you wrapped them in double
>> quotes anywhere else (and wrapping them individually in double quotes
>> here works just fine too).
>
> And then...
>
>> /adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> wrote:/
>>
>> Those are creating objects. Set search_path is setting a configuration
>> value. Pretty sure it is:
>>
>> { TO | = } { value | 'value' | DEFAULT
>
> There's different use cases. For example:
>
> *set my_namspace.x = 'Dog house';*
> *show my_namspace.x ;

Not sure what your point is?

> *
> I can't reconcile what you three (Tom, David, and Adrian) have said. I'm
> interested to hear how you interpret what I showed in this reply:
>
> https://www.postgresql.org/message-id/48E1391E-5A21-4736-B4B1-8B9468ECAFD4%40yugabyte.com
> <https://www.postgresql.org/message-id/48E1391E-5A21-4736-B4B1-8B9468ECAFD4%40yugabyte.com>
>
> and in particular to this:
>
> *create schema "s1, s2";
> create table "s1, s2".t(k int);
> insert into "s1, s2".t(k) values(42);
> set search_path = "s1, s2";*
> *show **search_path;**
> select k from t;*
>
> OR (with single quotes in "set search_path":
>
> *create schema "s1, s2";
> create table "s1, s2".t(k int);
> insert into "s1, s2".t(k) values(42);
> set search_path = 's1, s2';
> **show **search_path;**
> **select k from t;
> *

From here
https://www.postgresql.org/docs/current/runtime-config-client.html:

search_path (string)
...
The value for search_path must be a comma-separated list of schema names
...

By quoting the above in either single or double quotes you change what
looks like a list pf schemas into a single schema:

show search_path;
search_path
-------------
"s1, s2"

If you want that to be a list of schemas then:

set search_path = s1, s2;
SET

show search_path;
search_path
-------------
s1, s2

> I get a resounding 42 in both cases. Now try this:
>
> *set search_path = no_such_schema, "No Such Schema";*
> *show **search_path;**

Which is same as:

set search_path = no_such_schema, 'No Such Schema';

show search_path;
search_path
----------------------------------
no_such_schema, "No Such Schema"

Since the list of names will end up being identifiers for schema any
name with spaces needs to be quoted. Otherwise:

set search_path = no_such_schema, No Such Schema;
ERROR: syntax error at or near "Such"
LINE 1: set search_path = no_such_schema, No Such Schema;

> *
>
> All outcomes accord with the mental model that you tell me is wrong.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com