Re: Why lots of temp schemas are being created

Walter Coole escribió:

> 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.

Temp schemas are not destroyed on session shutdown; they are rather
destroyed the next time the backend ID is reused. Normally that's not a
problem, because a backend ID is reused pretty soon. It's only a
problem when you use so high a backend ID due to high load, that a very
long time passes before it's reused. Those temp tables linger and can
cause Xid wraparound problems.

> 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.

IIRC the time when the previous temp schema is destroyed is when the
first temp table is created in the new backend.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.