Re: Problem with temporary tables

On Wednesday 30 June 2010 6:21:44 am Andrea Lombardoni wrote:
> >> 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

You need to use EXECUTE for the INSERT statement as well per error:

CONTEXT: SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1,
1, 1)" PL/pgSQL function "test" line 16 at SQL statement

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com