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