Re: Seems to be impossible to set a NULL search_path

On Tue, Jul 5, 2022 at 12:13 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Bryn Llewellyn <bryn(at)yugabyte(dot)com> writes:
> > Thanks, all, for your replies. I'd assumed that the arguments of "set
> search_path" had to be SQL names. so I tried "". But that caused an error.
> I didn't try the ordinary empty string because I'd assumed that, as an
> illegal SQL name, it would be illegal in "set search_path". Hmm...
>
> 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).

("testschema" exists, schemas "a" and "b" do not)
^
postgres=# set search_path to 'a, b, testschema';
SET
postgres=# show search_path;
search_path
--------------------
"a, b, testschema"
(1 row)

postgres=# create table inab (id serial primary key);
ERROR: no schema has been selected to create in
LINE 1: create table inab (id serial primary key);
^
postgres=# set search_path to a, b, testschema;
SET
postgres=# show search_path;
search_path
------------------
a, b, testschema
(1 row)

postgres=# create table inab (id serial primary key);
CREATE TABLE
postgres=#

postgres=# set search_path to '';
SET
postgres=# show search_path;
search_path
-------------
""
(1 row)

postgres=# set search_path to "";
ERROR: zero-length delimited identifier at or near """"
LINE 1: set search_path to "";
^
postgres=# create table testtable."" (id serial primary key);
ERROR: zero-length delimited identifier at or near """"
LINE 1: create table testtable."" (id serial primary key);
^
postgres=#

So you cannot actually write an identifier that is double quoted empty
string but if you write < SET search_path TO '' > that is basically what
the system thinks you have done.

postgres=# set search_path to '';
SET
postgres=# create table "es" (id serial primary key);
ERROR: no schema has been selected to create in
LINE 1: create table "es" (id serial primary key);
^
postgres=#

Thus, it must be set using a list of identifiers, separated by commas, but
single quotes can be used instead of double quotes if desired, and single
quotes must be used to set it to a value where there are no additional
schemas added to the mandatory (and thus implied) < pg_temp, pg_catalog >
specification.

A bit off topic: I'm not sure how you came to the conclusion that
> superusers can't write into pg_catalog. They can.

True, but they need to jump through the hoop of modifying a setting before
they are allowed to do so.

David J.