RE: Why we allow CHECK constraint contradiction?

On Tue, Oct 9, 2018 at 6:01 PM, Amit Langote wrote:
> On 2018/10/10 14:25, Imai, Yoshikazu wrote:
> > Hi, all.
> >
> > I have a wonder about the behaviour of creating table which has a
> > constraint contradiction.
> >
> > I created below table.
> >
> > bugtest=# create table ct (a int, CHECK(a is not null and a >= 0 and
> a
> > < 100 and a >= 200 and a < 300)); bugtest=# \d+ ct
> > Table "public.ct"
> > Column | Type | Collation | Nullable | Default | Storage | Stats
> target | Description
> >
> --------+---------+-----------+----------+---------+---------+------
> --------+-------------
> > a | integer | | | | plain |
> |
> > Check constraints:
> > "ct_a_check" CHECK (a IS NOT NULL AND a >= 0 AND a < 100 AND a >=
> > 200 AND a < 300)
> >
> >
> > Are there any rows which can satisfy the ct's CHECK constraint? If
> > not, why we allow creating table when check constraint itself is
> contradicted?
> >
> >
> > I originally noticed this while creating partitioned range table as
> below.
> >
> > bugtest=# create table rt (a int) partition by range (a); bugtest=#
> > create table rt_sub1 partition of rt for values from (0) to (100)
> > partition by range (a); bugtest=# create table rt_sub2 partition of
> rt
> > for values from (100) to (200) partition by range (a); bugtest=#
> > create table rt150 partition of rt_sub1 for values from (150) to (151);
> bugtest=# \d+ rt_sub1
> > Table "public.rt_sub1"
> > Column | Type | Collation | Nullable | Default | Storage | Stats
> target | Description
> >
> --------+---------+-----------+----------+---------+---------+------
> --------+-------------
> > a | integer | | | | plain |
> |
> > Partition of: rt FOR VALUES FROM (0) TO (100) Partition constraint:
> > ((a IS NOT NULL) AND (a >= 0) AND (a < 100)) Partition key: RANGE (a)
> > Partitions: rt150 FOR VALUES FROM (150) TO (151)
> >
> > bugtest=# \d+ rt150
> > Table "public.rt150"
> > Column | Type | Collation | Nullable | Default | Storage | Stats
> target | Description
> >
> --------+---------+-----------+----------+---------+---------+------
> --------+-------------
> > a | integer | | | | plain |
> |
> > Partition of: rt_sub1 FOR VALUES FROM (150) TO (151) Partition
> > constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 100) AND (a IS NOT
> > NULL) AND (a >= 150) AND (a < 151))
> >
> >
> > Any rows are not routed to rt150 through rt nor we can't insert any
> > rows to
> > rt150 directly because of its constraints. If we add check whether
> > constraint is contradicted, it prevent us from accidentally creating
> > useless table like above rt150 which would not contain any rows.
> >
> > I thought there might be a discussion or documentation about this, but
> > I couldn't find it. If there is, please also tell me that.
>
> I had wondered about it when developing the partitioning feature about
> a couple of years ago and this is the response I'd gotten:
>
> https://www.postgresql.org/message-id/CA+TgmoaQABrsLQK4ms_4NiyavyJGS
> -b6ZFkZBBNC+-P5DjJNFA(at)mail(dot)gmail(dot)com

Thanks for tell me one of a discussion about this.

> To summarize, the answer I got was that it's pointless to create defenses
> against it inside the database. It's on the users to create the
> constraints (or specify bounds) that are non-contradicting.

I just thought it's kind to tell users whether users mistakenly specify
bounds.

> Interesting quotes from the above email:
>
> "If we allow partitioning on expressions, then it quickly becomes
> altogether impossible to deduce anything useful - unless you can solve
> the halting problem."
>
> "... This patch is supposed to be implementing partitioning, not
> artificial intelligence."

It takes little more time to completely understand this interesting quotes,
but I guess I see that point.

Thanks again!

--
Yoshikazu Imai