Re: Seems to be impossible to set a NULL search_path

On 7/5/22 20:55, Bryn Llewellyn wrote:
> //
>> /david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:
>> /
>>> tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com