public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Jian He <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions
Date: Tue, 5 Apr 2022 21:33:47 -0700
Message-ID: <CAKFQuwbdyufgi35mtyGSL9rBqgVDn4nRgnrARcGv1=n2evonyA@mail.gmail.com> (raw)
In-Reply-To: <CAMV54g2OxGKMyuUVwNM0LdDZsvM7XJVbe4WmVRMLNTZ5X1d0Gg@mail.gmail.com>
References: <CAMV54g2OxGKMyuUVwNM0LdDZsvM7XJVbe4WmVRMLNTZ5X1d0Gg@mail.gmail.com>

On Tue, Apr 5, 2022 at 9:08 PM Jian He <[email protected]> wrote:

> If the question is stupid, please tell me why.
>
Not stupid, but the question as phrased is making an assumption about how
the system works that isn't true.  Or, at least, I don't understand what
you mean by "deduce it" and "query it"?  The fact those check constraints
exist shows you PostgreSQL doesn't deduce it in order to prevent their
creation.  There is also nothing provided to query existing constraints and
sanity check them.  Nor is such a feature all that valuable - such
constraints are seldom written and the expected cursory testing that should
go along with such a thing, or even the fact that the system should
probably not function while they exist, means that when they do get created
they are quickly discovered.  For the redundant check constraint, it is not
so easily discovered but neither does it seem like a big deal - so long as
the relevant operator is cheap to execute.  That tends to be the case for
check constraints.  When they are not, they usually are not easily figured
out to be logically redundant either.

I wrote the following having mis-understood your question.  I will leave it
because it may be informative, and is at least tangentially on-topic:

IIUC, queries look at statistics to make decisions.  They will also look
at, I think, unique constraints at the table level and not null constraints
at the column level.  Not sure about references - I think the unique and
stats handles those sufficiently.

CREATE TABLE emp (test_check int check ( test_check >1 and test_check
> < 0 ));
>
> alter table emp  VALIDATE CONSTRAINT emp_test_check_check;```
>
> select * from pg_constraint where conname = 'emp_test_check_check';
>
> Even with the above mentioned step, does postgresql know above check
> constraint condition always false.
>
Indirectly, it will know, using statistics, that either the table itself is
empty or that the most common, and only, value for the column is null
(null_frac = 1.0)

>
> another similar question:
> can postgresql deduce from
> CREATE TABLE emp1 (test_check int check ( test_check >1 and test_check >
> 10 ));
>
> to
>
> CREATE TABLE emp1 (test_check int check ( test_check > 10 ));
>
I think only in the resulting statistics, most likely in this case the
lowest histogram_bounds boundary should be 11.

David J.

view thread (3+ 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: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions
  In-Reply-To: <CAKFQuwbdyufgi35mtyGSL9rBqgVDn4nRgnrARcGv1=n2evonyA@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