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

Some of the data was still in the base directory which corresponded to the table that was truncated.

Unfortunately, this was somewhat of a time sensitive issue so I did end up dropping the whole database and recreating it. That did clear the disk space which was originally left behind from the truncate. I don't really have the means to reproduce this. The only application that had connections open with the database was down at the time of the truncate. The only thing out of the ordinary was that there was very little disk space left when I attempted to truncate.

Thanks,
Eduardo Armendariz

On Nov 22, 2013, at 12:04 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Nov 22, 2013 at 11:12 AM, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> wrote:
> 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).
>
> If that were the case, I don't think pg_database_size would still be counting the size, as it would no longer be able to find it the files in that directory.
>
> I think the next step would be to get an 'ls -l' listing of the $PGDATA/base/<dboid> directory.
>
> Cheers,
>
> Jeff

--
CONFIDENTIALITY NOTICE: The information contained in this electronic
transmission may be confidential. If you are not an intended recipient, be
aware that any disclosure, copying, distribution or use of the information
contained in this transmission is prohibited and may be unlawful. If you
have received this transmission in error, please notify us by email reply
and then erase it from your computer system.