I was looking at how hard it would be to support altering column types and it
seems to me that it would be trivial to support changing nullability,
increasing the maximum length of the VARCHAR data type and increasing the
precision or scale of the DECIMAL / NUMERIC data type.
Oracle allows you to update a column to null and then modify its data type to
any other type. This is easy because it stores all columns in a variable
length format and a null looks the same regardless of type.
I understand that with the current heap tuple format described in
backend/access/common.c that changing the type of any fixed length attribute
requires updating every row.
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.
We could presumably do the following to change a column data type:
Preconditions: 1. Type conversion is possible from old type to new type and either a) old type is unconditionally
convertible(e.g. length/precision increasing) b) read locked scan of table reveals that all values are
convertible2. Exclusive write lock on table
if(new and old types are variable length and are binary compatible){ 1. Change type in catalog 2. Done}
else{ 1. Visit all current tuples and rewrite in place, converting attribute value
to new type, and shifting all other attributes and null bitmask appropriately 2. Change type in catalog 3. Done}
Does this sound reasonable? Also, is anyone working on ALTER TABLE DROP
COLUMN right now?
Speaking of which, couldn't we make it so that UPDATES and DELETES running
under an exclusive table lock do an inline vacuum?
- Mark Butler
