Re: Postgres Stored Procedure Call Function Return Type OID Caching Problem

Hello,

I'm wondering if anyone has experienced the same problem and is it fixed in the new Postgres release (We use Posgtgres 8.1.2, psqlodbc.so 7.2.5 (moving to 8.2.200), unixODBC 2.2.11 (moving to 
2.2.12), running on Linux OS.):

We have a recurring problem that the return type (a customer defined type) was changed somehow, which resulted in a failure: lookup for that type failed when trying to call that function.

Specifically, we have a file defining the following type and function. It is loaded at the start of the Postgres start time and the function then is called many times.

      drop type type_foo cascade;

      CREATE TYPE type_foo AS
      (
            ……

……

);

CREATE OR REPLACE FUNCTION sp_foo(int4, int4 , text, text , text , text, text , text)
  RETURNS SETOF type_foo AS
$BODY$
  DECLARE
      temp_row type_foo ; /** the type of data to be returned **/
      insert_statement text; /** placeholder for populating the page **/
   BEGIN


        /** create a temp table to put everything in **/
        EXECUTE 'CREATE TEMP TABLE temp_foo_page
        (
            ……

……

        ) ';

       /** build the insert statement to load initial values from cxcsession **/
       insert_statement = 'INSERT INTO temp_foo_page SELECT * from bar where  ( date( "timestamp" ) =  date( ''' || DATE_TO_USE || ''' )) ' ;

            ……

……

       /** execute the select **/
       EXECUTE insert_statement;

      /** return the result set **/
      FOR temp_row IN EXECUTE 'SELECT * FROM temp_foo_page order by "' || SORT_COLUMN || '" desc '
      LOOP
          RETURN NEXT temp_row ;
      END LOOP;

      /** drop the table we no longer need **/
      EXECUTE 'drop table temp_foo_page ';

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION sp_foo(int4, int4 , text, text , text , text, text , text) OWNER TO postgres;

The problem after a period of time (10 days or so not consistent), function sp_foo referenced an oid for an old return type that no longer existed. We had to explicitly drop the function sp_foo reload from the SQL source file to correct the problem.

I knew that there was a bug of Postgres with sp functions caching the temp table ids, and there is a temp table used in this function, but the problem is with the type id changing underneath:

Here is the results of system tables query. The oid 25266 no longer exists and the current type oid is somehow changed to 34487???

# select prorettype, proname from pg_proc where proname like 'sp_%';

 prorettype |                       proname

      16450 | sp_session

      25226 | sp_foo

         25 | sp_resolve

……

(12 rows)

spotlite=# select * from pg_type where typname = 'type_foo';

       typname       | typnamespace | typowner | typlen | typbyval | typtype | 

t

ypisdefined | typdelim | typrelid | typelem | typinput  | typoutput  | 

typreceiv

e  |   typsend   | typanalyze | typalign | typstorage | typnotnull | 

typbasetype

 | typtypmod | typndims | typdefaultbin | typdefault

---------------------+--------------+----------+--------+----------+---------+-

-

------------+----------+----------+---------+-----------+------------+---------

-

---+-------------+------------+----------+------------+------------+-----------

-

-+-----------+----------+---------------+------------

 type_foo |         2200 |       10 |     -1 | f        | c       | 

t

            | ,        |    34487 |       0 | record_in | record_out | 

record_re

cv | record_send | -          | d        | x          | f          |           

0

 |        -1 |        0 |               |

(1 row)

 

Any help would be greatly appreciated!

 

FC