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