Validating check constraints without a table scan?

Поиск
Список
Период
Сортировка
От Philip Couling
Тема Validating check constraints without a table scan?
Дата
Msg-id CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Validating check constraints without a table scan?
Re: Validating check constraints without a table scan?
Re: Validating check constraints without a table scan?
Список pgsql-general
Is there a solid reason why adding a check constraint does not use existing indexes for validation.

We are currently looking at partitioning a multi TB table leaving all existing data in place and simply attaching it as a partition to a new table. To prevent locking, we are trying to add an INVALID check constraint first and then validate it.

I can trivially prove the invalid constraint is valid with a simple SELECT which will use an existing index and return instantaneously. But AFAIK Theres no way to mark a constraint as valid without scanning all the rows.

This operation is really problematic on a production database with heavy IO load.

Is there a solid ready why validating check constraints cannot use existing indexes? If I can prove the constraint is valid so trivially with a SELECT, then why can Postgres not do the same (or similar)?

В списке pgsql-general по дате отправления: