How do I find my own blog?
Postgres Stored Procedure Call Function Return Type OID Caching Problem
Hello Guest		   
  • Login
• Register…
• Start blog		   • Who, Where, When
• What can I do?
• What to Read?		   • Polls
• Avatars
• Interests		   • Cities and Countries
• Random blog
• Users search		   • Search
• Games
• Tests
• QAIX		   • Talxy Chat
• Horoscope
• Online		  
Зарегистрируйся!
QAIX > PostgreSQL database development > Postgres Stored Procedure Call Function Return Type OID Caching Problem 9 May 2008 06:24:18
  Recent blog posts: 
real ventures
My prayer for u: Starting from today, you are moving from glory to glory; anything you lay your hands on…

Of Gold and Current Trends
Gold still hovers over the $860/oz mark. As usual, whenever such a price spike occurs -- and they do not…

Handpunch
if any one want to know about handpunch programming(Rsidll3­­2.dll), plz contact me. Hi hairul…

My Best Person
Hello, I have some one whom i love very much. Love beyond anyone cud think of . This brother of mine…

The valley of life
I WALKED THROUGH THE VALLEY OF LIFE THE VALLEY WAS FILLED WITH ROSE LIFE SEEMED TO BE BEAUTIFUL BUT…

A Poem by Gloria Oelman
:-)­ I knew You once in all your glory When I was unafraid And had no guilt to block Your…

Watch out the network trap
I wonder if everybody notice recent in the msn, our window have nothing to be different? Anyway I am…

RSIDLL32.DLL
Hi All, I done a programme for HandPunch device using RSIDLL32.DLL. I can establish a connection with…

Not until we see there fear !!!
"Can you smell that" I asked my friend as the car lurched forward and sped off into the night…

locking a cell dependant upon…
Hi I have a sheet full of look ups, charts etc. that I have designed for managing projects and…

  They have birthday today: 
islamsabri
  Forums:   
    topic feeds

• QAIX  all topics, blogs and comments

• PHP web-programming

• Perl web-programming

• ASP web-programming

• Visual Basic Programming

• .Net Development

• C/C++ Programming

• Java Programming

• Javascript web-programming

• Apache HTTP Server

• Web-design

• Web-hosting, dedicated, colocation

• HTML web-programming

• XML development

• General Web-programming

• Content Management Systems

• ColdFusion

• Search Engine Optimization

• Hardware

• Software, Computer Help

• MySQL database development

• PostgreSQL database development

• MS Access database development

• Oracle database development

• MS SQL database development

• Informix database development

• Buy / Sell

• Open discussion

• Anonymous
  Discuss: 
Postgres Stored Procedure Call…
Hello, I'm wondering if anyone has experienced the same problem and is it fixed in the new Postgres…

Multilingual Support
hi, Does PostgreSQL support Multilingual data? Is it possible to have rows in different languages like…

Hack credit card ( New Fraud )…
How To Hack Or Steal Credit Card Information HOW TO GAIN VALID CREDIT CARD NUMBERS In August, 1999…

updating
Hi I am working on an update php script, i have a list of the records and at the bottom of the script i…

build error
Yesterdays snapshot fails to build on my NT: gcc -O2 -fno-strict-aliasin­g -Wall…
  Recent forum topics: 
Register Globals &…
I'm converting old code to work with Register globals turned off in php.ini. With it on…

[JBoss jBPM] - Problem during…
Hello all, I want to deploy a very simple process in jbpm Bpel. I could configure and test helloworld…

DBD::Oracle, InstantClient…
Hello, I'm trying to get the Oracle InstantClient to work so we can connect to some remote data…

DBI->connect Error
A very simple script: #!/usr/bin/perl -w use strict; use DBI; my $dbname = "test"; my $host…

DBD::ODBC and cancel()
(DBI 1.604, DBD::ODBC 1.15, WinXP, AS 5.8.8) I'm trying to test cancel of long running queries via…
  Recent forum comments:
RE: Re: Convert .exe to .html Is…
no -appledyl

RE: Register Globals &…
You are working with Register globals turned off, so try…

RE: [JBoss jBPM] - Problem during…
]:-)­ same with me.... r u working on jBPM BPEL

RE: DBD::ODBC and cancel()
Tim Bunce wrote: Thanks for that - as Tim says it is a bug, cancel is not just for select statements…

RE: neopets hacking
Wtf??! Yew just wanna get referrals and prizes don't think I don't knoe that it's not gonna work pplz…

RE: DBD::ODBC and cancel()
On Wed, May 14, 2008 at 03:12:27PM -0700, Dean Arnold wrote: I don't think it should be checking the…

RE: Cannot see AND Windows system…
Steven Perreau wrote: Is this a 64 bit windows machine? I only ask because on 64 bit windows there are…

RE: DBD-ODBC and DBI 1.21
Hi Beadles: "ppm install DBD-ODBC.ppd -nodeps" worked for me also. thanks for the trick…

RE: Cannot see AND Windows system…
Here's the code that does this, maybe some guru can see something wrong! #!/usr/bin/perl # # This…

RE: DBI::_new_handle leak?
On Fri, May 09, 2008 at 11:31:46AM -0500, James R. Leu wrote: You've two choices: cut-down or…
  Moderators:
 Administrator
 Protorioris

Postgres Stored Procedure Call Function Return Type OID Caching Problem

Feng Chen 9 May 2008 06:24:18
 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

OptionsAdd comment
Guest 9 May 2008 06:24:18 [ permanent link ]
 how to add element in text array??
Add comment
 

Add new comment

As:
Login:  Password:  
 
 
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или УК РФ.

QAIX > PostgreSQL database development > Postgres Stored Procedure Call Function Return Type OID Caching Problem 9 May 2008 06:24:18
see also:
RelaxNG parsing problem
Compilation error for…
More XPath problems
see also:
how to copy document object returned by…
[OMT] XML::XPath node and attribute
getting better diagnostics out of…
see also:
xmltwig strange characters
[ANN] XML::SAX::ExpatXS 0.99
Getting defaults from XML Schema
  Copyright © 2001—2008 QAIX
Idea: Miсhael Monashev
You can find the help and FAQ in the Admin's blog.
Write us at:
If you would like to report an abuse of our service, such as a spam message, please .