Conflicting constraint being merged - Mailing list pgsql-hackers

Currently, if child table has a non-inherited constraint and a constraint
with the same name is added to the parent, it will fail with an error as
illustrated below:

create table parent (a int);
CREATE TABLE
create table child (constraint check_a check (a > 0) no inherit) inherits
(parent);
CREATE TABLE
alter table parent add constraint check_a check (a > 0);
ERROR:  constraint "check_a" conflicts with non-inherited constraint on
relation "child"

Or if parent with a inheritable constraint is added to inheritance parents
of child with non-inherited constraint of the same name:

create table parent (a int, constraint check_a check (a > 0));
CREATE TABLE
create table child (a int, constraint check_a check (a > 0) no inherit)
CREATE TABLE
alter table child inherit parent;
ERROR:  constraint "check_a" conflicts with non-inherited constraint on
child table "child"

If we had allowed it to be merged, any children of child itself won't
inherit that constraint (because on child it's marked NO INHERIT), which
would not be good.


However, it is still possible for a child to override/hide the parent's
constraint as follows:

create table parent (a int, constraint check_a check (a > 0));
CREATE TABLE
create table child (constraint check_a check (a > 0) no inherit) inherits
(parent);
CREATE TABLE

Note that child's "no inherit" constraint check_a has been successfully
created.  If we create its child table, the same won't be inherited.

create table child_child (a int) inherits (child);
CREATE TABLE

At this point:

\d parent
    Table "public.parent"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Check constraints:
    "check_a" CHECK (a > 0)
Number of child tables: 1 (Use \d+ to list them.)

\d child
     Table "public.child"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Check constraints:
    "check_a" CHECK (a > 0) NO INHERIT
Inherits: parent
Number of child tables: 1 (Use \d+ to list them.)

\d child_child
  Table "public.child_child"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Inherits: child

Inserting 0 into child_child is fine, whereas it's not fine in parent or
child.

insert into child_child values (0);
INSERT 0 1

Selecting from parent will now return some rows violating one of its valid
constraints viz. check (a > 0).

select * from parent;
 a
---
 0
(1 row)


I am afraid this is an oversight/bug.  Attached tries to fix the same - In
MergeWithExistingConstraint(), we check if the new "no inherit" constraint
being added to a relation matches one of its existing constraints that is
*inherited*.  So the following will now happen:

create table parent (a int, constraint check_a check (a > 0));
CREATE TABLE
create table child (constraint check_a check (a > 0) no inherit) inherits
(parent);
ERROR:  constraint "check_a" conflicts with inherited constraint on
relation "child"

Thoughts?

Thanks,
Amit