Re: Tuple data - Mailing list pgsql-hackers

Considering how often you actually change the structure of a database, I
don't mind waiting for such a reorganisation to take place, however it would
still be nice if it could be done in O(1) time because it would minimise the
amount of downtime required for structure changes.

What are the cases where the current implementation does not handle it
properly?

Restructuring all the tables (inherited too) would require either 2x the
space or lots of hackery to take care of situations where there isn't enough
room for a larger null bitmap. This hackery seems more complicated than just
having alter look for inherited tables and add the column to those as well.

You could define a flag or something so a deleted column could be so flagged
and
ALTER TABLE DELETE COLUMN
would run just as fast. Vacuum could then take care of cleaning out these
columns. If you wanted to make it really exciting, how about searching for a
deleted column for the ADD column. Touch all the tuples by zeroing that
column and finally update pg_attribute. Nothing would be more fun than 2 way
fragmentation :)

-Michael

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>
Cc: "Hannu Krosing" <hannu@tm.ee>; "Michael Richards"
<miker@interchange.ca>; <pgsql-hackers@postgresql.org>
Sent: Sunday, December 17, 2000 8:05 PM
Subject: Re: [HACKERS] Tuple data


> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> >> Tom Lane wrote:
> >>>>> ALTER ADD COLUMN doesn't touch any tuples, and you're right that
it's
> >>>> critically dependent on heap_getattr returning NULL when an attribute
> >>>> beyond the number of attributes actually present in a tuple is
accessed.
> >>>> That's a fragile and unclean implementation IMHO --- see past traffic
> >>>> on this list.
>
> > I don't remember the traffic either.
> > IIRC,I objected to Tom at this point in pgsql-bugs recently.
>
> That was the traffic I was recalling ;-)
>
> > I think it's very important for dbms that ALTER ADD COLUMN
> > touches tuples as less as possible.
>
> I disagree.  The existing ADD COLUMN implementation only works for
> appending columns at the end of tuples; it can't handle inserting
> a column.  To make it usable for inherited tables requires truly
> horrendous kluges (as you well know).  IMHO we'd be far better off
> to rewrite ADD COLUMN so that it does go through and change all the
> tuples, and then we could get rid of the hackery that tries --- not
> very successfully --- to deal with inconsistent column orders between
> parent and child tables.
>
> I have a similar opinion about DROP COLUMN ...
>
> regards, tom lane