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