MIME-Version: 1.0
From: Philip Couling <couling@gmail.com>
Date: Thu, 14 Nov 2024 21:33:31 +0000
Message-ID: <CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com>
Subject: Validating check constraints without a table scan?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006413290626e63424"
Archived-At: <https://www.postgresql.org/message-id/CANWftzK2MZ7Js_56V%2BZcLxZyH1utBZx4uEg03P7Cee86K2roCQ%40mail.gmail.com>
Precedence: bulk

--0000000000006413290626e63424
Content-Type: text/plain; charset="UTF-8"

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

--0000000000006413290626e63424
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Is there a solid reason why adding a check constraint does not use existing=
 indexes for validation.<div dir=3D"auto"><br></div><div dir=3D"auto">We ar=
e currently looking at partitioning a multi TB table leaving all existing d=
ata in place and simply attaching it as a partition to a new table. To prev=
ent locking, we are trying to add an INVALID check constraint first and the=
n validate it.</div><div dir=3D"auto"><br></div><div dir=3D"auto">I can tri=
vially prove the invalid constraint is valid with a simple SELECT which wil=
l use an existing index and return instantaneously. But AFAIK Theres no way=
 to mark a constraint as valid without scanning all the rows.</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">This operation is really problematic =
on a production database with heavy IO load.</div><div dir=3D"auto"><br></d=
iv><div dir=3D"auto">Is there a solid ready why validating check constraint=
s cannot use existing indexes? If I can prove the constraint is valid so tr=
ivially with a SELECT, then why can Postgres not do the same (or similar)?<=
/div>

--0000000000006413290626e63424--