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

From Tom Lane
Subject Re: plpgsql function problem whith creating temp table - not correctly using search_path ?
Date
Msg-id 2589876.1641914327@sss.pgh.pa.us
Whole thread Raw
In response to plpgsql function problem whith creating temp table - not correctly using search_path ?  (benj.dev@laposte.net)
List pgsql-general
benj.dev@laposte.net writes:
> -- 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
existingtable 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
existingfrom 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 think what you've done here is maneuver the plpgsql function into a
state where it has a cached query plan [1] using the public table.
Dropping the public table, or changing your search_path, would
invalidate that cached plan ... but creating a new temp table does not.
(This isn't specific to temp tables --- in any situation where you have
more than one schema in the search path, creation of a new object could
mask objects later in the path, and we won't invalidate plans just
because that possibly happened.)

My advice is "don't do that", ie, avoid masking permanent objects
with temporary ones.  It's extremely confusing to humans as well
as machines.  If you must do it, DISCARD PLANS might help you
with keeping plpgsql functions in line.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
Next
From: Tom Lane
Date:
Subject: Re: Folding of case of identifiers