Re: RFC: Restructuring pg_aggregate - Mailing list pgsql-hackers

> 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