MIME-Version: 1.0
References: <CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com>
 <CACJufxEeiT7kDS=x8jC25n+vtmxPQsW9zN_qgEtbpqH4dF-Dyw@mail.gmail.com>
In-Reply-To: <CACJufxEeiT7kDS=x8jC25n+vtmxPQsW9zN_qgEtbpqH4dF-Dyw@mail.gmail.com>
From: Philip Couling <couling@gmail.com>
Date: Wed, 20 Nov 2024 11:16:20 +0000
Message-ID: <CANWftzLezTEGwGr=dVRKMvQ0mWqcudV_EbtywFHEetynGuX6KA@mail.gmail.com>
Subject: Re: Validating check constraints without a table scan?
To: jian he <jian.universality@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000002dcfdf0627564853"
Archived-At: <https://www.postgresql.org/message-id/CANWftzLezTEGwGr%3DdVRKMvQ0mWqcudV_EbtywFHEetynGuX6KA%40mail.gmail.com>
Precedence: bulk

--0000000000002dcfdf0627564853
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Jian He

The context here is constraints for partitioning as suggested in
documentation
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION=
ING-DECLARATIVE-MAINTENANCE

An example constraint from the documentation:
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >=3D DATE '2008-02-01' AND logdate < DATE '2008-03-01' )=
;

If logdate is indexed, then this constraint can be manually validated very
quickly using a SELECT that will take advantage of the index
SELECT 1 FROM measurement_y2008m02  WHERE logdate < DATE '2008-02-01' OR
logdate >=3D DATE '2008-03-01' LIMIT 1

If the constraint is valid the query will return quickly with no rows, if
any rows violate the constraint it will also return very quickly but return
with a single row with column value: 1.

I guess that validating constraints doesn't invoke the query planner, or
otherwise the conversion is too complex for the query planner. The
conversion being:

   - from:  NOT (logdate >=3D DATE '2008-02-01' AND logdate < DATE
   '2008-03-01')
   - to: logdate < DATE '2008-02-01' OR logdate >=3D DATE '2008-03-01'

Hope that clarifies it.

On Wed, 20 Nov 2024 at 09:45, jian he <jian.universality@gmail.com> wrote:

> On Fri, Nov 15, 2024 at 4:38=E2=80=AFPM Philip Couling <couling@gmail.com=
> wrote:
> >
> > Is there a solid reason why adding a check constraint does not use
> existing indexes for validation.
> >
>
> can you give an sql example (except not-null)
> where indexes can be used for check constraint validation?
> i am not sure I understand it correctly.
>

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

<div dir=3D"ltr"><div>Jian He</div><div><br></div>The context here is const=
raints for partitioning as suggested in documentation=C2=A0<div><a href=3D"=
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION=
ING-DECLARATIVE-MAINTENANCE">https://www.postgresql.org/docs/current/ddl-pa=
rtitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE</a><div><br></div>=
<div>An example constraint from the documentation:</div><div><font face=3D"=
monospace">ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02<br>=C2=
=A0 =C2=A0CHECK ( logdate &gt;=3D DATE &#39;2008-02-01&#39; AND logdate &lt=
; DATE &#39;2008-03-01&#39; );</font></div><div><font face=3D"monospace"><b=
r></font></div><div><font face=3D"arial, sans-serif">If </font><font face=
=3D"monospace">logdate</font><font face=3D"arial, sans-serif">=C2=A0is inde=
xed, then this constraint can be manually validated very quickly using a SE=
LECT that will take advantage of the index</font></div><div><font face=3D"m=
onospace">SELECT 1 FROM=C2=A0</font><span style=3D"font-family:monospace">m=
easurement_y2008m02=C2=A0 WHERE=C2=A0</span><span style=3D"font-family:mono=
space">logdate &lt; DATE &#39;2008-02-01&#39; OR logdate &gt;=3D DATE &#39;=
2008-03-01&#39; LIMIT 1</span></div><div><br></div><div>If the constraint i=
s valid the query will return quickly with no rows, if any rows violate the=
 constraint it will also return very quickly but return with a single row w=
ith column value: 1.</div><div><br></div><div>I guess that validating const=
raints doesn&#39;t invoke the query planner, or otherwise the conversion is=
 too complex for the query planner. The conversion being:</div><div><ul><li=
>from:=C2=A0 NOT (logdate &gt;=3D DATE &#39;2008-02-01&#39; AND logdate &lt=
; DATE &#39;2008-03-01&#39;)</li><li>to: logdate &lt; DATE &#39;2008-02-01&=
#39; OR logdate &gt;=3D DATE &#39;2008-03-01&#39;</li></ul><div>Hope that c=
larifies it.</div></div></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Wed, 20 Nov 2024 at 09:45, jian he &lt;<a =
href=3D"mailto:jian.universality@gmail.com">jian.universality@gmail.com</a>=
&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On F=
ri, Nov 15, 2024 at 4:38=E2=80=AFPM Philip Couling &lt;<a href=3D"mailto:co=
uling@gmail.com" target=3D"_blank">couling@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; Is there a solid reason why adding a check constraint does not use exi=
sting indexes for validation.<br>
&gt;<br>
<br>
can you give an sql example (except not-null)<br>
where indexes can be used for check constraint validation?<br>
i am not sure I understand it correctly.<br>
</blockquote></div>

--0000000000002dcfdf0627564853--