ALTER TABLE DROP COLUMN

Marc called me today to discuss ALTER TABLE DROP COLUMN options.

Our new idea is to do the ALTER TABLE DROP COLUMN in place in the
existing table, rather than make a new one and try and preserve all the
table attributes.

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a heap_insert(), then a
heap_delete() on the current tuple, making sure to skip over the tuples
inserted by the current transaction. When completed, remove the column
from pg_attribute, mark the transaction as committed (if desired), and
run vacuum over the table to remove the deleted rows.

Seems this would be a very clean implementation for 7.1. It also would
be roll-backable in cases where the operation failed half-way during the
process.

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026