Re: Review: Non-inheritable check constraints

On Tue, Dec 20, 2011 at 1:14 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:
> Agreed. I just tried out the scenarios laid out by you both with and without
> the committed patch and AFAICS, normal inheritance semantics have been
> preserved properly even after the commit.

No, they haven't. I didn't expect this to break anything when you
have two constraints with different names. The problem is when you
have two constraints with the same name.

Testing reveals that this is, in fact, broken:

rhaas=# create table A(ff1 int);
CREATE TABLE
rhaas=# create table B () inherits (A);
CREATE TABLE
rhaas=# create table C () inherits (B);
CREATE TABLE
rhaas=# alter table only b add constraint chk check (ff1 > 0);
ALTER TABLE
rhaas=# alter table a add constraint chk check (ff1 > 0);
NOTICE: merging constraint "chk" with inherited definition
ALTER TABLE

At this point, you'll find that a has a constraint, and b has a
constraint, but *c does not have a constraint*. That's bad, because
a's constraint wasn't "only" and should therefore have propagated all
the way down the tree.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company