Re: Bug #740: Temp tables not deleted if postmaster

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 11:10 17/08/2002 -0400, Tom Lane wrote:
> Temp tables not deleted if postmaster crashes
>>
>> This is dealt with in current sources, although not in the way you propose.

> Great - what does it do?

Temp-table namespaces are flushed when a backend first attempts to use
one, if it finds any already-existing tables therein. So a temp table
could survive for awhile after a system crash, but it will be deleted
before it could get in anyone's way. Also, the odds are good that it
*will* get deleted eventually, while with the old arrangement it might
hang around forever. (There are at most max_connections distinct
temp-table namespaces in an installation.)

If you're annoyed about the disk space a temp table uses, then a
superuser could remove it with a manual DROP TABLE operation in
advance of any backend happening to assign the temp namespace for use.
This is a little easier than in prior releases because the temp table's
name is not converted into pg_temp_nnn ... you may have to guess about
which temp namespace it's in, but psql can help you out with a schema
wildcard:

regression=# create temp table mytemp(f1 int);
CREATE TABLE
regression=# \d pg_temp_*.mytemp
Table "pg_temp_1.mytemp"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |

regression=# -- could do "drop table pg_temp_1.mytemp" here

regards, tom lane