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
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
=> 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@xxxxxxxxxxxxxxxxxxxxxxx
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,
