public inbox for [email protected]
help / color / mirror / Atom feedRe: Validating check constraints without a table scan?
2+ messages / 2 participants
[nested] [flat]
* Re: Validating check constraints without a table scan?
@ 2024-11-20 09:45 jian he <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: jian he @ 2024-11-20 09:45 UTC (permalink / raw)
To: Philip Couling <[email protected]>; +Cc: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Validating check constraints without a table scan?
@ 2024-11-20 11:16 Philip Couling <[email protected]>
parent: jian he <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Philip Couling @ 2024-11-20 11:16 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: [email protected]
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.
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-20 11:16 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-20 09:45 Re: Validating check constraints without a table scan? jian he <[email protected]>
2024-11-20 11:16 ` Philip Couling <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox