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

You are not looking at the right type OID. 34487 is not the type oid, its
the OID of the corresponding implicit relation.
The problem is the compiled state of your function which persists for
performance reasons. You told yourself that this error goes away when you
recreate the function.

--Imad
www.EnterpriseDB.com

On 1/25/07, Feng Chen <fchen(at)covergence(dot)com> wrote:
>
> 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
>