Re: plpgsql function problem whith creating temp table - not correctly using search_path ?

benj(dot)dev(at)laposte(dot)net writes:
> -- Executing each row on differents transactions but in the same session
> /*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true);  -- => OK takes table from pg_temp (no existing table in public)
> /*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false); -- => OK takes table from public
> /*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true);  -- => OK takes table from pg_temp (and the existing from public)
> /*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false); -- => OK takes table from public
> /*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true);  -- => NOK => it takes public and not pg_temp

I think what you've done here is maneuver the plpgsql function into a
state where it has a cached query plan [1] using the public table.
Dropping the public table, or changing your search_path, would
invalidate that cached plan ... but creating a new temp table does not.
(This isn't specific to temp tables --- in any situation where you have
more than one schema in the search path, creation of a new object could
mask objects later in the path, and we won't invalidate plans just
because that possibly happened.)

My advice is "don't do that", ie, avoid masking permanent objects
with temporary ones. It's extremely confusing to humans as well
as machines. If you must do it, DISCARD PLANS might help you
with keeping plpgsql functions in line.

regards, tom lane

[1] https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING