Re: Problem with temporary tables

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!

In response to Andrea Lombardoni :
> Hello.
>
>
> 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?

The plan is cached, to avoid this problem, use dynamic SQL. In your
case:

EXECUTE 'CREATE TEMPORARY TABLE idmap ...'

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

>> 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

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

> 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

Thanks, this works and solves my problem.

Still, I find this behaviour to be rather quirky.

Ideally the generated query plan should notice such cases and either
report an error or use the version of the temporary table currently
'live'.

At least this quirk should be highlighted both in the plpgsql
documentation page
http://www.postgresql.org/docs/8.2/interactive/plpgsql-overview.html
and linked from the CREATE TABLE page
http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

I will propose these changes in the appropriate mailing lists.

Have a nice day!

On Wed, Jun 30, 2010 at 2:41 PM, Andrea Lombardoni <andrea(at)lombardoni(dot)ch> wrote:
>> 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
>
> Thanks, this works and solves my problem.
>
> Still, I find this behaviour to be rather quirky.
>

it is fixed on 8.3. So you are out of luck with 8.2 on this one I'm
afraid, gotta go with eXECUTe

--
GJ

Hello

in PostgreSQL 8.2 and older you have to respect one rule - newer to
drop temp table. You don't must do it. After session end, all temp
tables are removed.

you can execute some initialisation part like

CREATE OR REPLACE FUNCTION check_tab()
RETURNS void AS $$
BEGIN
BEGIN
TRUNCATE TABLE foo;
EXCEPTION
WHEN others THEN
CREATE TABLE foo(a int);
END;
RETURN;
END;
$$ LANGUAGE plpgsql;

This problem is solved from 8.3

2010/6/30 Andrea Lombardoni <andrea(at)lombardoni(dot)ch>:
> 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!
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Wednesday 30 June 2010 6:41:18 am Andrea Lombardoni wrote:
> > 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
>
> Thanks, this works and solves my problem.
>
> Still, I find this behaviour to be rather quirky.
>
> Ideally the generated query plan should notice such cases and either
> report an error or use the version of the temporary table currently
> 'live'.

In the temporary table case it does for versions of 8.3+. From release notes for
8.3:

"
Automatically re-plan cached queries when table definitions change or statistics
are updated (Tom)

Previously PL/PgSQL functions that referenced temporary tables would fail if the
temporary table was dropped and recreated between function invocations, unless
EXECUTE was used. This improvement fixes that problem and many related issues.
"

>
> At least this quirk should be highlighted both in the plpgsql
> documentation page
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-overview.html
> and linked from the CREATE TABLE page
> http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

>
> I will propose these changes in the appropriate mailing lists.
>
> Have a nice day!

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

On Wed, Jun 30, 2010 at 9:51 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On Wednesday 30 June 2010 6:41:18 am Andrea Lombardoni wrote:
>> > 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
>>
>> Thanks, this works and solves my problem.
>>
>> Still, I find this behaviour to be rather quirky.
>>
>> Ideally the generated query plan should notice such cases and either
>> report an error or use the version of the temporary table currently
>> 'live'.
>
> In the temporary table case it does for versions of 8.3+. From release notes for
> 8.3:
>
> "
> Automatically re-plan cached queries when table definitions change or statistics
> are updated (Tom)
>
> Previously PL/PgSQL functions that referenced temporary tables would fail if the
> temporary table was dropped and recreated between function invocations, unless
> EXECUTE was used. This improvement fixes that problem and many related issues.

Even in 8.3+ it's still better not to drop tables between function
calls if it can be reasonably done. temp tables are a headache
generally and I try to use them as little as possible.

for cases where 'on commit drop' (transaction local data) might be
used in the cases where arrays are not appropriate for local function
storage, I use a persistent table. Since I generally like being
pedantic, I'll give you a workaround you didn't ask for. :-)

for cases where 'on commit drop' (transaction local data) might be
used in the cases where arrays are not appropriate for local function
storage, I use a persistent table.

create table i_dislike_on_commit_drop
(
tx_time timestamptz default now(),
pid int default pg_backend_pid(),

col1 text,
col2 text, [etc]
);
create index idocd_idx on i_dislike_on_commit_drop(tx_time, pid);

for scratch inserts, you just:
insert into i_dislike_on_commit_drop(col1, col2) values (...)

for select, updates and deletes should you need them, be sure to
WHERE...AND (tx_time, pid) = (now(), pg_backend_pid());

just make sure to delete the records once in a while (you can freely
delete them all...in process records won't get hit). you might be
tempted to use a view to wrap the table and filter out records, I
don't think that's a good idea (requires righting nasty insert/update
rules).

merlin