Re: BUG #1204: user-defined function in transaction

PostgreSQL Bugs List wrote:

> The following bug has been logged online:
>
> Bug reference: 1204
> Logged by: Golkin Stanislav
>
> Email address: stas(at)intercom(dot)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