Re: Validating CHECK constraints with SPI

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.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services