Skip to content

Commit 7160c86

Browse files
bmomjianbmomjian
committed
These patches should fix check constraints not inheriting
when added by alter table add constraint. The first file patches backend/commands/command.c and the latter is a patch to the alter table regression test. Stephan Szabo
1 parent 3f5563d commit 7160c86

File tree

3 files changed

+180
-1
lines changed

3 files changed

+180
-1
lines changed

‎src/backend/commands/command.c

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.130 2001/05/30 12:57:36 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.131 2001/05/30 13:00:03 momjian Exp $
1212
*
1313
* NOTES
1414
* The PerformAddAttribute() code, like most of the relation
@@ -1215,6 +1215,7 @@ AlterTableAddConstraint(char *relationName,
12151215
Relation rel;
12161216
Node *expr;
12171217
char *name;
1218+
Oid myrelid;
12181219

12191220
if (constr->name)
12201221
name = constr->name;
@@ -1224,6 +1225,7 @@ AlterTableAddConstraint(char *relationName,
12241225
constlist = makeList1(constr);
12251226

12261227
rel = heap_openr(relationName, AccessExclusiveLock);
1228+
myrelid = RelationGetRelid(rel);
12271229

12281230
/* make sure it is not a view */
12291231
if (rel->rd_rel->relkind == RELKIND_VIEW)
@@ -1318,6 +1320,35 @@ AlterTableAddConstraint(char *relationName,
13181320
*/
13191321
AddRelationRawConstraints(rel, NIL, constlist);
13201322
heap_close(rel, NoLock);
1323+
1324+
if (inh) {
1325+
List *child,
1326+
*children;
1327+
1328+
/* this routine is actually in the planner */
1329+
children = find_all_inheritors(myrelid);
1330+
1331+
/*
1332+
* find_all_inheritors does the recursive search of the
1333+
* inheritance hierarchy, so all we have to do is process all
1334+
* of the relids in the list that it returns.
1335+
*/
1336+
foreach(child, children)
1337+
{
1338+
Oid childrelid = lfirsti(child);
1339+
char *childrelname;
1340+
1341+
if (childrelid == myrelid)
1342+
continue;
1343+
rel = heap_open(childrelid, AccessExclusiveLock);
1344+
childrelname = pstrdup(RelationGetRelationName(rel));
1345+
heap_close(rel, AccessExclusiveLock);
1346+
1347+
AlterTableAddConstraint(childrelname, false, newConstraint);
1348+
1349+
pfree(childrelname);
1350+
}
1351+
}
13211352
pfree(constlist);
13221353

13231354
break;

‎src/test/regress/expected/alter_table.out

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -375,3 +375,76 @@ NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FORE
375375
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
376376
You will have to retype this query using an explicit cast
377377
-- temp tables should go away by themselves, need not drop them.
378+
-- test check constraint adding
379+
create table atacc1 ( test int );
380+
-- add a check constraint
381+
alter table atacc1 add constraint atacc_test1 check (test>3);
382+
-- should fail
383+
insert into atacc1 (test) values (2);
384+
ERROR: ExecAppend: rejected due to CHECK constraint atacc_test1
385+
-- should succeed
386+
insert into atacc1 (test) values (4);
387+
drop table atacc1;
388+
-- let's do one where the check fails when added
389+
create table atacc1 ( test int );
390+
-- insert a soon to be failing row
391+
insert into atacc1 (test) values (2);
392+
-- add a check constraint (fails)
393+
alter table atacc1 add constraint atacc_test1 check (test>3);
394+
ERROR: AlterTableAddConstraint: rejected due to CHECK constraint atacc_test1
395+
insert into atacc1 (test) values (4);
396+
drop table atacc1;
397+
-- let's do one where the check fails because the column doesn't exist
398+
create table atacc1 ( test int );
399+
-- add a check constraint (fails)
400+
alter table atacc1 add constraint atacc_test1 check (test1>3);
401+
ERROR: Attribute 'test1' not found
402+
drop table atacc1;
403+
-- something a little more complicated
404+
create table atacc1 ( test int, test2 int, test3 int);
405+
-- add a check constraint (fails)
406+
alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
407+
-- should fail
408+
insert into atacc1 (test,test2,test3) values (4,4,2);
409+
ERROR: ExecAppend: rejected due to CHECK constraint atacc_test1
410+
-- should succeed
411+
insert into atacc1 (test,test2,test3) values (4,4,5);
412+
drop table atacc1;
413+
-- lets do some naming tests
414+
create table atacc1 (test int check (test>3), test2 int);
415+
alter table atacc1 add check (test2>test);
416+
-- should fail for $2
417+
insert into atacc1 (test2, test) values (3, 4);
418+
ERROR: ExecAppend: rejected due to CHECK constraint $2
419+
drop table atacc1;
420+
-- inheritance related tests
421+
create table atacc1 (test int);
422+
create table atacc2 (test2 int);
423+
create table atacc3 (test3 int) inherits (atacc1, atacc2);
424+
alter table atacc2 add constraint foo check (test2>0);
425+
-- fail and then succeed on atacc2
426+
insert into atacc2 (test2) values (-3);
427+
ERROR: ExecAppend: rejected due to CHECK constraint foo
428+
insert into atacc2 (test2) values (3);
429+
-- fail and then succeed on atacc3
430+
insert into atacc3 (test2) values (-3);
431+
ERROR: ExecAppend: rejected due to CHECK constraint foo
432+
insert into atacc3 (test2) values (3);
433+
drop table atacc3;
434+
drop table atacc2;
435+
drop table atacc1;
436+
-- let's try only to add only to the parent
437+
create table atacc1 (test int);
438+
create table atacc2 (test2 int);
439+
create table atacc3 (test3 int) inherits (atacc1, atacc2);
440+
alter table only atacc2 add constraint foo check (test2>0);
441+
-- fail and then succeed on atacc2
442+
insert into atacc2 (test2) values (-3);
443+
ERROR: ExecAppend: rejected due to CHECK constraint foo
444+
insert into atacc2 (test2) values (3);
445+
-- both succeed on atacc3
446+
insert into atacc3 (test2) values (-3);
447+
insert into atacc3 (test2) values (3);
448+
drop table atacc3;
449+
drop table atacc2;
450+
drop table atacc1;

‎src/test/regress/sql/alter_table.sql

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,3 +254,78 @@ ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
254254
references pktable(ptest1, ptest2);
255255

256256
-- temp tables should go away by themselves, need not drop them.
257+
258+
-- test check constraint adding
259+
260+
create table atacc1 ( test int );
261+
-- add a check constraint
262+
alter table atacc1 add constraint atacc_test1 check (test>3);
263+
-- should fail
264+
insert into atacc1 (test) values (2);
265+
-- should succeed
266+
insert into atacc1 (test) values (4);
267+
drop table atacc1;
268+
269+
-- let's do one where the check fails when added
270+
create table atacc1 ( test int );
271+
-- insert a soon to be failing row
272+
insert into atacc1 (test) values (2);
273+
-- add a check constraint (fails)
274+
alter table atacc1 add constraint atacc_test1 check (test>3);
275+
insert into atacc1 (test) values (4);
276+
drop table atacc1;
277+
278+
-- let's do one where the check fails because the column doesn't exist
279+
create table atacc1 ( test int );
280+
-- add a check constraint (fails)
281+
alter table atacc1 add constraint atacc_test1 check (test1>3);
282+
drop table atacc1;
283+
284+
-- something a little more complicated
285+
create table atacc1 ( test int, test2 int, test3 int);
286+
-- add a check constraint (fails)
287+
alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
288+
-- should fail
289+
insert into atacc1 (test,test2,test3) values (4,4,2);
290+
-- should succeed
291+
insert into atacc1 (test,test2,test3) values (4,4,5);
292+
drop table atacc1;
293+
294+
-- lets do some naming tests
295+
create table atacc1 (test int check (test>3), test2 int);
296+
alter table atacc1 add check (test2>test);
297+
-- should fail for $2
298+
insert into atacc1 (test2, test) values (3, 4);
299+
drop table atacc1;
300+
301+
-- inheritance related tests
302+
create table atacc1 (test int);
303+
create table atacc2 (test2 int);
304+
create table atacc3 (test3 int) inherits (atacc1, atacc2);
305+
alter table atacc2 add constraint foo check (test2>0);
306+
-- fail and then succeed on atacc2
307+
insert into atacc2 (test2) values (-3);
308+
insert into atacc2 (test2) values (3);
309+
-- fail and then succeed on atacc3
310+
insert into atacc3 (test2) values (-3);
311+
insert into atacc3 (test2) values (3);
312+
drop table atacc3;
313+
drop table atacc2;
314+
drop table atacc1;
315+
316+
-- let's try only to add only to the parent
317+
318+
create table atacc1 (test int);
319+
create table atacc2 (test2 int);
320+
create table atacc3 (test3 int) inherits (atacc1, atacc2);
321+
alter table only atacc2 add constraint foo check (test2>0);
322+
-- fail and then succeed on atacc2
323+
insert into atacc2 (test2) values (-3);
324+
insert into atacc2 (test2) values (3);
325+
-- both succeed on atacc3
326+
insert into atacc3 (test2) values (-3);
327+
insert into atacc3 (test2) values (3);
328+
drop table atacc3;
329+
drop table atacc2;
330+
drop table atacc1;
331+

0 commit comments

Comments
 (0)