Re: Why lots of temp schemas are being created

Thanks for the pointer!

In case anyone else has the same problem, here's what I did:

I used

SELECT MAX(backendid) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

to get the highest backend ID that is running. I deleted all the pg*_temp_ schemas numbers higher than that.

This didn't seem quite thorough enough, as I found that when a process would end (MAX(backendid) went down), the corresponding pg*_temp_ schema would not go away. I think these were schemas created by a previous backend, so would not be cleaned up by a backend that hadn't created it.

I restarted the database; forcing it to have just one backend. Then I repeated the above procedure. I'm fairly sure that pg_toast_temp_1 and pg_temp_1 are not actually in use, but I decided to quit while I'm ahead.

I guess these schemas are fairly harmless, but it seems kind of messy to have them sloshing around. It seems like when a new backend starts up, it would be better to clear out the temp schemas to avoid accidentally using stale data, but this doesn't seem to be happening. One could also imagine hooking a cleanup in the database startup, but I don't see that either.

Walter

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
Sent: Wednesday, February 03, 2010 3:36 PM
To: Walter Coole
Cc: Merlin Moncure; Grzegorz Jaśkiewicz; Anirban Pal; pgsql-novice(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Why lots of temp schemas are being created

Walter Coole escribió:

> I would like to drop them, since there are so many of them, they make
> it tedious to look through my databases in pgAdmin. Is there a
> reliable way to distinguish between temp schemas that exist because
> they are supposed to be there and those that are not?

Run pg_get_backend_idset() (or something like that, maybe there's "stat"
in the name), which returns a list of backend IDs that are running.
Then see which temp schemas have numbers beyond what's listed there;
those shouldn't be there and could cause problems if the numbers are too
high.

> Or even better, a way to tell the database to clean them up itself?

It does, unless one of them gets a very high backend ID that's not
reused.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support