Re: Why we allow CHECK constraint contradiction?

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@mail.gmail.com

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. 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."

:)

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CA+TgmoaQABrsLQK4ms_4NiyavyJGS-b6ZFkZBBNC+-P5DjJNFA@mail.gmail.com