Re: Review: Non-inheritable check constraints - Mailing list pgsql-hackers
| From | Nikhil Sontakke |
|---|---|
| Subject | Re: Review: Non-inheritable check constraints |
| Date | |
| Msg-id | CANgU5ZcHrm+LvqvPObeJUR8J17C8XPFjpoHyZV30rUNYAceKNQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Review: Non-inheritable check constraints (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: Review: Non-inheritable check constraints
(Robert Haas <robertmhaas@gmail.com>)
|
| List | pgsql-hackers |
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.
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
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.
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.
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.
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.
It's an error for inherited constraints, but not for "only" constraints.
Yeah, only constraints removal succeeds, whereas inherited constraints cannot be removed.
This removes the constraint from A, but maintains the inheritance relationship between B and C. Again standard existing inheritance semantics.
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.
The constraint gets added to A and since it is an "only" constraint, its removal both with and without "only A" works just fine.
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
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
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
pgsql-hackers by date: