Re: Review: Non-inheritable check constraints

On Thu, Dec 22, 2011 at 2:43 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Nikhil Sontakke's message of mar dic 20 12:03:33 -0300 2011:
>
>> > Apologies, I did not check this particular scenario.
>> >
>> > I guess, here, we should not allow merging of the inherited constraint
>> > into an "only" constraint. Because that breaks the semantics for "only"
>> > constraints. If this sounds ok, I can whip up a patch for the same.
>> >
>> >
>> PFA, patch which does just this.
>>
>> postgres=# alter table a add constraint chk check (ff1 > 0);
>> ERROR:  constraint "chk" for relation "b" is an ONLY constraint. Cannot
>> merge
>
> I think the basic idea is fine -- the constraint certainly cannot be
> merged, and we can't continue without merging it because of the
> inconsistency it would create.

I was just looking at this as well. There is at least one other
problem. Consider:

rhaas=# create table a (ff1 int, constraint chk check (ff1 > 0));
CREATE TABLE
rhaas=# create table b (ff1 int, constraint chk check (ff1 > 0));
CREATE TABLE
rhaas=# alter table b inherit a;
ALTER TABLE

This needs to fail if chk is an "only" constraint, but it doesn't,
even with this patch.

I think that part of the problem here is fuzzy thinking about the
meaning of the word "ONLY" in "ALTER TABLE ONLY b". The word "ONLY"
there is really supposed to mean that the operation is performed on b
but not on its descendents; but that's not what you're doing: you're
really performing a different operation. In theory, for a table with
no current descendents, ALTER TABLE ONLY b and ALTER TABLE b ought to
be identical, but here they are not. I think that's probably bad.

Another manifestation of this problem is that there's no way to add an
ONLY constraint in a CREATE TABLE command. I think that's bad, too:
it should be possible to declare any constraint at table creation
time, and if the ONLY were part of the constraint definition rather
than part of the target-table specification, that would work fine. As
it is, it doesn't.

I am tempted to say we should revert this and rethink. I don't
believe we are only a small patch away from finding all the bugs here.

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