Re: Review: Non-inheritable check constraints

And yeah, certainly there's a bug as you point out.

postgres=# create table a1 (ff1 int, constraint chk check (ff1 > 0));
postgres=# create table b1 (ff1 int);
postgres=# alter table only b1 add constraint chk check (ff1 > 0);
postgres=# alter table b1 inherit a1;

The last command should have refused to inherit.

Regards,
Nikhils

On Fri, Dec 23, 2011 at 8:55 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:

> Hi,
>
>
>> 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.
>>
>>
> As you rightly point out, this is because we cannot specify ONLY
> constraints inside a CREATE TABLE as of now.
>
>
>> 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.
>>
>>
> ONLY has inheritance based connotations for present/future children. ALTER
> ONLY combined with ADD is honored better now with this patch IMO (atleast
> for constraints I think).
>
>
>> 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.
>>
>>
> Well, the above was thought about during the original discussion and
> eventually we felt that CREATE TABLE already has other issues as well, so
> not having this done as part of creating a table was considered acceptable:
>
>
> http://postgresql.1045698.n5.nabble.com/Check-constraints-on-partition-parents-only-tt4633334.html#a4647144
>
>
>> 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.
>>
>>
> Sure, if we all think that CREATE TABLE should support ONLY CONSTRAINT
> type of syntax, then +1 for reverting this and a subsequent revised
> submission.
>
> Regards,
> Nikhils
>