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.gf5...@eldon.alvh.no-ip.org -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
