On Sat, 2002-04-13 at 17:29, Tom Lane wrote:
> [ way past time to change the title of this thread ]
>
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > OK, sounds fair. However, is there a more aggressive way of reclaiming the
> > space? The problem with updating all the rows to null for that column is
> > that the on-disk size is doubled anyway, right? So, could a VACUUM FULL
> > process do the nulling for us? Vacuum works outside of normal transaction
> > constraints anyway...?
>
> No, VACUUM has the same transactional constraints as everyone else
> (unless you'd like a crash during VACUUM to trash your table...)
But can't it do the SET TO NULL thing if it knows that the transaction
that dropped the column has committed.
This could probably even be done in the light version of vacuum with a
special flag (VACUUM RECLAIM).
Of course running this this makes sense only if the dropped column had
some significant amount of data .
> I do not think that we necessarily need to provide a special mechanism
> for this at all. The docs for DROP COLUMN could simply explain that
> the DROP itself doesn't reclaim the space, but that the space will be
> reclaimed over time as extant rows are updated or deleted. If you want
> to hurry the process along you could do
> UPDATE table SET othercol = othercol
> VACUUM FULL
If only we could do it in namageable chunks:
FOR i IN 0 TO (size(table)/chunk) DO UPDATE table SET othercol = othercol OFFSET i*chunk LIMIT chunk VACUUM FULL;
END FOR;
or even better - "VACUUM FULL OFFSET i*chunk LIMIT chunk" and then make
chunk == 1 :)
--------------
Hannu