Re: Fast AT ADD COLUMN with DEFAULTs

On Wed, Oct 5, 2016 at 11:44 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Oct 5, 2016 at 3:29 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
>> On 2016-10-05 15:23:05 -0700, Vitaly Burovoy wrote:
>> > On 10/5/16, Andres Freund <andres(at)anarazel(dot)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. 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.
>
> Cheers,
>
> Jeff
>

I understood the same thing. That when the column is added the "DEFAULT
constant" means 2 things:

-a- existing rows get a value of that constant (that is not actually
written in the rows, but kept (hidden from the user) in the system tables
and only written in the rows that are updated, vacuumed, etc) and
-b- new rows, inserted after the ADD COLUMN will get the DEFAULT constant,
same way as a normal column definition would.

The b part can easily be changed later with an ALTER COLUMN that sets a new
DEFAULT.
The a part is never changed - but possibly deleted from the system table
when all rows existing before the ADD COLUMN have been updated.

Pantelis