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