Re: Stale temp tables

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: Peter Darley <pdarley(at)kinesis-cem(dot)com>, Pgsql-General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Stale temp tables
Date: 2002-03-11 22:57:28
Message-ID: 200203112257.g2BMvSZ25593@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Martijn van Oosterhout wrote:
> On Mon, Mar 11, 2002 at 03:49:22PM -0500, Bruce Momjian wrote:
> > Peter Darley wrote:
> > > Friends,
> > > I've got a whole bunch (8,000 or so) of old stale temp tables that were the
> > > result of a hung backend. I can see them using pgAdmin II, but not through
> > > psql. Is there any way to dump these guys, should I dump them, are they
> > > likely to be causing problems? What should I do about this?
> >
> > Wow, that is a good question. You can stop the postmaster and start the
> > 'postgres' binary with the -O option and drop them. -O is required
> > because they are system tables.
>
> 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.

It is at:

ftp://candle.pha.pa.us/pub/postgresql/mypatches/clean.patch

I am looking for other ideas to get this functionality into the
database.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthew Hixson 2002-03-11 23:03:13 Postgres on Apple hardware?
Previous Message Martijn van Oosterhout 2002-03-11 22:53:10 Re: Stale temp tables