stale temporary tables

thimoty@thimoty.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

thimoty@thimoty.it wrote:

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.

Lost temp tables should never happen, but they sometimes do because of
backend crashes.

I was wondering how he got to that state, too. If it was because of
crashes, he must have had an awful lot of 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.

7.3 is better anyway: a backend crash can still leave temp tables
behind, but they'll be cleaned up the next time some backend tries
to use the same pg_temp_NNN schema.

regards, tom lane