Search Postgresql Archives

Problems with leftover types in pg_temp schemas

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

last week i asked a question about how to remove a left over pg_type from a 
temp table.

http://archives.postgresql.org/pgsql-general/2005-09/msg00409.php

Tom Lane helped me managing it by reindexing pg_depends and DROPping the 
pg_temp_X.temp_gc.

Now i have the same problem again but with another function which creates and 
drops a temporary table (not temp_gc this time, but "spiele")

If the server is overloaded and lots of requests are made to this function it 
seems that cleaning up pg_type isn't working. 

It happend on two different database servers today. On one of them it happens 
yesterday too. So it can't be a hardware failure. If i do 
DROP pg_temp_N.spiele serveral times (i found the type about 10 times), 
everything works fine. 

What happens in my function is the following: I have a table "spiele" (games) 
where i save scores. Users can use a html form which offers to input scores 
and temporarly overwrite the table "spiele" to do some calculation like "what 
would be the result if the scores were like my input".

For this reason i take the input and build a temporary table "spiele" which 
overrides the main table because both schemas public and pg_temp are in the 
search_path. 

the last two days, when many people used this function, suddenly i got lots of 
pg_type "spiele" which are not deleted after dropping the temporary table.

Is the amount of temporary schemas bound to a specific number? Or can i create 
as many temporary schemas as sessions. Why can there be some types left after 
dropping a temporary table. To me it seems like an odd bug which occurs only 
when using a lot of temporary tables in parallel sessions.

the problem is quite urgent because i can't afford the time for rewriting the 
app to not use temp tables. So i need to fix it. any help is very 
appreciated.

kind regards,
janning

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux