Postgres Stored Procedure Call Function Return Type OID Caching Problem - Mailing list pgsql-bugs

Hello,
=20
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.):
=20
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.
=20
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.
=20

      drop type type_foo cascade;


      CREATE TYPE type_foo AS
      (
            ......

......


);

=20

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 =3D 'INSERT INTO temp_foo_page SELECT * from bar
where  ( date( "timestamp" ) =3D  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;



=20
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.
=20
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:
=20
Here is the results of system tables query. The oid 25266 no longer
exists and the current type oid is somehow changed to 34487???
=20
# select prorettype, proname from pg_proc where proname like 'sp_%';
 prorettype |                       proname
      16450 | sp_session
      25226 | sp_foo
         25 | sp_resolve
......
(12 rows)
=20
=20
spotlite=3D# select * from pg_type where typname =3D 'type_foo';
       typname       | typnamespace | typowner | typlen | typbyval |
typtype |=20
t
ypisdefined | typdelim | typrelid | typelem | typinput  | typoutput  |=20
typreceiv
e  |   typsend   | typanalyze | typalign | typstorage | typnotnull |=20
typbasetype
 | typtypmod | typndims | typdefaultbin | typdefault
---------------------+--------------+----------+--------+----------+----
-----+-
-
------------+----------+----------+---------+-----------+------------+--
-------
-
---+-------------+------------+----------+------------+------------+----
-------
-
-+-----------+----------+---------------+------------
 type_foo |         2200 |       10 |     -1 | f        | c       |=20
t
            | ,        |    34487 |       0 | record_in | record_out |=20
record_re
cv | record_send | -          | d        | x          | f          |

0
 |        -1 |        0 |               |
(1 row)
=20

=20

Any help would be greatly appreciated!

=20

FC