Skip site navigation (1) Skip section navigation (2)

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

From: imad <immaad(at)gmail(dot)com>
To: "Feng Chen" <fchen(at)covergence(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Postgres Stored Procedure Call Function Return Type OID Caching Problem
Date: 2007-01-24 20:33:23
Message-ID: 1f30b80c0701241233m6be5c9ffj38be2d10e4875e82@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-odbc
Well, there is somehow you are dropping the type and recreating it without
updating the function?
BTW, are you sure about these states of pg_proc and pg_type? Are you sure
you drop the custom type
always using the CASCADE keyword?

--Imad
www.EnterpriseDB.com


On 1/25/07, Feng Chen <fchen(at)covergence(dot)com> wrote:
>
>  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(at)gmail(dot)com]
> *Sent:* Wednesday, January 24, 2007 2:51 PM
> *To:* Feng Chen
> *Cc:* pgsql-odbc(at)postgresql(dot)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(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
>
>
>

In response to

Responses

pgsql-odbc by date

Next:From: noreplyDate: 2007-01-24 21:08:48
Subject: [ psqlodbc-Bugs-1001172 ] Problem with ADO.RecordCount
Previous:From: Feng ChenDate: 2007-01-24 20:15:38
Subject: Re: Postgres Stored Procedure Call Function Return Type OID Caching Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group