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.
