From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | "thimoty(at)thimoty(dot)it" <thimoty(at)thimoty(dot)it> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: stale temporary tables |
Date: | 2002-10-04 17:55:28 |
Message-ID: | 200210041755.g94HtSv27833@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
thimoty(at)thimoty(dot)it wrote:
> Hello all,
> i don't know if there is a quicker way, but i ended up in having
> 40,000 or so
> stale temp tables from a Java application which is using postgres as
> backend.
>
> what i did is the following:
> 1) run postmaster with -o -O
> 2) type \dS and put the result to a file (list of all the temp tables,
> all still there!)
> 3) create a sql file to drop the tables like this
> cut -f1 -d\| staletables.txt | grep pg_temp | sed '/pg_temp.[0-9]*.[0-9]
> */s//DROP TABLE "&";/' > droptables.sql
> 4) from pgsql run \i droptables.sql
>
> isn't there any switch or command in VACUUM that does it?
Lost temp tables should never happen, but they sometimes do because of
backend crashes. I have worked on a patch to auto-delete them, but it
was rejected and we can't come up with a good way to do it. I think
this is fixed in 7.3.
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-10-04 18:00:52 | Re: Error after updating postgresql |
Previous Message | Martin D. Weinberg | 2002-10-04 17:46:18 | multi-column btree index for real values |