Re: Why we allow CHECK constraint contradiction?
От | Amit Langote |
---|---|
Тема | Re: Why we allow CHECK constraint contradiction? |
Дата | |
Msg-id | 92053064-a5ce-7a71-438e-0e146d7d1457@lab.ntt.co.jp обсуждение исходный текст |
Ответ на | Why we allow CHECK constraint contradiction? ("Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>) |
Ответы |
RE: Why we allow CHECK constraint contradiction?
("Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>)
|
Список | 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
В списке pgsql-hackers по дате отправления: