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

út 11. 1. 2022 v 16:51 odesílatel <benj(dot)dev(at)laposte(dot)net> napsal:

> ‌Hi, Thank you for pointing this part of the documentation.
> It's actually works with EXECUTE 'INSERT INTO my_table VALUES((random() *
> 100)::INT);'; INSTEAD OF INSERT INTO my_table VALUES((random() * 100)::INT);
> And it's possible to suppose that the test_search_path_v2 worked before
> because of the
> PERFORM set_config('search_path', '"$user", public', true);
> I imagine that the changement of search_path have on side effect to
> invalidate the cached plans here and force to recompute the query plan.
> I imagine that the probleme appears in test_search_path_v1 after second
> call maybe because the cache is kept by following rules such as a certain
> number of executions
>
> In this example, use EXECUTE only in INSERT INTO my_table is sufficient to
> remove the problem.
> subsequent SELECT works (without the EXECUTE).
> Does doing an EXECUTE on a table have the side effect of invalidating
> caches using that table name or is it just a "chance" here and for added
> security I should use EXECUTE everywhere?
>

EXECUTE uses one shot plan - this plan is not cached. It has not any impact
on others' plans.

Regards

Pavel

> Thanks
>
> De : "David G. Johnston"
> A : "benj(dot)dev(at)laposte(dot)net" ,"pgsql-general(at)postgresql(dot)org"
> Envoyé: mardi 11 Janvier 2022 15:18
> Objet : 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.
>
>