On Wed, Jul 6, 2022 at 11:50 AM Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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; -- 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; -- STILL 0Why 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.
