Обсуждение: BUG #1204: user-defined function in transaction

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

On Wed, 4 Aug 2004, PostgreSQL Bugs List wrote:

> 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

This is likely to mean that you're using temporary objects (or
creating/dropping an object) inside the function and not doing so, through
execute which is a known issue with plpgsql's queryplan saving.  Without
seeing the function definition, it's hard to say more.

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