Re: Validating CHECK constraints with SPI

Dan Robinson <dan@drob.us> writes:
> 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?

This seems like a lot of work for a gain that would only occur sometimes,
ie if the CHECK happened to correspond to a usable index condition.
I realize your point is that a clever DBA might intentionally create
such an index, but I don't think that people would bother in practice.
It's not any simpler, nor faster, than using the existing approach with
ALTER TABLE ADD CONSTRAINT NOT VALID followed by ALTER TABLE VALIDATE
CONSTRAINT.
        regards, tom lane