Re: Validating CHECK constraints with SPI

On Wed, Oct 29, 2014 at 10:24:26AM -0400, Tom Lane wrote:
> Dan Robinson <dan(at)drob(dot)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.

Consider the case of adding a NOT NULL constraint. Most single-column btree
indexes can quickly determine whether the column contains nulls, so the DBA
may well get the benefit on the strength of an already-present index.

> 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.

There will be no point in building a throwaway index for this, agreed.