public inbox for [email protected]  

help / color / mirror / Atom feed

From: Philip Couling <[email protected]>
To: jian he <[email protected]>
Cc: [email protected]
Subject: Re: Validating check constraints without a table scan?
Date: Wed, 20 Nov 2024 11:16:20 +0000
Message-ID: <CANWftzLezTEGwGr=dVRKMvQ0mWqcudV_EbtywFHEetynGuX6KA@mail.gmail.com> (raw)
In-Reply-To: <CACJufxEeiT7kDS=x8jC25n+vtmxPQsW9zN_qgEtbpqH4dF-Dyw@mail.gmail.com>
References: <CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com>
	<CACJufxEeiT7kDS=x8jC25n+vtmxPQsW9zN_qgEtbpqH4dF-Dyw@mail.gmail.com>

Jian He

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

An example constraint from the documentation:
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= 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 >= 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 >= DATE '2008-02-01' AND logdate < DATE
   '2008-03-01')
   - to: logdate < DATE '2008-02-01' OR logdate >= DATE '2008-03-01'

Hope that clarifies it.

On Wed, 20 Nov 2024 at 09:45, jian he <[email protected]> wrote:

> On Fri, Nov 15, 2024 at 4:38 PM Philip Couling <[email protected]> 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.
>

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Validating check constraints without a table scan?
  In-Reply-To: <CANWftzLezTEGwGr=dVRKMvQ0mWqcudV_EbtywFHEetynGuX6KA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox