PostgreSQL Bugs List wrote:
> The following bug has been logged online:
>
> Bug reference: 1204
> Logged by: Golkin Stanislav
>
> Email address: stas@intercom.ru
>
> PostgreSQL version: 7.4
>
> Operating system: FREBSD 4.3
>
> Description: user-defined function in transaction
>
> Details:
>
> User-defined function is called inside transaction block (begin end) in php
> script. There is loop in php script where this PL/pgsql functon is invoked
> several times. On first iteration it cause no mistake, on second it cause
> mistake like this:
>
> ERROR: relation with OID 165645734 does not exist
> CONTEXT: PL/pgSQL function "session_recount_time_sec" line 35 at select
> into variables
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
>
>
> And it doesn't depend on input data. On first loop it's always OK and then
> it's always error
Mmm, I bet you are using temporary table in this fashion:
CREATE OR REPLACE FUNCTION sp_test ( )
RETURNS INTEGER AS'
DECLARE
my_value integer;
BEGIN
CREATE TEMP TABLE test ( a integer );
select a INTO my_value from test limit 1;
drop table test;
return 0;
END;
' LANGUAGE 'plpgsql';
regression=# select sp_test();
sp_test
---------
0
(1 row)
regression=# select sp_test();
ERROR: relation with OID 89367289 does not exist
CONTEXT: PL/pgSQL function "sp_test" line 7 at select into variables
As you can see I got the same error.
I don't know if this is the cleaneast way but you can solve in this way:
CREATE OR REPLACE FUNCTION sp_test ( )
RETURNS INTEGER AS'
DECLARE
my_value integer;
BEGIN
PERFORM * FROM pg_tables
WHERE schemaname = ''pg_temp_1'' AND
tablename = ''test'';
IF NOT FOUND THEN
CREATE TEMP TABLE test ( a integer ) ON COMMIT DELETE ROWS;
END IF;
select a INTO my_value from test limit 1;
return 0;
END;
' LANGUAGE 'plpgsql'
VOLATILE;
regression=# select sp_test();
sp_test
---------
0
(1 row)
regression=# select sp_test();
sp_test
---------
0
(1 row)
Regards
Gaetano Mendola
