Why can't I log in from another PC?
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: 
Free iPod nano raffle for…
Aimersoft 2008 Christmas promotion: "Free iPod nano raffle for Chrismas" and "Buy 1 Get…

How to convert Rhapsody to…
Music purchased from Rhapsody is under DRM protection, which stops customers from playing the downloaded&helli­p;

PS3 firmware update adds full…
Eric Lempel, Sony's Director of PlayStation Network Operations, has posted on the official PlayStation 3…

Nintendo makes $6 profit on…
According to Macquarie Securities analyst David Gibson, Nintendo makes $6 USD operating profit on each…

Maintenance in winter
Skin for winter very easy to do, if does not supplement the moisture in time, like persimmons, flat and…

'Jasper' Xbox 360s finally…
'Jasper' Xbox 360s finally available According to a flooding of posts over at xbox-scene, it appears…

How to transfer Napster music…
Music purchased from Napster is under DRM protection, which stops customers from playing the downloaded&helli­p;

The song my brother taught me
Don't let your heart be hardened Don't let your love grow cold May it always stay so childlike May it…

My brother taught me this…
God grant me the serenity to accept the things I cannot change; courage to change the things I can;…

baz-baz-baz-baz pam-parabaram…
baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram-baz ba-ra-ba…

  They have birthday today: 
xiongchong
henna diah
  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: 
plpgsql - Insert from a record…
Dear Postgresql experts, I'm writing a simple plpgsql function that selects a row from a table…

calling a pl/pgsql function…
Hello, i am tryin to pass an array like argument to a pl/pgsql function but i can not find the correct…

PgDatabase, on heap…
I am having a problem constructing a PgDatabase object. If I create the object on the stack (as a var…

Backup and restore through JDBC
Hello List I am trying to develop, a API to carry through backup and restore through JDBC. I think that…
  Recent forum topics: 
why infix:<div>:(Int, Int…
HaloO, I realized from the typed literal thread that S03 now explicitly states that div on two Ints…

[rt.cpan.org #41329] VMS…
<URL: http://rt.cpan.org/­Ticket/Display.html?­id=41329 > Thanks, applied

how to write literals of some…
Hello, I'm wondering how to write anonymous value literals of some Perl 6 basic types, and I didn't…
  Recent forum comments:
RE: Updating A Portion Of A Site
Thanks! I remembered just before I came back to check. It's amazing the things you forget. Thanks…

RE: Updating A Portion Of A Site
Use a FrontPage include file - see Help on Included Content. Or, if all pages are asp or PHP, use a…

RE: Index Files
What they are telling you is the home page of your site (before you publish it) must be named index.htm…

RE: Website diifers from local copy
I see it as a 40x68 icon (clear your cache) - as for the position check w/ Google API help Neither issue…

RE: Forced submit
"Ellie" <ellymay999nospam@n­ospamnetzero.com> wrote in message…

RE: access denied
IUSR account doesn't have access to cmd.exe by default (assuming you are using anonymous…

RE: access denied
Hey guys, I'm totally out of my league here, I don't even know how I got stuck with this problem, it's…

RE: Forced submit
Ellie wrote on 05 sep 2008 in microsoft.public.in­etserver.asp.general­: You cannot…

RE: [patch@34995] vms.c - Memory…
On Wed, Dec 3, 2008 at 7:38 PM, John E. Malmberg <wb8tyw@qsl.net> wrote: Thanks, applied as…

RE: why infix:<div>:(Int, Int…
On Thu, Dec 4, 2008 at 3:26 PM, David Green <david.green@telus.­net> wrote: Using…
  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

Add 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:
Custom ClassLoader and method access…
Load-sharing/failover for RMI
Programatic access to Call Stack
пройди тесты:
see also:
hhhj
Here i Am
DeNemee
  Copyright © 2001—2008 QAIX
Idea: Miсhael Monashev
Помощь и задать вопросы можно в сообществе support.qaix.com.
Сообщения об ошибках оставляем в сообществе bugs.qaix.com.
Предложения и комментарии пишем в сообществе suggest.qaix.com.
Информация для родителей.
Write us at:
If you would like to report an abuse of our service, such as a spam message, please .