Bug #740: Temp tables not deleted if postmaster crashes
Philip Warner (pjw@rhyme.com.au) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Temp tables not deleted if postmaster crashes
Long Description
If a postmaster crashes then any temporary tables associated with the backend will not be deleted, which is logical though sad. However, when the database is next started, it does not clean up the leftover temp tables.
'drop table pg_temp_NNNNN_N' seems to work, but it seems more reasonable to expect the startup process to cleanup dead tables.
Sample Code
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
Temp tables not deleted if postmaster crashes
This is dealt with in current sources, although not in the way you propose.
regards, tom lane
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?
Show quoted text
regards, tom lane
Philip Warner <pjw@rhyme.com.au> writes:
At 11:10 17/08/2002 -0400, Tom Lane wrote:
Temp tables not deleted if postmaster crashesThis 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