Re: RFC: Restructuring pg_aggregate

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