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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026