| From: | Philip Couling <couling(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Validating check constraints without a table scan? |
| Date: | 2024-11-14 21:33:31 |
| Message-ID: | CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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)?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2024-11-15 08:44:08 | Re: Validating check constraints without a table scan? |
| Previous Message | Adrian Klaver | 2024-11-14 17:02:16 | Re: Help with restoring database from old version of PostgreSQL |