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

From: Greg Stark <stark(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Sam Mason <sam(at)samason(dot)me(dot)uk>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?
Date: 2009-05-21 15:13:18
Message-ID: 4136ffa0905210813m4e7b1a21k50ab6711875e4782@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, May 21, 2009 at 3:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> 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...)

Schemes like this have been discussed before but I don't think we
considered applying the limitation that only the "first" default value
would be covered. We always wanted to be able to handle new defaults
or making a non-null column nullable later. I think the main
motivation in the past was space savings for default values rather
than making adding new non-null columns cheap.

AFAICS as long as we only want to handle newly created non-nullable
columns with initial defaults that are applied when the column is
added then we could actually handle it by storing the initial default
value in the catalog and keeping it in the tupledesc at all times.

If you later change the default then it would only affect newly
inserted rows which will have the new default value explicitly
included anyways. And if likewise if you later make the row nullable
the column will be explicitly listed in natts and the null bitmap.

One gotcha I can think of is if the default expression depends on
something like a type. If the default is later changed then people
might be surprised to find there's still an invisible dependency on
the type. But if it's limited to simple constants that should minimize
that issue quite a bit. At least for types the column itself will have
the same dependency anyways.

--
greg

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zdenek Kotala 2009-05-21 15:20:59 Re: psql is broken in 8.4
Previous Message Robert Haas 2009-05-21 15:07:04 Re: Fast ALTER TABLE ... ADD COLUMN ... DEFAULT xxx?