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

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". PG just never needs
to flush the changes to every tuple because it knows that all "old"
tuples (i.e. ones that were created before this column was added) are
supposed to be NULL.

> But another very common-case query like
>
> ALTER TABLE ... ADD COLUMN ... BOOLEAN NOT NULL DEFAULT false;
> or
> ALTER TABLE ... ADD COLUMN ... INT NOT NULL DEFAULT 0;

> So, are there plans to optimize such kind of queries? This could be done by
> many ways:

I think this hasn't been done before because it's been considered too
difficult to keep track of everything, but I've just tried to come up
with an example of why it's difficult and failed. If I'm interpreting
things correctly it's not nearly as difficult as I thought it should
be. All that needs to be tracked is the "first" default value (this is
currently assumed to be NULL). All subsequent INSERTs will have this
value in the tuple and things should just work out.

CREATE TABLE t ( i INTEGER PRIMARY KEY );
INSERT INTO t (i) VALUES (1);
ALTER TABLE t ADD COLUMN j INTEGER DEFAULT 1;
INSERT INTO t (i) VALUES (2);
ALTER TABLE t ALTER j SET DEFAULT 2;
INSERT INTO t (i) VALUES (3);
ALTER TABLE t ALTER j DROP DEFAULT;
INSERT INTO t (i) VALUES (4);

After this we will have the following tuples:

(1)
(2,1)
(3,2)
(4,NULL)

All that needs to be done is to fill in the "default" for i=1 to the
first default (i.e. the integer 1) and everything is done.

Who wants to tell me what I've missed?

--
Sam http://samason.me.uk/