Re: Truncating/vacuuming relations on full tablespaces

On 15 January 2016 at 15:21, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Sep 4, 2015 at 8:04 AM, Thom Brown <thom@linux.com> wrote:
>> Currently, when attempting to vacuum a table on a tablespace with no space
>> left, we get an error:
>>
>> postgres=# vacuum test;
>> ERROR:  could not extend file
>> "pg_tblspc/19605/PG_9.6_201508111/12382/19616_vm": No space left on device
>> HINT:  Check free disk space.
>>
>> This is because it first tries to grow the visibility map file.
>>
>> We also get a similar problem when attempting to truncate with restart
>> identity:
>>
>> postgres=# truncate table test restart identity;
>> ERROR:  canceling autovacuum task
>> CONTEXT:  automatic vacuum of table "postgres.public.test"
>> ERROR:  could not extend file "base/12382/16391": No space left on device
>> HINT:  Check free disk space.
>> STATEMENT:  truncate table test restart identity;
>>
>> I guess a workaround for the 2nd case is to truncate without restarting the
>> identify, then truncate again with restart identify, or just resetting the
>> sequence.  In any case, someone will likely be running this command to free
>> up space, and they can't due to lack of space.
>>
>> But shouldn't we not be creating FSM or VM files when truncating?
>
> That seems like it might possibly be a good thing to avoid, but we're
> not doing it in either of those examples.  So, I am confused.

So am I, reading it back I'm not sure why I said that now.

The problem is with attempting to extend some file on a full
tablespace during a vacuum or a truncate.  I guess they are different
but related problems.

Thom