On Wed, Jul 6, 2022 at 11:50 AM Bryn Llewellyn <b...@yugabyte.com> wrote:
> > It succeeded. And the \d metacommand showed me that I now have a table > pg_temp_3.x. Using a different database, it ends up in "pg_temp_1. What's > going on here? Is "pg_temp" a kind of generic nickname for ANY "pg_temp_N"? > > I hate to realize that I'm failing to understand a fundamental principle. > > It rather looks like the name-res to pg_catalog and (some) pg_temp is > hard-coded and doesn't rely on the reigning search_path. Or, to put it > another way, these two schemas are inevitably at the end of the search_path > no matter what you set explicitly, and never mind that "show search_path" > doesn't show them unless you also put them on the path (again) explicitly. > 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). > > I can't make sense of this wording from "Writing SECURITY > DEFINER Functions Safely": > > « A secure arrangement can be obtained by forcing the temporary schema to > be searched last. To do this, write pg_temp as the last entry > in search_path. » > > If I do this: > > *set search_path = 'pg_catalog, pg_temp';* > *show search_path;* > > Then I see what I set—in that order. But if I set the search_path to empty > (and don't see pg_catalog or pg_temp with "show") PG behaves as if they're > still there. > Those same docs also explain why search_path shows what it does (it is the literally saved value) and to find out how the system actually resolved it at runtime to come up with a final search_path you need to use a different thing (via a function). > Not only as my f() and p() above show. But even, say, "select count(*) > from pg_class". Moreover, this is allowed too: > > > > *set search_path = 'pg_temp, pg_catalog, pg_temp';show search_path;* > Now I see exactly what I set. > If you meant that to be a literal thing you've only identified one very oddly named schema...otherwise yes I get your point. > It seems strange that this is allowed. How does the implementation handle > this when a to-be-resolved name exists nowhere? Does it just crank on, > repeatedly searching where it already failed, right up to the bitter end? > Probably...or maybe it ignores the second reference and it is treated the same as 'pg_temp, pg_catalog' > > Here's another test whose outcome surprises me and seems to be at odds > with what you're saying and what the "Writing SECURITY DEFINER Functions > Safely" section says: > > > > > > > *select count(*) from pg_class; -- 399create temporary table pg_class(k > int);select count(*) from pg_class; -- 0set search_path = 'pg_catalog, > pg_temp';select count(*) from pg_class; -- STILL 0* > Why does the final "select" show that the temp table's name has still > captured the one in pg_catalog even though it's ahead in the path. > 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 "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. David J.
