Problem with temporary tables

From: Andrea Lombardoni <andrea(at)lombardoni(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Cc: Andrea Terribilini <andrea(dot)terribilini(at)oneoverzero(dot)net>
Subject: Problem with temporary tables
Date: 2010-06-30 12:51:37
Message-ID: AANLkTik4N7ZVfP_uD3LIMTPodjQzWCwftIG9hdXzbZmX@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

I am trying to use temporary tables inside a stored procedure, but I
get a rather puzzling error.

I am currently using PostgreSQL 8.2.7 and this is my stored procedure:

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

-- create tmp-table used to map old-id to new-id
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;

INSERT INTO idmap (oldid, type, newid) VALUES(1, 1, 1);

RETURN 1;
END;
$$ LANGUAGE plpgsql;

The first time I invoke the stored procedure, everything goes fine:

# 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 SQL statement
NOTICE: OOID of idmap 475391180
test
------
1
(1 row)

COMMIT

The second time I invoke the stored procedure, I get an error:

# 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 SQL statement
NOTICE: OOID of idmap 475391184
ERROR: relation with OID 475391180 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

The strange part is that the second time, the OID of the idmap is the
same as the one in the first invocation!

Am I doing something wrong or is this a bug?

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2010-06-30 13:14:55 Re: Problem with temporary tables
Previous Message Vibhor Kumar 2010-06-30 12:12:24 Re: Scheduling backup