Re: BUG #8612: Truncate did not release disk space

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> On 11/20/2013 08:35 PM, eduardoa@mirthcorp.com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      8612
>> Logged by:          Eduardo Armendariz
>> Email address:      eduardoa@mirthcorp.com
>> PostgreSQL version: 9.0.13
>> Operating system:   CentOS
>> Description:
>>
>> Ran out of disk space and postgres shut down. Recovered enough disk space
>> for database to be operational. Truncated the largest table in the database,
>> the message table. This table had over 600gb of data. The result of the
>> truncate was that only about 200gb of the data was actually released to the
>> OS.

> sure that no other backend was/is actually still a file-handle
> referenced? That open filehandle will prevent the OS from showing up the
> freed space on the filesystem and can happen if you have backends still
> running that once referenced the table now truncated and have not done
> any work since you did the truncate (like a large connection pool or
> idle client connections, maybe an open psql session or something like that).

I think recent versions of PG contain logic that should ensure such open
handles will be released within a reasonable period of time (like one
checkpoint cycle).  9.0 I wouldn't bet on, though.  If nothing else, a
quick shutdown-and-restart of the database should release the space.

            regards, tom lane