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: | Raw Message | Whole Thread | 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 |