Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7160c86

Browse files
committed
These patches should fix check constraints not inheriting
when added by alter table add constraint. The first filepatches backend/commands/command.c and the latter is a patchto the alter table regression test.Stephan Szabo
1 parent3f5563d commit7160c86

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/3012:57:36 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.131 2001/05/3013:00:03 momjian Exp $
1212
*
1313
* NOTES
1414
* The PerformAddAttribute() code, like most of the relation
@@ -1215,6 +1215,7 @@ AlterTableAddConstraint(char *relationName,
12151215
Relationrel;
12161216
Node*expr;
12171217
char*name;
1218+
Oidmyrelid;
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+
Oidchildrelid=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+
createtableatacc1 ( testint );
261+
-- add a check constraint
262+
altertable atacc1 addconstraint atacc_test1check (test>3);
263+
-- should fail
264+
insert into atacc1 (test)values (2);
265+
-- should succeed
266+
insert into atacc1 (test)values (4);
267+
droptable atacc1;
268+
269+
-- let's do one where the check fails when added
270+
createtableatacc1 ( testint );
271+
-- insert a soon to be failing row
272+
insert into atacc1 (test)values (2);
273+
-- add a check constraint (fails)
274+
altertable atacc1 addconstraint atacc_test1check (test>3);
275+
insert into atacc1 (test)values (4);
276+
droptable atacc1;
277+
278+
-- let's do one where the check fails because the column doesn't exist
279+
createtableatacc1 ( testint );
280+
-- add a check constraint (fails)
281+
altertable atacc1 addconstraint atacc_test1check (test1>3);
282+
droptable atacc1;
283+
284+
-- something a little more complicated
285+
createtableatacc1 ( testint, test2int, test3int);
286+
-- add a check constraint (fails)
287+
altertable atacc1 addconstraint atacc_test1check (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+
droptable atacc1;
293+
294+
-- lets do some naming tests
295+
createtableatacc1 (testintcheck (test>3), test2int);
296+
altertable atacc1 addcheck (test2>test);
297+
-- should fail for $2
298+
insert into atacc1 (test2, test)values (3,4);
299+
droptable atacc1;
300+
301+
-- inheritance related tests
302+
createtableatacc1 (testint);
303+
createtableatacc2 (test2int);
304+
createtableatacc3 (test3int) inherits (atacc1, atacc2);
305+
altertable atacc2 addconstraint foocheck (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+
droptable atacc3;
313+
droptable atacc2;
314+
droptable atacc1;
315+
316+
-- let's try only to add only to the parent
317+
318+
createtableatacc1 (testint);
319+
createtableatacc2 (test2int);
320+
createtableatacc3 (test3int) inherits (atacc1, atacc2);
321+
altertable only atacc2 addconstraint foocheck (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+
droptable atacc3;
329+
droptable atacc2;
330+
droptable atacc1;
331+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp