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