Re: RFC: Restructuring pg_aggregate

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
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