Re: Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?

On Thu, May 21, 2009 at 10:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sam Mason <sam@samason.me.uk> writes:
>> On Thu, May 21, 2009 at 12:06:29PM +0400, Dmitry Koterov wrote:
>>> ALTER TABLE ... ADD COLUMN ... NULL;
>>>
>>> (nullable without a default value). This is because of NULL bitmap in
>>> tuples. And it's greatest feature for a developer!
>
>> I don't think this is because of the "NULL bitmap".
>
> No, it isn't.  It's because each tuple includes the actual count of
> fields it contains (t_natts or HeapTupleHeaderGetNatts), and the value
> extraction routines are coded to assume that references to fields
> beyond that number should yield NULL.  So the ALTER can just leave
> the existing rows alone --- only when you update a row will it change
> to include the newly added field(s).
>
> AFAICS there's no good way to scale that solution up to handling
> non-null values.
>
>> All that needs to be tracked is the "first" default value (this is
>> currently assumed to be NULL).
>
> You're being a bit vague, but in any case I don't think it can work
> for non-constant defaults (consider DEFAULT NOW()).  And what about
> ALTER COLUMN DEFAULT?

I think what Sam is proposing is that, in addition to storing the
default value for a column, you could store the value at the time the
column was added.  These might be the same if the default is a
constant and has never been modified, or they might be different.
This would even work for now() because it's stable, but it couldn't be
used for a volatile function like random() or timeofday(), of course.

...Robert