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

On Tuesday, January 11, 2022, <benj(dot)dev(at)laposte(dot)net> wrote:

>
> SHOW search_path -- => "$user", public;
> DROP TABLE IF EXISTS my_table;
> -- 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
>

Per [1], you are violating:

“Because PL/pgSQL saves prepared statements and sometimes execution plans
in this way, SQL commands that appear directly in a PL/pgSQL function must
refer to the same tables and columns on every execution; that is, you
cannot use a parameter as the name of a table or column in an SQL command.”

While that wording is odd, it is not even possible to use variables in
place of table and column names, what you are doing here is effectively the
same. I cannot explain the precise observed behavior, and maybe there is a
bug here, but on its face what you are doing is simply invalid in face of
how the session relation cache works.

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

David J.