Skip to content

Commit d721f20

Browse files
alvherrealvherre
committed
connoinherit may be true only for CHECK constraints
The code was setting it true for other constraints, which is bogus. Doing so caused bogus catalog entries for such constraints, and in particular caused an error to be raised when trying to drop a constraint of types other than CHECK from a table that has children, such as reported in bug #6712. In 9.2, additionally ignore connoinherit=true for other constraint types, to avoid having to force initdb; existing databases might already contain bogus catalog entries. Includes a catversion bump (in HEAD only). Bug report from Miroslav Šulc Analysis from Amit Kapila and Noah Misch; Amit also contributed the patch.
1 parent d7991a1 commit d721f20

File tree

5 files changed

+169
-3
lines changed

5 files changed

+169
-3
lines changed

‎src/backend/catalog/index.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1156,7 +1156,7 @@ index_constraint_create(Relation heapRelation,
11561156
NULL,
11571157
true, /* islocal */
11581158
0, /* inhcount */
1159-
false); /* noinherit */
1159+
true); /* noinherit */
11601160

11611161
/*
11621162
* Register the index as internally dependent on the constraint.

‎src/backend/commands/tablecmds.c

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6039,7 +6039,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
60396039
NULL,
60406040
true, /* islocal */
60416041
0, /* inhcount */
6042-
false); /* isnoinherit */
6042+
true); /* isnoinherit */
60436043

60446044
/*
60456045
* Create the triggers that will enforce the constraint.
@@ -6947,6 +6947,16 @@ ATExecDropConstraint(Relation rel, const char *constrName,
69476947

69486948
is_no_inherit_constraint = con->connoinherit;
69496949

6950+
/*
6951+
* XXX as a special hack, we turn on no-inherit here unconditionally
6952+
* except for CHECK constraints. This is because 9.2 until beta2
6953+
* contained a bug that marked it false for all constraints, even
6954+
* though it was only supported false for CHECK constraints.
6955+
* See bug #6712.
6956+
*/
6957+
if (con->contype != CONSTRAINT_CHECK)
6958+
is_no_inherit_constraint = true;
6959+
69506960
/*
69516961
* Perform the actual constraint deletion
69526962
*/

‎src/backend/commands/trigger.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -459,7 +459,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
459459
NULL,
460460
true, /* islocal */
461461
0, /* inhcount */
462-
false); /* isnoinherit */
462+
true); /* isnoinherit */
463463
}
464464

465465
/*

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

Lines changed: 120 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -970,6 +970,126 @@ DETAIL: drop cascades to table inht2
970970
drop cascades to table inhts
971971
drop cascades to table inht3
972972
drop cascades to table inht4
973+
-- Test non-inheritable indices [UNIQUE, EXCLUDE] contraints
974+
CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
975+
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_constraints_val1_val2_key" for table "test_constraints"
976+
CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
977+
\d+ test_constraints
978+
Table "public.test_constraints"
979+
Column | Type | Modifiers | Storage | Stats target | Description
980+
--------+-------------------+-----------+----------+--------------+-------------
981+
id | integer | | plain | |
982+
val1 | character varying | | extended | |
983+
val2 | integer | | plain | |
984+
Indexes:
985+
"test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
986+
Child tables: test_constraints_inh
987+
Has OIDs: no
988+
989+
ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
990+
\d+ test_constraints
991+
Table "public.test_constraints"
992+
Column | Type | Modifiers | Storage | Stats target | Description
993+
--------+-------------------+-----------+----------+--------------+-------------
994+
id | integer | | plain | |
995+
val1 | character varying | | extended | |
996+
val2 | integer | | plain | |
997+
Child tables: test_constraints_inh
998+
Has OIDs: no
999+
1000+
\d+ test_constraints_inh
1001+
Table "public.test_constraints_inh"
1002+
Column | Type | Modifiers | Storage | Stats target | Description
1003+
--------+-------------------+-----------+----------+--------------+-------------
1004+
id | integer | | plain | |
1005+
val1 | character varying | | extended | |
1006+
val2 | integer | | plain | |
1007+
Inherits: test_constraints
1008+
Has OIDs: no
1009+
1010+
DROP TABLE test_constraints_inh;
1011+
DROP TABLE test_constraints;
1012+
CREATE TABLE circles (
1013+
c circle,
1014+
EXCLUDE USING gist (c WITH &&)
1015+
);
1016+
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "circles_c_excl" for table "circles"
1017+
CREATE TABLE circles_inh () INHERITS (circles);
1018+
\d+ circles
1019+
Table "public.circles"
1020+
Column | Type | Modifiers | Storage | Stats target | Description
1021+
--------+--------+-----------+---------+--------------+-------------
1022+
c | circle | | plain | |
1023+
Indexes:
1024+
"circles_c_excl" EXCLUDE USING gist (c WITH &&)
1025+
Child tables: circles_inh
1026+
Has OIDs: no
1027+
1028+
ALTER TABLE circles DROP CONSTRAINT circles_c_excl;
1029+
\d+ circles
1030+
Table "public.circles"
1031+
Column | Type | Modifiers | Storage | Stats target | Description
1032+
--------+--------+-----------+---------+--------------+-------------
1033+
c | circle | | plain | |
1034+
Child tables: circles_inh
1035+
Has OIDs: no
1036+
1037+
\d+ circles_inh
1038+
Table "public.circles_inh"
1039+
Column | Type | Modifiers | Storage | Stats target | Description
1040+
--------+--------+-----------+---------+--------------+-------------
1041+
c | circle | | plain | |
1042+
Inherits: circles
1043+
Has OIDs: no
1044+
1045+
DROP TABLE circles_inh;
1046+
DROP TABLE circles;
1047+
-- Test non-inheritable foreign key contraints
1048+
CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
1049+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_primary_constraints_pkey" for table "test_primary_constraints"
1050+
CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
1051+
CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
1052+
\d+ test_primary_constraints
1053+
Table "public.test_primary_constraints"
1054+
Column | Type | Modifiers | Storage | Stats target | Description
1055+
--------+---------+-----------+---------+--------------+-------------
1056+
id | integer | not null | plain | |
1057+
Indexes:
1058+
"test_primary_constraints_pkey" PRIMARY KEY, btree (id)
1059+
Referenced by:
1060+
TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1061+
Has OIDs: no
1062+
1063+
\d+ test_foreign_constraints
1064+
Table "public.test_foreign_constraints"
1065+
Column | Type | Modifiers | Storage | Stats target | Description
1066+
--------+---------+-----------+---------+--------------+-------------
1067+
id1 | integer | | plain | |
1068+
Foreign-key constraints:
1069+
"test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1070+
Child tables: test_foreign_constraints_inh
1071+
Has OIDs: no
1072+
1073+
ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
1074+
\d+ test_foreign_constraints
1075+
Table "public.test_foreign_constraints"
1076+
Column | Type | Modifiers | Storage | Stats target | Description
1077+
--------+---------+-----------+---------+--------------+-------------
1078+
id1 | integer | | plain | |
1079+
Child tables: test_foreign_constraints_inh
1080+
Has OIDs: no
1081+
1082+
\d+ test_foreign_constraints_inh
1083+
Table "public.test_foreign_constraints_inh"
1084+
Column | Type | Modifiers | Storage | Stats target | Description
1085+
--------+---------+-----------+---------+--------------+-------------
1086+
id1 | integer | | plain | |
1087+
Inherits: test_foreign_constraints
1088+
Has OIDs: no
1089+
1090+
DROP TABLE test_foreign_constraints_inh;
1091+
DROP TABLE test_foreign_constraints;
1092+
DROP TABLE test_primary_constraints;
9731093
--
9741094
-- Test parameterized append plans for inheritance trees
9751095
--

‎src/test/regress/sql/inherit.sql

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -291,6 +291,42 @@ SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected
291291

292292
DROP TABLE inht1, inhs1 CASCADE;
293293

294+
295+
-- Test non-inheritable indices [UNIQUE, EXCLUDE] contraints
296+
CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
297+
CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
298+
\d+ test_constraints
299+
ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
300+
\d+ test_constraints
301+
\d+ test_constraints_inh
302+
DROP TABLE test_constraints_inh;
303+
DROP TABLE test_constraints;
304+
305+
CREATE TABLE circles (
306+
c circle,
307+
EXCLUDE USING gist (c WITH &&)
308+
);
309+
CREATE TABLE circles_inh () INHERITS (circles);
310+
\d+ circles
311+
ALTER TABLE circles DROP CONSTRAINT circles_c_excl;
312+
\d+ circles
313+
\d+ circles_inh
314+
DROP TABLE circles_inh;
315+
DROP TABLE circles;
316+
317+
-- Test non-inheritable foreign key contraints
318+
CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
319+
CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
320+
CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
321+
\d+ test_primary_constraints
322+
\d+ test_foreign_constraints
323+
ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
324+
\d+ test_foreign_constraints
325+
\d+ test_foreign_constraints_inh
326+
DROP TABLE test_foreign_constraints_inh;
327+
DROP TABLE test_foreign_constraints;
328+
DROP TABLE test_primary_constraints;
329+
294330
--
295331
-- Test parameterized append plans for inheritance trees
296332
--

0 commit comments

Comments
 (0)