Re: Toasted table not deleted when no out of line columns left

On Sep 22, 2008, at 9:46 AM, Simon Riggs wrote:

>
> On Sun, 2008-09-21 at 12:05 -0400, Tom Lane wrote:
>
>> ... and it goes on to point out how to force immediate space
>> reclamation
>> if you need that. These statements apply independently of whether
>> any
>> particular value is toasted or not.
>>
>> The reason for this choice is that reclaiming the space immediately
>> would turn DROP COLUMN from a quick operation into a slow one, as it
>> would have to grovel over every row of the table looking for TOAST
>> pointers.
>>
>>> Judging from that, the toasted table
>>> cleanup may be part of ALTER TABLE DROP COLUMN.
>
> I thought Hans meant cleanup, not drop?
>
> Perhaps there is room for a function that scans a toast table to
> remove
> unreferenced toast data? It could be done much more efficiently than
> the
> UPDATE and VACUUM FULL technique. No need to add it into DROP COLUMN,
> but that doesn't mean it shouldn't be available somewhere, somehow.
>
> Hans is likely to write this anyway for his customer, so it seems
> worth
> defining how it should look so we can accept it into core. VACUUM
> TOAST
> perhaps?

hello simon,

we definitely have to do something about this problem. VACUUM FULL is
not an option at all.
once the last text column is gone (toastable column) we definitely
have to reclaim space.
we just cannot afford to lose hundreds of gigs of good storage because
of this missing feature.

so, to comment tom's answer - it is not about not understanding "no";
it was more a request to get a "how to do it best" because we have to
do it somehow.

best regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: www.postgresql-support.de