Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



‌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?

Thanks
 
De : "David G. Johnston"
A : "benj.dev@xxxxxxxxxxx" ,"pgsql-general@xxxxxxxxxxxxxx"
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.dev@xxxxxxxxxxx> 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.
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux