Thread: stale temporary tables

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?

Regards
 Tim

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