Re: Adding a not null constraint faster

Joe Van Dyk wrote:
> Is there a way to add a NOT NULL constraint to a column without having
> to lock the table while a sequential read happens?
>
> Seems like it should be possible to add an index on the column for
> null values, like:
> create index on t using btree(col_name) where col_name is null;
>
> Then when adding the not null constraint, pg could lock the table, use
> that index to see if there's any nulls, and if there's not, add the
> constraint.

It is not possible right now. Ideally you should be able to add
CONSTRAINT NOT NULL NOT VALID; that only inserts the catalog tuple and
enforces the constraint on all future tuples, and so should be quick.
In a later step you'd do VALIDATE CONSTRAINT, with a reduced lock, which
runs the seqscan.

To implement this we need to have NOT NULL in catalogs. There is an
incomplete patch I wrote for this, but it needs some further work (along
as, I imagine, some rebasing). You can see the latest version I had at

http://www.postgresql.org/message-id/20130802040319.GF5669@eldon.alvh.no-ip.org

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services