Re: Seems to be impossible to set a NULL search_path

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com 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.