Re: ALTER TABLE DROP COLUMN

On Mon, 9 Oct 2000, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> Basically, move the first 100 rows to the end of the table file, then take
> >> 100 and write it to position 0, 101 to position 1, etc ... that way, at
> >> max, you are using ( tuple * 100 ) bytes of disk space, vs 2x the table
> >> size ... either method is going to lock the file for a period of time, but
> >> one is much more friendly as far as disk space is concerned *plus*, if RAM
> >> is available for this, it might even be something that the backend could
> >> use up to -S blocks of RAM to do it off disk? If I set -S to 64meg, and
> >> the table is 24Meg in size, it could do it all in memory?
>
> > Yes, I liked that too.
>
> What happens if you crash partway through?

what happens if you crash partway through a vacuum?

> I don't think it's possible to build a crash-robust rewriting ALTER
> process that doesn't use 2X disk space: you must have all the old
> tuples AND all the new tuples down on disk simultaneously just before
> you commit. The only way around 2X disk space is to adopt some
> logical renumbering approach to the columns, so that you can pretend
> the dropped column isn't there anymore when it really still is.

how about a combination of the two? basically, we're gonna want a vacuum
of the table after the alter to clean out those extra columns that we've
marked as 'dead' ... basically, anything that avoids tht whole 2x disk
space option is cool ...