Problem with temporary tables

Поиск
Список
Период
Сортировка
От Andrea Lombardoni
Тема Problem with temporary tables
Дата
Msg-id AANLkTik4N7ZVfP_uD3LIMTPodjQzWCwftIG9hdXzbZmX@mail.gmail.com
обсуждение исходный текст
Ответы Re: Problem with temporary tables  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: Problem with temporary tables  (Pavel Stehule <pavel.stehule@gmail.com>)
Список 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!

В списке pgsql-general по дате отправления:

Предыдущее
От: stanimir petrov
Дата:
Сообщение: pgpool2 + slony 1 in master/slave mode with no load balance and no select replication - slaves handle all reads
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Problem with temporary tables