Re: Seems to be impossible to set a NULL search_path

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tue, Jul 5, 2022 at 12:13 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.