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

Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
> On 11/20/2013 08:35 PM, eduardoa(at)mirthcorp(dot)com wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 8612
>> Logged by: Eduardo Armendariz
>> Email address: eduardoa(at)mirthcorp(dot)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