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

Hi Imad,

Yes I'm sure the states of the pg_proc and pg_type are correct. And
CASCADE has always been part of the drop command.

The commands of

drop type type_foo cascade;

create or replace function sp_foo;

are in one single file that got loaded once only when the app is being
started. So it is hard to image why would the type be dropped without
the function being created/replaced.

Thanks!

Feng

________________________________

From: imad [mailto:immaad(at)gmail(dot)com]
Sent: Wednesday, January 24, 2007 3:33 PM
To: Feng Chen
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Postgres Stored Procedure Call Function Return Type
OID Caching Problem

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