Validating check constraints without a table scan?

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)?

Responses

Browse pgsql-general by date

  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