On Wed, Oct 29, 2014 at 7:17 AM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote:
> Dan Robinson wrote: > > Hi all, > > > > If I'm reading correctly in src/backend/commands/tablecmds.c, it looks > like > > PostgreSQL does a full table scan in validateCheckConstraint and in the > > constraint validation portion of ATRewriteTable. > > > > Since the table is locked to updates while the constraint is validating, > > this means you have to jump through hoops if you want to add a CHECK > > constraint to a large table in a production setting. This validation > could > > be considerably faster if we enabled it to use relevant indexes or other > > constraints. Is there a reason not to make an SPI call here, instead? > > I don't think SPI would help you here. But I think you would like to > add the constraint as NOT VALID and then do an ALTER TABLE .. VALIDATE > CONSTRAINT command afterwards. In 9.4, this doesn't require > AccessExclusive lock on the table. Interesting! I hadn't seen the patch that makes ALTER TABLE ... VALIDATE CONSTRAINT require only ShareUpdateExclusive. Very cool. Yes, that makes this change totally unnecessary. -Dan
