Re: Vacuuming leaked temp tables (once again)

On Fri, 2008-06-27 at 12:43 -0400, Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> >> The only solution proposed in that thread was to auto-delete temp
> >> tables at postmaster restart; which I opposed on the grounds that
> >> throwing away data right after a crash was a terrible idea from a
> >> forensic standpoint.
>
> > Why not just rename the files out of the way, and nuke the entries from
> > the catalog?
>
> It's usually tough to make any sense of the contents of a table if you
> don't have the catalog entries. Anyway, that approach would put the
> onus on the admin to clean things up eventually, which isn't all that
> appealing.

We need to learn to live without the catalog entries anyway.

In Hot Standby mode, creating a temp table would cause a write to the
catalog, which would then fail. If we want to allow creating temp tables
in Hot Standby mode then we must prevent them from inserting and then
later deleting them from the catalog.

So it would seem that we need a way of handling temp tables that doesn't
rely on catalog entries at all.

My proposal would be that we identify all temp table names by the xid
that first creates them (and their name...). In memory we would keep
track of which files go with which tablenames, so we can still locate
them easily. If we lose that context we can always see which files are
in need of vacuum using that xid.

Are we also at risk from very long lived sessions that use temp tables?
Do we need to put in a check for sessions to vacuum old temp tables that
are still valid?

I'm happy to work on this if a solution to all of the above emerges.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support