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

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

> Hi,
>
> I would like to submit a problem (bug ?) that I encountered while handling
> temporary tables in plpgsql functions.
>
> First, if I create a TABLE and a TEMP TABLE with the same name, and I
> request without specified the schema, the temporary table is used.
>
> -- SHOW search_path; -- => "$user", public
> -- Exectute next commands in the same transaction
> /* Début Transaction 1 */
> DROP TABLE IF EXISTS public.myexemple;
> DROP TABLE IF EXISTS pg_temp.myexemple;
> CREATE TABLE IF NOT EXISTS myexemple(i INT);
> CREATE TEMP TABLE IF NOT EXISTS myexemple(i INT) ON COMMIT DROP;
> INSERT INTO public.myexemple VALUES(1);
> INSERT INTO public.myexemple VALUES(2);
> INSERT INTO public.myexemple VALUES(3);
> INSERT INTO myexemple VALUES(7);
> SELECT 'public', * FROM public.myexemple
> UNION ALL SELECT 'noschema', * FROM myexemple
> UNION ALL SELECT 'pg_temp', * FROM pg_temp.myexemple;
> /* Fin Transaction 1 */
> =>
> public;1
> public;2
> public;3
> noschema;7
> pg_temp;7
>
> Here, all is fine It's the expected behavior.
>
> But If I create the the TEMP TABLE in a function, it's (sometimes) the
> public table which is used and not the temporary table.
>
> CREATE OR REPLACE FUNCTION test_search_path_v1(tmp boolean)
> RETURNS TABLE(ori text, i int)
> LANGUAGE plpgsql
> AS $_$
> DECLARE
> txt text;
> BEGIN
> CREATE TEMP TABLE IF NOT EXISTS return_table(ori TEXT, i INT) ON COMMIT
> DROP;
>
> IF $1 THEN
> CREATE TEMP TABLE IF NOT EXISTS my_table(i INT) ON COMMIT DROP;
> ELSE
> CREATE TABLE IF NOT EXISTS my_table(i INT);
> END IF;
>
> SELECT setting FROM pg_settings WHERE name = 'search_path' INTO txt;
> RAISE INFO 'search_path = %', txt;
>
> INSERT INTO my_table VALUES((random() * 100)::INT);
>
> FOR txt IN SELECT schemaname FROM pg_tables WHERE tablename = 'my_table'
> LOOP
> RAISE INFO '==> %', txt;
> END LOOP;
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname ='public' AND
> tablename = 'my_table') THEN
> RAISE INFO 'public.my_table exists';
> INSERT INTO return_table SELECT 'public', t.i FROM public.my_table t;
> END IF;
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname LIKE 'pg_temp%' AND
> tablename = 'my_table') THEN
> RAISE INFO 'pg_temp.my_table exists';
> INSERT INTO return_table SELECT 'pg_temp', t.i FROM pg_temp.my_table
> t;
> END IF;
>
> INSERT INTO return_table SELECT '', t.i FROM my_table t;
> RETURN QUERY SELECT t.ori, t.i FROM return_table t;
> END;
> $_$;
>
> 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
>
>
I cannot reproduce any fails in this test.

> In fact, if I call 0 or 1 time "test_search_path_v1(false)" all the call
> with "true" will be ok.
> But AFTER the second call with false, all subsequent call with true will
> failed
>

What is error message?

Regards

Pavel

> => using public instead of pg_temp for the INSERT INTO my_table
> VALUES((random() * 100)::INT)
>
> If I do the test with changing session before exh call, the problem
> doesn't appear
> -- Executing each row on differents session
> /*Session A */ SELECT * FROM test_search_path_v1(true); -- => OK takes
> table from pg_temp (no existing table in public)
> /*Session B */ SELECT * FROM test_search_path_v1(false); -- => OK takes
> table from public
> /*Session C */ SELECT * FROM test_search_path_v1(true); -- => OK takes
> table from pg_temp (and the existing from public)
> /*Session D */ SELECT * FROM test_search_path_v1(false); -- => OK takes
> table from public
> /*Session E */ SELECT * FROM test_search_path_v1(true); -- => OK takes
> table from pg_temp (and the existing from public)
>
>
> It's possible to bypass te problem with enforce the use of pg_temp like in
> this second version.
>
> CREATE OR REPLACE FUNCTION test_search_path_v2(tmp boolean) RETURNS
> table(ori text, i int)
> LANGUAGE plpgsql
> AS $_$
> DECLARE
> txt text;
> BEGIN
> CREATE TEMP TABLE IF NOT EXISTS return_table(ori TEXT, i INT) ON COMMIT
> DROP;
>
> IF $1 THEN
> PERFORM set_config('search_path', 'pg_temp, "$user", public', true);
> -- is_local = true
> CREATE TEMP TABLE IF NOT EXISTS my_table(i INT) ON COMMIT DROP;
> ELSE
> PERFORM set_config('search_path', '"$user", public', true); --
> is_local = true
> CREATE TABLE IF NOT EXISTS my_table(i INT);
> END IF;
>
> SELECT setting FROM pg_settings WHERE name = 'search_path' INTO txt;
> RAISE INFO 'search_path = %', txt;
>
> INSERT INTO my_table VALUES((random() * 100)::INT);
>
> FOR txt IN SELECT schemaname FROM pg_tables WHERE tablename = 'my_table'
> LOOP
> RAISE INFO '==> %', txt;
> END LOOP;
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname LIKE 'pg_temp%' AND
> tablename = 'my_table') THEN
> RAISE INFO 'pg_temp.my_table exists';
> INSERT INTO return_table SELECT 'pg_temp', t.i FROM pg_temp.my_table
> t;
> END IF;
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname ='public' AND
> tablename = 'my_table') THEN
> RAISE INFO 'public.my_table exists';
> INSERT INTO return_table SELECT 'public', t.i FROM public.my_table t;
> END IF;
>
> INSERT INTO return_table SELECT '', t.i FROM my_table t;
> RETURN QUERY SELECT t.ori, t.i FROM return_table t;
> END;
> $_$;
>
> 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); --
> => OK takes table from pg_temp (and the existing from public)
>
> SELECT version() -- PostgreSQL 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) on
> x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0,
> 64-bit
>
> I can bypass but I would like to understand what is the cause of this
> problem to avoid unexpected problems.
> I don't understand why the probleme appears afters some (two) calls that
> works with public table and not everytime.
> I don't understand why I need to explicit pg_temp in first choice of
> search_path. I thought that is the default behavior.
> I don't understant why there may be a difference between case in different
> session and case in same session. Is the problem linked with the reuse of
> number of pg_temp_X ?
>
> I have searched and found a thread with similarity
>
> https://dba.stackexchange.com/questions/60997/is-this-temp-table-behaviour-documented
> relied on
>
> https://www.postgresql.org/message-id/20140315165011.20722.74795@wrigleys.postgresql.org
> but in this case the problem is between SQL VS PLPGSQL and it seems to
> demonstrate that in PLPGSQL the normal attemps is to use pg_temp first.
>
> Thanks for yours helps,
> Regards,
>
>