Re: ALTER TABLE MODIFY COLUMN

Tom Lane wrote:
>
> Mark Butler <butlerm(at)middle(dot)net> writes:
> > Surely if we have an write exclusive table lock we can rewrite tuples
> > in place rather than creating new versions with its corresponding 2x
> > space requirement.
>
> Nyet. Consider transaction rollback.

Well, the first thing to consider would be to make this type of DDL operation
un-abortable. If the database goes down while the table modification is in
progress, the recovery process could continue the operation to completion
before releasing the table for general access.

The problem with the standard alternatives is that they waste space and are
slow:

Alt 1. create new version of tuples in new format like DROP COLUMN proposal
Alt 2. rename column; add new column; copy column; drop (hide) old column
Alt 3. rename indices; rename table; copy table; recreate indices;

Now this probably only makes a difference in a data warehouse environment,
where the speed
of mass load / update operations is much more important than being able to
roll them back.

I suppose there are two really radical alternatives as well:

Radical Alt 1: Use format versioning to allow multiple row formats to
co-exist,
lazy update to latest format

Radical Alt 2: Delta compress different versions of the same row on the same
page

I can see that the conventional alternatives make sense for now, however.

- Mark Butler