Re: Stale temp tables

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> IIRC though, temporary tables only exist in the image of the postmaster that
> created them, so there is no way to find out which ones are supposed to
> exists. Maybe you need a new system table pg_temp_tables to track them...

> Is there some way you can use the schema code to do this?

What I'm intending in 7.3 is that temp tables will live in
special-purpose schemas, so it should be relatively easy to identify
them correctly.

I was toying with an approach like this:

* Identify the temp schemas by names along the lines of pg_temp_N, where
N ranges from 1 to max_connections. A particular backend gets to use
the temp schema associated with its BackendId (PROC array slot).

* For speed, do nothing with the temp schema until/unless a temp table
creation is requested in the current session. At that point, delete
anything we find in the temp schema (it must be from a crashed backend),
add the temp schema to the search path, and proceed with table creation.

As is, this scheme would not ensure prompt cleanout of dead temp tables;
particularly for the higher-numbered PROC slots, it might be a long time
before someone else wants to use temp tables in that slot. You could do
the cleanup manually if you had to reclaim space sooner. We could
theoretically offer some automated tool for this, but I'm not sure that
it's worth the trouble. I would definitely not support expecting the
postmaster to do it automatically at startup. (The postmaster isn't a
backend; it would have to launch 1 backend per database to perform this
function.)

regards, tom lane