Re: effective SELECT from child tables

On P, 2005-10-02 at 15:30 +0200, Martijn van Oosterhout wrote:
> On Sun, Oct 02, 2005 at 03:57:37PM +0300, Hannu Krosing wrote:
> > On P, 2005-10-02 at 01:24 -0400, Greg Stark wrote:
> > > Of course that only works if the reason they want to set fill the rows with
> > > the default value isn't precisely because NULL is a perfectly reasonable thing
> > > for the column to have (but not what they want for the existing rows).
> >
> > Sure.
> >
> > What would be needed for adding new colums with default filling would be
> > some end-of-tuple marker or stored column count or tuple version nr, and
> > then a rule (or just default behaviour) of showing default value for
> > *missing* columns (colno > nr of stored columns).
>
> Actually, PostgreSQL does know the number of columns in a tuple. It
> would be possible get change heap_getattr to return the default value.

Good. (I have not looked at tuple layout for a few years :)

> However, from a semantic point of view, it would be a bit strange. If
> you added a column, updated some rows then set a default, that default
> might end up applying to every row, except the ones you already
> modified. With careful coding you may be able to get around this.

If I do the add column and set default in the same transaction, this
should not be a problem.

> However, a good argument can be made that setting the DEFAULT for a
> column shouldn't change data anywhere. What about if I want to change
> the default for new values but not for old ones. That wouldn't work if
> the database starts adding values randomly, depending on when they are
> read...

Right. Actually the "default" value returned for missing columns should
be different from the default for new values and should be settable only
once, when adding the column, else issues would become really really
weird.

--
Hannu Krosing <hannu(at)skype(dot)net>