Validating CHECK constraints with SPI

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?

This would make it possible to do something like:
postgres=# CREATE INDEX CONCURRENTLY foo_temp_idx ON my_table (id) WHERE
foo = 'bar';
postgres=# SELECT COUNT(*) FROM my_table WHERE foo = 'bar'; -- Make sure
this is 0.
postgres=# ALTER TABLE my_table ADD CONSTRAINT my_check CHECK (foo !=
'bar');
postgres=# DROP INDEX foo_temp_idx;

The third step here would be fast, because it would be able to use
foo_temp_idx under the hood. Additionally, it would be easy to get a sense
for how long this operation will lock your table by timing the query in the
second step. (I suppose the latter is true already if you do the same with
enable_indexscan off, but that requires knowing that PostgreSQL is going to
do the seq scan no matter what.)

Would y'all be open to a patch that made this change?

Best,
-Dan