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

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,

 

‌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@laposte.net" ,"pgsql-general@postgresql.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.dev@laposte.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.