Re: Review: Non-inheritable check constraints

Excerpts from Nikhil Sontakke's message of vie dic 23 00:25:26 -0300 2011:
> 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.

No, it's not related to that. You could do it even without that, by
creating a table then adding an ONLY constraint and only later doing the
ALTER TABLE / INHERIT. The problem we need to fix here is that this
command needs to check that there are no unmergeable constraints; and if
there are any, error out.

> > 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).

I agree with Robert that this usage of ALTER TABLE ONLY is slightly
different from other usages of the same command, but I disagree that
this means that we need another command to do what we want to do here.
IOW, I prefer to keep the syntax we have.

> > 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.

I don't think this is a given ... In fact, IMO if we're only two or
three fixes away from having it all nice and consistent, I think
reverting is not necessary.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support