Skip to content

Commit 3da13a6

Browse files
alvherrealvherre
committed
Add test for inherited CHECK constraint drop
This code is insufficiently covered by tests, so add a few small test cases to immortalize its behavior before it gets rewritten completely by the project to catalog NOT NULL constraints.
1 parent b0bea38 commit 3da13a6

File tree

2 files changed

+148
-0
lines changed

2 files changed

+148
-0
lines changed

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

+109
Original file line numberDiff line numberDiff line change
@@ -1283,6 +1283,115 @@ order by 1, 2;
12831283

12841284
drop table p1 cascade;
12851285
NOTICE: drop cascades to table p1_c1
1286+
--
1287+
-- Test DROP behavior of multiply-defined CHECK constraints
1288+
--
1289+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1290+
create table p1_c1 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1291+
NOTICE: merging column "f1" with inherited definition
1292+
NOTICE: merging constraint "f1_pos" with inherited definition
1293+
alter table p1_c1 drop constraint f1_pos;
1294+
ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1"
1295+
alter table p1 drop constraint f1_pos;
1296+
\d p1_c1
1297+
Table "public.p1_c1"
1298+
Column | Type | Collation | Nullable | Default
1299+
--------+---------+-----------+----------+---------
1300+
f1 | integer | | |
1301+
Check constraints:
1302+
"f1_pos" CHECK (f1 > 0)
1303+
Inherits: p1
1304+
1305+
drop table p1 cascade;
1306+
NOTICE: drop cascades to table p1_c1
1307+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1308+
create table p2(f1 int constraint f1_pos CHECK (f1 > 0));
1309+
create table p1p2_c1 (f1 int) inherits (p1, p2);
1310+
NOTICE: merging multiple inherited definitions of column "f1"
1311+
NOTICE: merging column "f1" with inherited definition
1312+
create table p1p2_c2 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1, p2);
1313+
NOTICE: merging multiple inherited definitions of column "f1"
1314+
NOTICE: merging column "f1" with inherited definition
1315+
NOTICE: merging constraint "f1_pos" with inherited definition
1316+
alter table p2 drop constraint f1_pos;
1317+
alter table p1 drop constraint f1_pos;
1318+
\d p1p2_c*
1319+
Table "public.p1p2_c1"
1320+
Column | Type | Collation | Nullable | Default
1321+
--------+---------+-----------+----------+---------
1322+
f1 | integer | | |
1323+
Inherits: p1,
1324+
p2
1325+
1326+
Table "public.p1p2_c2"
1327+
Column | Type | Collation | Nullable | Default
1328+
--------+---------+-----------+----------+---------
1329+
f1 | integer | | |
1330+
Check constraints:
1331+
"f1_pos" CHECK (f1 > 0)
1332+
Inherits: p1,
1333+
p2
1334+
1335+
drop table p1, p2 cascade;
1336+
NOTICE: drop cascades to 2 other objects
1337+
DETAIL: drop cascades to table p1p2_c1
1338+
drop cascades to table p1p2_c2
1339+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1340+
create table p1_c1() inherits (p1);
1341+
create table p1_c2() inherits (p1);
1342+
create table p1_c1c2() inherits (p1_c1, p1_c2);
1343+
NOTICE: merging multiple inherited definitions of column "f1"
1344+
\d p1_c1c2
1345+
Table "public.p1_c1c2"
1346+
Column | Type | Collation | Nullable | Default
1347+
--------+---------+-----------+----------+---------
1348+
f1 | integer | | |
1349+
Check constraints:
1350+
"f1_pos" CHECK (f1 > 0)
1351+
Inherits: p1_c1,
1352+
p1_c2
1353+
1354+
alter table p1 drop constraint f1_pos;
1355+
\d p1_c1c2
1356+
Table "public.p1_c1c2"
1357+
Column | Type | Collation | Nullable | Default
1358+
--------+---------+-----------+----------+---------
1359+
f1 | integer | | |
1360+
Inherits: p1_c1,
1361+
p1_c2
1362+
1363+
drop table p1 cascade;
1364+
NOTICE: drop cascades to 3 other objects
1365+
DETAIL: drop cascades to table p1_c1
1366+
drop cascades to table p1_c2
1367+
drop cascades to table p1_c1c2
1368+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1369+
create table p1_c1() inherits (p1);
1370+
create table p1_c2(constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1371+
NOTICE: merging constraint "f1_pos" with inherited definition
1372+
create table p1_c1c2() inherits (p1_c1, p1_c2, p1);
1373+
NOTICE: merging multiple inherited definitions of column "f1"
1374+
NOTICE: merging multiple inherited definitions of column "f1"
1375+
alter table p1_c2 drop constraint f1_pos;
1376+
ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c2"
1377+
alter table p1 drop constraint f1_pos;
1378+
alter table p1_c1c2 drop constraint f1_pos;
1379+
ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1c2"
1380+
alter table p1_c2 drop constraint f1_pos;
1381+
\d p1_c1c2
1382+
Table "public.p1_c1c2"
1383+
Column | Type | Collation | Nullable | Default
1384+
--------+---------+-----------+----------+---------
1385+
f1 | integer | | |
1386+
Inherits: p1_c1,
1387+
p1_c2,
1388+
p1
1389+
1390+
drop table p1 cascade;
1391+
NOTICE: drop cascades to 3 other objects
1392+
DETAIL: drop cascades to table p1_c1
1393+
drop cascades to table p1_c2
1394+
drop cascades to table p1_c1c2
12861395
-- Test that a valid child can have not-valid parent, but not vice versa
12871396
create table invalid_check_con(f1 int);
12881397
create table invalid_check_con_child() inherits(invalid_check_con);

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

+39
Original file line numberDiff line numberDiff line change
@@ -443,6 +443,45 @@ order by 1, 2;
443443

444444
drop table p1 cascade;
445445

446+
--
447+
-- Test DROP behavior of multiply-defined CHECK constraints
448+
--
449+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
450+
create table p1_c1 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1);
451+
alter table p1_c1 drop constraint f1_pos;
452+
alter table p1 drop constraint f1_pos;
453+
\d p1_c1
454+
drop table p1 cascade;
455+
456+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
457+
create table p2(f1 int constraint f1_pos CHECK (f1 > 0));
458+
create table p1p2_c1 (f1 int) inherits (p1, p2);
459+
create table p1p2_c2 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1, p2);
460+
alter table p2 drop constraint f1_pos;
461+
alter table p1 drop constraint f1_pos;
462+
\d p1p2_c*
463+
drop table p1, p2 cascade;
464+
465+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
466+
create table p1_c1() inherits (p1);
467+
create table p1_c2() inherits (p1);
468+
create table p1_c1c2() inherits (p1_c1, p1_c2);
469+
\d p1_c1c2
470+
alter table p1 drop constraint f1_pos;
471+
\d p1_c1c2
472+
drop table p1 cascade;
473+
474+
create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
475+
create table p1_c1() inherits (p1);
476+
create table p1_c2(constraint f1_pos CHECK (f1 > 0)) inherits (p1);
477+
create table p1_c1c2() inherits (p1_c1, p1_c2, p1);
478+
alter table p1_c2 drop constraint f1_pos;
479+
alter table p1 drop constraint f1_pos;
480+
alter table p1_c1c2 drop constraint f1_pos;
481+
alter table p1_c2 drop constraint f1_pos;
482+
\d p1_c1c2
483+
drop table p1 cascade;
484+
446485
-- Test that a valid child can have not-valid parent, but not vice versa
447486
create table invalid_check_con(f1 int);
448487
create table invalid_check_con_child() inherits(invalid_check_con);

0 commit comments

Comments
 (0)