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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers