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

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?

(BTW, I'm quite sure schemes like this have been discussed before.
Check the archives...)
        regards, tom lane