Thread added to TODO.detail/drop.
--------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > > Actually, what we need to do to reclaim space is to enable table > > recreation without the column, now that we have relfilenode for file > > renaming. It isn't hard to do, but no one has focused on it. I want to > > focus on it, but have not had the time, obviously, and would be very > > excited to assist someone else. > > > > Hiroshi's fine idea of marking certain columns as unused would not have > > reclaimed the missing space, just as my idea of physical/logical column > > distinction would not reclaim the space either. Again, my > > physical/logical idea is more for fixing other problems and > > optimization, not DROP COLUMN. > > Hmmm. Personally, I think that a DROP COLUMN that cannot reclaim space is > kinda useless - you may as well just use a view!!! > > So how would this occur?: > > 1. Lock target table for writing (allow reads) > 2. Begin a table scan on target table, writing > a new file with a particular filenode > 3. Delete the attribute row from pg_attribute > 4. Point the table in the catalog to the new filenode > 5. Release locks > 6. Commit transaction > 7. Delete orhpan filenode > > i. Upon postmaster startup, remove any orphaned filenodes > > The real problem here is the fact that there are now missing attnos in > pg_attribute. Either that's handled or we renumber the attnos - which is > also quite hard? > > This, of course, suffers from the double size data problem - but I believe > that it does not matter - we just need to document it. > > Interestingly enough, Oracle support > > ALTER TABLE foo SET UNUSED col; > > Which invalidates the attribute entry, and: > > ALTER TABLE foo DROP col CHECKPOINT 1000; > > Which actually reclaims the space. The optional CHECKPOINT [n] clause > tells Oracle to do a checkpoint every [n] rows. > > "Checkpointing cuts down the amount of undo logs accumulated during the > drop column operation to avoid running out of rollback segment space. > However, if this statement is interrupted after a checkpoint has been > applied, the table remains in an unusable state. While the table is > unusable, the only operations allowed on it are DROP TABLE, TRUNCATE > TABLE, and ALTER TABLE DROP COLUMNS CONTINUE (described below). " > > Chris > > > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org