Hi,
On Fri, Mar 7, 2008 at 6:37 AM, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
> Added to TODO:
>
> > o Require all CHECK constraints to be inherited
> >
> > http://archives.postgresql.org/pgsql-bugs/2007-04/msg00026.php
>
>
PFA, a small patch attached which should fix this. I have made relevant
changes in the relevant regression files too.
I was wondering though if there are other locations where we might need to
add checks to ensure that ALTER TABLE ONLY parentrel operations are ok? I
did see checks for this in some other operations like ADD COLUMN already in
place too.
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.242
diff -c -r1.242 tablecmds.c
*** src/backend/commands/tablecmds.c 7 Feb 2008 17:09:51 -0000 1.242
--- src/backend/commands/tablecmds.c 19 Mar 2008 07:41:32 -0000
***************
*** 229,234 ****
--- 229,236 ----
bool recurse, bool recursing);
static void ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild);
+ static void ATPrepAddConstraint(List **wqueue, Relation rel, bool recurse,
+ AlterTableCmd *cmd);
static void ATExecAddConstraint(AlteredTableInfo *tab, Relation rel,
Node *newConstraint);
static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
***************
*** 1949,1962 ****
break;
case AT_AddConstraint: /* ADD CONSTRAINT */
ATSimplePermissions(rel, false);
!
! /*
! * Currently we recurse only for CHECK constraints, never for
! * foreign-key constraints. UNIQUE/PKEY constraints won't be seen
! * here.
! */
! if (IsA(cmd->def, Constraint))
! ATSimpleRecursion(wqueue, rel, cmd, recurse);
/* No command-specific prep needed */
pass = AT_PASS_ADD_CONSTR;
break;
--- 1951,1958 ----
break;
case AT_AddConstraint: /* ADD CONSTRAINT */
ATSimplePermissions(rel, false);
! /* Performs own recursion */
! ATPrepAddConstraint(wqueue, rel, recurse, cmd);
/* No command-specific prep needed */
pass = AT_PASS_ADD_CONSTR;
break;
***************
*** 3833,3838 ****
--- 3829,3861 ----
}
/*
+ * ALTER TABLE ADD CONSTRAINT
+ *
+ * Currently we recurse only for CHECK constraints, never for
+ * foreign-key constraints. UNIQUE/PKEY constraints won't be seen
+ * here.
+ */
+ static void
+ ATPrepAddConstraint(List **wqueue, Relation rel, bool recurse,
+ AlterTableCmd *cmd)
+ {
+ if (IsA(cmd->def, Constraint))
+ {
+ if (recurse)
+ ATSimpleRecursion(wqueue, rel, cmd, recurse);
+ else
+ /*
+ * If we were told not to recurse, there better not be any
+ * children tables around
+ */
+ if (find_inheritance_children(RelationGetRelid(rel)) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("constraint must be added to children tables too")));
+ }
+ }
+
+ /*
* ALTER TABLE ADD CONSTRAINT
*/
static void
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.104
diff -c -r1.104 alter_table.out
*** src/test/regress/expected/alter_table.out 29 Oct 2007 21:31:28 -0000 1.104
--- src/test/regress/expected/alter_table.out 19 Mar 2008 07:41:32 -0000
***************
*** 314,319 ****
--- 314,323 ----
create table atacc1 (test int);
create table atacc2 (test2 int);
create table atacc3 (test3 int) inherits (atacc1, atacc2);
+ -- only add constraint should fail on atacc2
+ alter table only atacc2 add constraint foo check (test2>0);
+ ERROR: constraint must be added to children tables too
+ -- add constraint to parent and children
alter table atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
***************
*** 378,398 ****
NOTICE: drop cascades to table atacc3
NOTICE: drop cascades to constraint foo on table atacc3
drop table atacc1;
- -- let's try only to add only to the parent
- create table atacc1 (test int);
- create table atacc2 (test2 int);
- create table atacc3 (test3 int) inherits (atacc1, atacc2);
- alter table only atacc2 add constraint foo check (test2>0);
- -- fail and then succeed on atacc2
- insert into atacc2 (test2) values (-3);
- ERROR: new row for relation "atacc2" violates check constraint "foo"
- insert into atacc2 (test2) values (3);
- -- both succeed on atacc3
- insert into atacc3 (test2) values (-3);
- insert into atacc3 (test2) values (3);
- drop table atacc3;
- drop table atacc2;
- drop table atacc1;
-- test unique constraint adding
create table atacc1 ( test int ) with oids;
-- add a unique constraint
--- 382,387 ----
Index: src/test/regress/sql/alter_table.sql
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/test/regress/sql/alter_table.sql,v
retrieving revision 1.56
diff -c -r1.56 alter_table.sql
*** src/test/regress/sql/alter_table.sql 14 Feb 2007 01:58:58 -0000 1.56
--- src/test/regress/sql/alter_table.sql 19 Mar 2008 07:41:32 -0000
***************
*** 344,349 ****
--- 344,352 ----
create table atacc1 (test int);
create table atacc2 (test2 int);
create table atacc3 (test3 int) inherits (atacc1, atacc2);
+ -- only add constraint should fail on atacc2
+ alter table only atacc2 add constraint foo check (test2>0);
+ -- add constraint to parent and children
alter table atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
***************
*** 389,410 ****
drop table atacc2 cascade;
drop table atacc1;
- -- let's try only to add only to the parent
-
- create table atacc1 (test int);
- create table atacc2 (test2 int);
- create table atacc3 (test3 int) inherits (atacc1, atacc2);
- alter table only atacc2 add constraint foo check (test2>0);
- -- fail and then succeed on atacc2
- insert into atacc2 (test2) values (-3);
- insert into atacc2 (test2) values (3);
- -- both succeed on atacc3
- insert into atacc3 (test2) values (-3);
- insert into atacc3 (test2) values (3);
- drop table atacc3;
- drop table atacc2;
- drop table atacc1;
-
-- test unique constraint adding
create table atacc1 ( test int ) with oids;
--- 392,397 ----
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
