Re: temp table problem

Tom Lane wrote:
> What PG version are you testing?  Maybe you need to show a complete
> test case, instead of leaving us to guess at details?

I think that example is bogus. Let's forget that one, and look at the
attached script.

The underlying problem is that when we do GetOverrideSearchPath() in
CreateCachedPlan, the memorized search path doesn't include pg_temp, if
the temp namespace wasn't initialized for the backend yet. When we later
need to revalidate the plan, pg_temp still isn't searched, even if it
now exists.

(On 8.3 and CVS HEAD)

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
DROP TABLE IF EXISTS ambiguous_table;
CREATE TABLE ambiguous_table(which text);
INSERT INTO ambiguous_table VALUES ('permanent');
CREATE OR REPLACE FUNCTION ss () RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  RETURN which FROM ambiguous_table;
END;
$$;

-- Returns 'permanent'
SELECT ss();

-- Replace the table in 'public' schema with a temp table
DROP TABLE ambiguous_table;
CREATE TEMP TABLE ambiguous_table(which text);
INSERT INTO ambiguous_table VALUES ('temp');

-- Should return 'temp', but fails if pg_temp wasn't initialized
-- before executing the function the first time.
SELECT ss();