Thread: Adding a not null constraint faster

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.

(That's not possible already, right? I couldn't figure it out if it was.)

Right now, the strategy for adding a not null constraint to a column
without any significant downtime involves creating a new table, as I
understand it.

Joe

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