Re: Truncating/vacuuming relations on full tablespaces

On Fri, Sep 4, 2015 at 8:04 AM, Thom Brown <thom(at)linux(dot)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. In the
first example, the error is happening during VACUUM, not TRUNCATE, and
it's unclear what else we could do besides error out. I mean, we
could make it so that VACUUM fails softly rather than emitting a hard
error if unable to grow the visibility map, but that sounds like an
anti-feature. In the second case, the error is happening during
TRUNCATE, but it's happening on the main fork of the sequence
relation, not any auxiliary fork. So you've got two examples of
things failing here but neither one matches the problem statement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company