On 02/25/2014 04:41 AM, Zev Benjamin wrote: > I'm conceptually trying to do > ALTER TABLE "foo" ADD COLUMN "bar" boolean NOT NULL DEFAULT False; > > without taking any noticeable downtime. I know I can divide the query > up like so: > > ALTER TABLE "foo" ADD COLUMN "bar" boolean; > UPDATE foo SET bar = False; -- Done in batches > ALTER TABLE "foo" ALTER COLUMN "bar" SET DEFAULT False; > ALTER TABLE "foo" ALTER COLUMN "bar" SET NOT NULL; You need to set the default before doing the update. Also, make sure the update is in its own transaction. > The first 3 queries shouldn't impact other concurrent queries on the > system. My question is about the sequential scan that occurs when > setting the column NOT NULL. Will that sequential scan block other > inserts or selects on the table? Yes, because ALTER TABLE will have taken an AccessExclusiveLock. > If so, can it be sped up by using an index (which would be created > concurrently)? Unfortunately not. -- Vik -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
