Обсуждение: 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
thoughsad. 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?


>                         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 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