Hi All,
Running postgres 8.1.9 on SLES10 (kernel 2.6.16) currently, I noticed something
about the behaviour
of TRUNCATE on a large table.
The docs imply that TRUNCATE is the best way to delete everything in a table
(rather than DELETE) since there is no need to VACUUM afterward - the disk space
used is immediately returned to the operating system.
In the setup in question, there is one table in the cluster that acts as a
sequential
log. A long-lived process (24/7) connects to the cluster and writes rows to the
table
(existing rows are never altered.) The client does not use transactions, only
single INSERT commands.
Obviously this table can't be allowed to grow for ever, but it is important not
to
disrupt the client connection. One approach is to periodically
DELETE old entries and then do a VACUUM so that they can be re-used. This is
quite slow since
the table is large.
So I tried TRUNCATE on the table. It appeared to work - in that the row count
dropped to zero
and the connected client was not disrupted, and "du" on the postgres data
directory showed a fall.
But the available disk space (reported by "df") did not fall.
So I used "lsof | grep pgsql | grep deleted" to look for files that have been
deleted but are held open
and sure enough, there is the file for the table I just truncated. It is
referenced by a number of
postmaster processes(threads?) Most of which are associated with connections
that have *never queried* the
table in question, which is odd, but one process is associated with the
long-lived connection.
What causes the file handles of the truncated table to be released by all
postmaster processes?
I am concerned that some of these files will only get fully deleted once all
clients have disconnected
or the postgres server shuts down (neither of which is desirable.)
Vince
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
