From: | Andrea Lombardoni <andrea(at)lombardoni(dot)ch> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem with temporary tables |
Date: | 2010-06-30 13:21:44 |
Message-ID: | AANLkTilkX1oF2RtgZjaYR65rF29WRK5aT8oSHAYLM_vs@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2010-06-30 13:27:50 | Re: Problem with temporary tables |
Previous Message | A. Kretschmer | 2010-06-30 13:14:55 | Re: Problem with temporary tables |