Re: Stale temp tables

On Mon, Mar 11, 2002 at 05:57:28PM -0500, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> > Isn't this something VACUUM should be checking for? Assuming that it could
> > tell if a temporary table was still in use by another backend, it would be
> > possible for the system to go through the data directory and delete any
> > temporary tables not in use.
>
> Agreed. Should be very possible. I actually wrote such a function to
> be added to 7.1 but Tom didn't like it because he was concerned about
> the use of oid's to determine if any other backend was using the temp
> table.

Using OIDs does seem a bit flimsy. It depends entirely on how temp tables
are handled. If, for example, they were stored in normal pg_class and
the rows are automatically deleted when the backend terminates, then a
simple scan of pg_class will tell you which files are in use. If not deleted
you could mark them with a backend identifier so you know when the backend
has died.

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?
--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?