Re: Review: Non-inheritable check constraints

Hi Robert,

First of all, let me state that this "ONLY" feature has not messed around
with existing inheritance semantics. It allows attaching a constraint to
any table (which can be part of any hierarchy) without the possibility of
it ever playing any part in future or existing inheritance hierarchies. It
is specific to that table, period.

It's not just that. Suppose that C inherits from B which inherits
> from A. We add an "only" constraint to B and a non-"only" constraint
> to "A". Now, what happens in each of the following scenarios?
>
>
An example against latest HEAD should help here:

create table A(ff1 int);
create table B () inherits (A);
create table C () inherits (B);

alter table A add constraint Achk check (ff1 > 10);

The above will attach Achk to A, B and C

alter table only B add constraint Bchk check (ff1 > 0);

The above will attach Bchk ONLY to table B

1. We drop the constraint from "B" without specifying ONLY.
>

postgres=# alter table B drop constraint Achk;
ERROR: cannot drop inherited constraint "achk" of relation "b"

The above is existing inheritance based behaviour.

Now let's look at the ONLY constraint:

postgres=# alter table B drop constraint Bchk;
ALTER TABLE

Since this constraint is not part of any hierarchy, it can be removed.

postgres=# alter table only B add constraint bchk check (ff1 > 0);
ALTER TABLE
postgres=# alter table only B drop constraint Bchk;
ALTER TABLE

So "only" constraints do not need the "only B" qualification to be
deleted. They work both ways and can always be deleted without any issues.

2. We drop the constraint from "B" *with* ONLY.
>

postgres=# alter table only B drop constraint Achk;
ERROR: cannot drop inherited constraint "achk" of relation "b"

The above is existing inheritance based behavior. So regardless of
ONLY an inherited constraint cannot be removed from the middle of the
hierarchy.

> 3. We drop the constraint from "A" without specifying ONLY.
>

postgres=# alter table A drop constraint Achk;
ALTER TABLE

This removes the constraint from the entire hierarchy across A, B and
C. Again existing inheritance behavior.

> 4. We drop the constraint from "A" *with* ONLY.
>
>
postgres=# alter table only A drop constraint Achk;
ALTER TABLE

This converts the Achk constraints belonging to B into a local one. C
still has it as an inherited constraint from B. We can now delete those
constraints as per existing inheritance semantics. However I hope the
difference between these and ONLY constraints are clear. The Achk
constraint associated with B can get inherited in the future whereas "only"
constraints will not be.

> Off the top of my head, I suspect that #1 should be an error;

It's an error for inherited constraints, but not for "only" constraints.

> #2
> should succeed, leaving only the inherited version of the constraint
> on B;

Yeah, only constraints removal succeeds, whereas inherited constraints
cannot be removed.

> #3 should remove the constraint from A and leave it on B but I'm
> not sure what should happen to C,

This removes the entire hierarchy.

> and I have no clear vision of what
> #4 should do.
>
>
This removes the constraint from A, but maintains the inheritance
relationship between B and C. Again standard existing inheritance semantics.

As a followup question, if we do #2 followed by #4, or #4 followed by
> #2, do we end up with the same final state in both cases?
>
>
Yeah. #2 is not able to do much really because we do not allow inherited
constraints to be removed from the mid of the hierarchy.

> Here's another scenario. B inherits from A. We a constraint to A
> using ONLY, and then drop it without ONLY. Does that work or fail?
>

The constraint gets added to A and since it is an "only" constraint, its
removal both with and without "only A" works just fine.

> Also, what happens we add matching constraints to B and A, in each
> case using ONLY, and then remove the constraint from A without using
> ONLY? Does anything happen to B's constraint? Why or why not?
>
>
Again the key differentiation here is that "only" constraints are bound to
that table and wont be inherited ever. So this works just fine.

postgres=# alter table only A add constraint A2chk check (ff1 > 10);
ALTER TABLE
postgres=# alter table only B add constraint A2chk check (ff1 > 10);
ALTER TABLE

Just to be clear, I like the feature. But I've done some work on this
> code before, and it is amazingly easy for to screw it up and end up
> with bugs... so I think lots of careful thought is in order.
>
>
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.

Regards,
Nikhils