Re: Seems to be impossible to set a NULL search_path - Mailing list pgsql-general

The example sets a search path the does *not* mention pg_catalog.

I hadn't come across this locution:

operator(pg_catalog.=)

But I found an account here:

4.2.5. Operator Invocations
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-OPERATOR-CALLS

To be sure, I tried this:

select 17 operator(pg_catalog.=) 17;

It ran without error and produced TRUE.

I believe that you're saying that because (as I presume) the "normal" definition of the = operator is done by code in the pg_catalog schema, pg_catalog must be on the path for ordinary equality tests to work. (I also found random folks saying this on StackExchange.) So I tried this:

create function f()
  returns boolean
  set search_path = ''
  security definer
  language plpgsql
as $body$
begin
  return (2 + 5) = 7;
end;
$body$;
select f()::text;

It succeeded and said true. I don't know how to explain this outcome. For good measure, I tried this too:

create procedure p()
  set search_path = ''
  security definer
  language plpgsql
as $body$
begin
  create temporary table x(k int);
end;
$body$;
call p();

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.

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:

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. Not only as my f() and p() above show. But even, say, "select count(*) from pg_class". Moreover, this is allowed too:

Now I see exactly what I set. 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?

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:

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.

Might I trouble you to explain more carefully—or to give me a doc reference that will allow me to see why the equality function in my example f() is found when the path for the function is empty? And why my example procedure p() manages to create a temporary table while pg_temp is not on the search_path?