Re: [HACKERS] Happy column dropping

On Mon, 24 Jan 2000, Peter Eisentraut wrote:

> I think what I did is significantly better than that, and of course it
> will be cleaned up by next week.

>From a discussion with Bruce over the past 24hrs, as well as was mentioned
by Hannu on this list ... we effectively do this with VACUUM, so "copying"
that logic should be, I imagine, relatively easy, *and* it preserves OIDs
*and* it doesn't require 2x the space...

I imagine that DROP COLUMN isn't used that often, so the time it takes to
do this isn't an issue ...

=================
> >
> > One thing I've never been able to figure out ... why isn't implementing
> > DROP COLUMN a simple matter of "lock table;remove field from pg_*;rebuild
> > table", similar to the way that we do when we vacuum?
>
> Because the column is still in the table, just invisible after removing
> from pg_attribute. You need to remove the column from the heap, and
> that requires creating a new version of the table. Vacuum moves tuples
> but does not make them shorter.

That I understand ... excuse my ignorance, but what would it take to
do that? The way I envision a table 'on disk':

col1col2col3col4col5|col1col2col3col4col5|col1col2col3col4col5|

Basically, you have X tuples per page, where a page is 8192bytes,
correct? If you were to remove, let's say, col2 out of the table, why
can't you do:

lock table
read page 1 into memory
rewrite page1 to disk as:
col1col3col4col5|col1col3col4col5|col1col3col4col5|
add one to page and goto 'read page n to memory'
unlock table

I'm making an assumption here ... first one being that each 'tuples' has
some sort of endoftuple marker in the table ...

If we're removing a column, the resultant 'page size' from the modified
page is going to be smaller then the original, so I would think it would
be a relatively simple thing, considering that its a read/re-write from
the same part of the 'on disk file' ...

... and it wouldn't require 2X the space used by the table ...
===================

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org