Re: Why we allow CHECK constraint contradiction? - Mailing list pgsql-hackers

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