Re: Validating CHECK constraints with SPI

On Wed, Oct 29, 2014 at 7:17 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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