Re: Seems to be impossible to set a NULL search_path

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote

...

You either didn't read or failed or retain knowledge of the words in the documentation that are the canonical reference for search_path and explain exactly this. I suggest you (re-)read them.

https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT  (under search_path)

And elsewhere I'm sure it is written that since temporary objects are session-local it was decided that a useful implementation detail for that would be for each session to have its own temporary schema, hence the appended integer to distinguish them (referencing pg_temp works, the system resolves the session specific schema name for you).

 
Right. Mea maxima culpa. « the current session's temporary-table schema... can be explicitly listed in the path by using the alias pg_temp. »

It seems that the wording is wrong here:

« The value for search_path must be a comma-separated list of schema names. »

It's schema identifiers—and not schema names. Yes?

Here's another test whose outcome surprises me...

Remember that session scoped relation cache we went on about a little while back...I think that by creating the object you got a cache invalidation but simply changing the search_path does not cause a cache invalidation.

The problem was my stupid typo: writing « set search_path = 'pg_catalog, pg_temp'; » when I should *not* have typed those single quotes. Now the demo that I'd intended gets the outcome that I'd expected:

select count(*) from pg_class; --------------<< 399
create temporary table pg_class(k int);
select count(*) from pg_class; --------------<<   0
set search_path = pg_catalog, pg_temp;
select count(*) from pg_class; --------------<< 400

The "Writing SECURITY DEFINER Functions Safely" section explicitly recommends that a subprogram includes a "set search_path" specification. But, as I read it, you're saying that this advice is wrong (at least when a function will be invoked in more than a bare "select" because it prevents inlining.

How should I resolve these two conflicting pieces of advice?

There is no "conflict" - you basically get to choose safety or performance. Though since performance isn't guaranteed nor always a need I would say choose safety unless you've confirmed that you need performance.

Thanks, that's crystal clear now.