Re: Problem with temporary tables

>> Am I doing something wrong or is this a bug?
>
> The plan is cached, to avoid this problem, use dynamic SQL. In your
> case:
>
> EXECUTE 'CREATE TEMPORARY TABLE idmap ...'

Nice idea, but the problem persists, see log below.

I am beginning to mentally place this into the 'bug' area :)

CREATE OR REPLACE FUNCTION test() RETURNS bigint AS $$
DECLARE

v_oid bigint;
BEGIN

-- create tmp-table used to map old-id to new-id
-- type: 1=skill 3=function
EXECUTE 'CREATE TEMPORARY TABLE idmap (oldid bigint PRIMARY KEY,
type bigint, newid bigint) ON COMMIT DROP';

SELECT INTO v_oid oid FROM pg_class WHERE relname = 'idmap';
RAISE NOTICE 'OOID of idmap %', v_oid;

-- add id mapping (type=1)
INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1);

RETURN 1;
END;
$$ LANGUAGE plpgsql;

# begin;select test();commit;
BEGIN
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"idmap_pkey" for table "idmap"
CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint
PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP"
PL/pgSQL function "test" line 9 at execute statement
NOTICE: OOID of idmap 475391188
test
------
1
(1 row)

COMMIT
# begin;select test();commit;
BEGIN
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"idmap_pkey" for table "idmap"
CONTEXT: SQL statement "CREATE TEMPORARY TABLE idmap (oldid bigint
PRIMARY KEY, type bigint, newid bigint) ON COMMIT DROP"
PL/pgSQL function "test" line 9 at execute statement
NOTICE: OOID of idmap 475391192
ERROR: relation with OID 475391188 does not exist
CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1)"
PL/pgSQL function "test" line 16 at SQL statement
ROLLBACK