Re: Fast AT ADD COLUMN with DEFAULTs - Mailing list pgsql-hackers

On 2016-10-05 15:44:56 -0700, Jeff Janes wrote:
> On Wed, Oct 5, 2016 at 3:29 PM, Andres Freund <andres@anarazel.de> wrote:
> 
> > On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote:
> > > On 10/5/16, Andres Freund <andres@anarazel.de> wrote:
> > > > On 2016-10-05 11:58:33 -0700, Serge Rielau wrote:
> > > >> Dear Hackers,
> > > >> I’m working on a patch that expands PG’s ability to add columns to a
> > table
> > > >> without a table rewrite (i.e. at O(1) cost) from the
> > > >> nullable-without-default to a more general case.
> > > >
> > > > If I understand this proposal correctly, altering a column default will
> > > > still have trigger a rewrite unless there's previous default?
> > >
> > > No, "a second “exist default"" was mentioned, i.e. it is an additional
> > > column in a system table (pg_attribute) as default column values of
> > > the "pre-alter" era. It solves changing of the default expression of
> > > the same column later.
> >
> > Don't think that actually solves the issue. The default might be unset
> > for a while, for example. Essentially you'd need to be able to associate
> > arbitrary number of default values with an arbitrary set of rows.
> >
> >
> > ALTER TABLE foo ALTER COLUMN withdefault DROP DEFAULT;
> > INSERT id = 1;
> > ALTER TABLE foo ALTER COLUMN withdefault SET DEFAULT 1;
> > ALTER TABLE foo ALTER COLUMN withdefault DROP DEFAULT;
> > INSERT id = 2;
> > ALTER TABLE foo ALTER COLUMN withdefault SET DEFAULT 2;
> > ALTER TABLE foo ALTER COLUMN withdefault DROP DEFAULT;
> > INSERT id = 3;
> > ALTER TABLE foo ALTER COLUMN withdefault SET DEFAULT 3;
> >
> > The result here would be that there's three rows with a default value
> > for foo that's the same as their id. None of them has that column
> > present in the row.
> >
> 
> My understanding is that all of those would be materialized.

But that'd require a table rewrite, as none of the above INSERTs were
done when a default was in place. But each has a different "applicable"
default value.


> The only
> default that isn't materialized is the one in effect in the same statement
> in which that column was added.  Since a column can only be added once, the
> default in effect at the time the column was added can never change, no
> matter what you do to the default later on.

DROP DEFAULT pretty much does that, because it allows multiple (set of)
rows with no value (or a NULL) for a specific column, but with differing
applicable default values.

Andres