plpgsql function problem whith creating temp table - not correctly using search_path ? - Mailing list pgsql-general

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