Re: Truncating/vacuuming relations on full tablespaces

On Fri, Jan 15, 2016 at 11:05 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 15 January 2016 at 15:21, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> 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.
>
> 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.

Well, I think that trying to extend a file on a full tablespace during
truncate would be a problem. However, I can't see any evidence that
we do that, except with RESTART IDENTITY, where it's unavoidable
because you need to recreate the sequence. On the other hand,
extending a file on a full tablespace during VACUUM does not seem to
me to be a bug. It is true that it is remotely possible that you
could have a table with empty space at the end which VACUUM would
truncate but for inability to create the FSM or VM, and that would
suck. On the other hand, suppose you have a table which just happens
to fill the tablespace until it's almost but (you think) not quite
full. Then you VACUUM the table. If it just silently failed to build
the visibility map and then all your subsequent vacuums were really
slow but without any user-visible notice that there's a problem, that
would be awful. So all in all I think the system seems to be behaving
as we would wish, unless there's some other test case that shows us
creating the VM or FSM when it's needless to do so.

Now, I do think it's a somewhat fair complaint that if you have a
tablespace which is totally, 100% full, recovery is difficult. You
can probably DROP the table, but TRUNCATE might fail, and so might
VACUUM. You could argue that DROP is usually a good substitute for
TRUNCATE, although there could be dependencies, but DROP is certainly
not a good substitute for VACUUM. We could address the VACUUM case by
having an optional argument to VACUUM which tells it to skip VM and
FSM maintenance, presumably to be used only in case of emergency. I'm
not sure if it's worth having for what is presumably a pretty rare
case, but it seems like it could be done.

We could try to address the TRUNCATE case by adding a flag to
optionally perform a non-transactional TRUNCATE, like we did prior to
7.4, but I wonder how that's ever really safe. Suppose PostgreSQL
tries to truncate either the table or one of its indexes but then,
when trying to truncate the other, we get an error from the operating
system. We cannot recover by aborting the transaction, nor can we
complete the operation by going forward. That mighta been the sort of
thing we didn't worry about much in the early 7 series, but I don't
think it has much chance of passing muster today.

Anybody else want to weigh in with thoughts on any of this?

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