benj.dev@xxxxxxxxxxx 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 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 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
