From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Joe Van Dyk <joe(at)tanga(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Adding a not null constraint faster |
Date: | 2014-05-17 15:58:57 |
Message-ID: | 20140517155857.GD7857@eldon.alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2014-05-18 08:40:53 | Is it typo in pg_stat_replication column name in PG 9.4 ? |
Previous Message | Thomas Kellerer | 2014-05-17 12:25:00 | 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION |