Re: DROP COLUMN (was RFC: Restructuring pg_aggregate)

[ way past time to change the title of this thread ]

"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)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...)

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
to force all the rows to be updated and then reclaim space. But given
the peak-space-is-twice-as-much behavior, this is not obviously a win.
I'd sure object to an implementation that *forced* that approach on me,
whether during DROP itself or the next VACUUM.

> Also, it seems to me that at some point we are forced to break client
> compatibility. Either we add attisdropped field to pg_attribute, or we use
> Hiroshi's (-1 * attnum - offset) idea. Both Tom and Hiroshi have good
> reasons for each of these - would it be possible for you guys to post with
> your reasons for and against both the techniques.

Er, didn't we do that already?

regards, tom lane