> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.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.
