Re: Vacuum full of parent without partitions possible?

On Tue, May 3, 2016 at 3:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jan Keirse <jan(dot)keirse(at)tvh(dot)com> writes:
> > I have a table that used to contain all data.
> > because it grew too big I added a partition trigger a long time ago and
> > since than all new data was added to small partitions. By now all data in
> > the original parent table has become obsolete and was deleted, however
> the
> > disk space cannot be reclaimed without a vacuum full. The problem is, a
> > vacuum full of only the parent table should be instantaneous since it
> > contains no rows, but because the vacuum full triggers a vacuum of all
> > partitions too,
>
> No, a VACUUM on a single table processes only that table.
>
> I'm inclined to think your actual problem is that VACUUM FULL wants
> an exclusive lock and can't get one because of other traffic on the
> table. Plain VACUUM doesn't need an exclusive lock ... unless it's
> trying to truncate the relation, which in this case it presumably would
> be. Maybe your conclusion that you needed a VACUUM FULL was based
> on observing that VACUUM didn't reduce disk consumption; but if the
> table is empty, that would only be because it couldn't get exclusive
> lock.
>
> I'd suggest waiting for a low-traffic time of day and then doing a
> plain VACUUM. Or alternatively, if you're sure the table is empty
> and will stay that way, you could just cut to the chase and TRUNCATE
> it. But none of these alternatives are going to reclaim any disk
> space without taking an exclusive lock on the table, because they
> simply cannot truncate the file while other queries are scanning it.
>

​OK, thanks for clearing that up. It is indeed impossible to ever take an
exclusive lock on the table during normal operations (there are continuous
selects and ​

inserts into the table which are redirected to the partitions by a before
insert trigger, the data is all machine output and the load is constant
24x7.​)
I'll leave the table as is for the time being and do a vacuum the next time
there is a need for scheduled down time.

--

**** DISCLAIMER ****

http://www.tvh.com/glob/en/email-disclaimer

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."