Hi All,
I've been thinking about this DROP COLUMN business (sorry to start another
spammy, flamey thread!). I'm taking ideas from lots of sources here.
How does this sound for a process?
1.
A new column is added to pg_attribute called 'attisdropped'. It, of course,
defaults to false.
2.
The column expansion (*) code and the code that checks for valid column
references everywhere in the codebase is changed to also check the
attisdropped field. Does someone have a comprehensive list of places to be
changed?
3.
The DROP COLUMN command does nothing but set the attisdropped of a column to
true, and rename the column to something like DELETED_old_col_name. The
column renaming will help people using non-attisdropped aware admin programs
see what's what, plus it will allow people to create a new column with the
same name as the column just dropped.
Now the dropped column will be invisible. As you update rows, etc. the
space will be reclaimed in the table as NULLs are put in where the old value
used to be. Is this correct?
4.
A new command, something like "ALTER TABLE tab RECLAIM;" will be able to be
run on tables. It will basically go through the entire table and rewrite
every row as is, NULLifying all dropped columns in the table. This gives
the DBA the option of recovering his/her space if they want.
Notes
-----
a. What happens with TOASTed columns that are dropped?
b. Would it be worth implementing an 'UNDROP' command...?
c. Do we need an 'attisreclaimed' field in pg_attribute to indicate that a
field as been fully reclaimed, or do we just let people run it whenever they
want (even if it has no effect other than to waste time)?
d. Are there any other comments?
Basically, I would like to come up with a 'white paper' implementation that
we can all agree on. Then, I will try to code some parts myself, and
solicit help from others for other parts. Hopefully, together we can get a
DROP COLUMN implementation. The most important step, however, is to agree
on an implementation spec.
Hopefully I can get the www person to set up a project page (like the
proposed win32 project page) to coordinate things.
Comments?
Regards,
Chris