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!!!
Yep, kind of a problem. It is a tradeoff between double diskspace/speed and removing column from disk. I guess that's why Oracle has both. > > 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 Yep, something like that. CLUSTER is a good start. DROP COLUMN just deals with the attno too. You would have to renumber them to fill the gap. > i. Upon postmaster startup, remove any orphaned filenodes Actually, we don't have a good solution for finding orphaned filenodes right now. I do have some code that tries to do this as part of VACUUM but it was not 100% perfect, so it was rejected. I am willing to open the discussion to see if a perfect solution can be found. -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])