Обсуждение: 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

Hi Imad,

 

The problem is that I should not have to and cannot re-load the functions every now and then. Why would the type id change and the function still references to the old type id thus fails to get the right results?

 

FC

 

---

From: imad [mailto:immaad@gmail.com]
Sent: Wednesday, January 24, 2007 2:51 PM
To: Feng Chen
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] 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@covergence.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

 

"Feng Chen" <fchen@covergence.com> writes:
> The problem is that I should not have to and cannot re-load the
> functions every now and then. Why would the type id change and the
> function still references to the old type id thus fails to get the right
> results?

I suspect the problem
is not with the return type at all, but with some table or other type
referenced within the function body.