Change the rules for inherited CHECK constraints to be essentially the same

as those for inherited columns; that is, it's no longer allowed for a child
table to not have a check constraint matching one that exists on a parent.
This satisfies the principle of least surprise (rows selected from the parent
will always appear to meet its check constraints) and eliminates some
longstanding bogosity in pg_dump, which formerly had to guess about whether
check constraints were really inherited or not.

The implementation involves adding conislocal and coninhcount columns to
pg_constraint (paralleling attislocal and attinhcount in pg_attribute)
and refactoring various ALTER TABLE actions to be more like those for
columns.

Alex Hunsaker, Nikhil Sontakke, Tom Lane

4 files changed, 324 insertions, 24 deletions

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 51d5afa81fd..b5af16c558e 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -378,19 +378,21 @@ drop table atacc2 cascade;
 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
+-- adding only to a parent is disallowed as of 8.4
 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);
+create table atacc2 (test2 int) inherits (atacc1);
+-- fail:
+alter table only atacc1 add constraint foo check (test>0);
+ERROR:  constraint must be added to child tables too
+-- ok:
+alter table only atacc2 add constraint foo check (test>0);
+-- check constraint not there on parent
+insert into atacc1 (test) values (-3);
+insert into atacc1 (test) values (3);
+-- check constraint is there on child
+insert into atacc2 (test) 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;
+insert into atacc2 (test) values (3);
 drop table atacc2;
 drop table atacc1;
 -- test unique constraint adding
@@ -1230,7 +1232,7 @@ alter table p1 add column f2 text;
 NOTICE:  merging definition of column "f2" for child "c1"
 insert into p1 values (1,2,'abc');
 insert into c1 values(11,'xyz',33,0); -- should fail
-ERROR:  new row for relation "c1" violates check constraint "c1_a1_check"
+ERROR:  new row for relation "c1" violates check constraint "p1_a1_check"
 insert into c1 values(11,'xyz',33,22);
 select * from p1;
  f1 | a1 | f2  
@@ -1249,7 +1251,7 @@ select * from p1;
 
 drop table p1 cascade;
 NOTICE:  drop cascades to table c1
-NOTICE:  drop cascades to constraint c1_a1_check on table c1
+NOTICE:  drop cascades to constraint p1_a1_check on table c1
 -- test that operations with a dropped column do not try to reference
 -- its datatype
 create domain mytype as text;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f81776fe804..e3acd03c17d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -692,3 +692,220 @@ drop function p2text(p2);
 drop table c1;
 drop table p2;
 drop table p1;
+CREATE TABLE ac (aa TEXT);
+alter table ac add constraint ac_check check (aa is not null);
+CREATE TABLE bc (bb TEXT) INHERITS (ac);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname  | contype | conislocal | coninhcount |      consrc      
+---------+----------+---------+------------+-------------+------------------
+ ac      | ac_check | c       | t          |           0 | (aa IS NOT NULL)
+ bc      | ac_check | c       | f          |           1 | (aa IS NOT NULL)
+(2 rows)
+
+insert into ac (aa) values (NULL);
+ERROR:  new row for relation "ac" violates check constraint "ac_check"
+insert into bc (aa) values (NULL);
+ERROR:  new row for relation "bc" violates check constraint "ac_check"
+alter table bc drop constraint ac_check;  -- fail, disallowed
+ERROR:  cannot drop inherited constraint "ac_check" of relation "bc"
+alter table ac drop constraint ac_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | consrc 
+---------+---------+---------+------------+-------------+--------
+(0 rows)
+
+-- try the unnamed-constraint case
+alter table ac add check (aa is not null);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname |   conname   | contype | conislocal | coninhcount |      consrc      
+---------+-------------+---------+------------+-------------+------------------
+ ac      | ac_aa_check | c       | t          |           0 | (aa IS NOT NULL)
+ bc      | ac_aa_check | c       | f          |           1 | (aa IS NOT NULL)
+(2 rows)
+
+insert into ac (aa) values (NULL);
+ERROR:  new row for relation "ac" violates check constraint "ac_aa_check"
+insert into bc (aa) values (NULL);
+ERROR:  new row for relation "bc" violates check constraint "ac_aa_check"
+alter table bc drop constraint ac_aa_check;  -- fail, disallowed
+ERROR:  cannot drop inherited constraint "ac_aa_check" of relation "bc"
+alter table ac drop constraint ac_aa_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | consrc 
+---------+---------+---------+------------+-------------+--------
+(0 rows)
+
+alter table ac add constraint ac_check check (aa is not null);
+alter table bc no inherit ac;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname  | contype | conislocal | coninhcount |      consrc      
+---------+----------+---------+------------+-------------+------------------
+ ac      | ac_check | c       | t          |           0 | (aa IS NOT NULL)
+ bc      | ac_check | c       | t          |           0 | (aa IS NOT NULL)
+(2 rows)
+
+alter table bc drop constraint ac_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname  | contype | conislocal | coninhcount |      consrc      
+---------+----------+---------+------------+-------------+------------------
+ ac      | ac_check | c       | t          |           0 | (aa IS NOT NULL)
+(1 row)
+
+alter table ac drop constraint ac_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | consrc 
+---------+---------+---------+------------+-------------+--------
+(0 rows)
+
+drop table bc;
+drop table ac;
+create table ac (a int constraint check_a check (a <> 0));
+create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
+NOTICE:  merging column "a" with inherited definition
+NOTICE:  merging constraint "check_a" with inherited definition
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+ relname | conname | contype | conislocal | coninhcount |  consrc  
+---------+---------+---------+------------+-------------+----------
+ ac      | check_a | c       | t          |           0 | (a <> 0)
+ bc      | check_a | c       | t          |           1 | (a <> 0)
+ bc      | check_b | c       | t          |           0 | (b <> 0)
+(3 rows)
+
+drop table bc;
+drop table ac;
+create table ac (a int constraint check_a check (a <> 0));
+create table bc (b int constraint check_b check (b <> 0));
+create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+ relname | conname | contype | conislocal | coninhcount |  consrc  
+---------+---------+---------+------------+-------------+----------
+ ac      | check_a | c       | t          |           0 | (a <> 0)
+ bc      | check_b | c       | t          |           0 | (b <> 0)
+ cc      | check_a | c       | f          |           1 | (a <> 0)
+ cc      | check_b | c       | f          |           1 | (b <> 0)
+ cc      | check_c | c       | t          |           0 | (c <> 0)
+(5 rows)
+
+alter table cc no inherit bc;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+ relname | conname | contype | conislocal | coninhcount |  consrc  
+---------+---------+---------+------------+-------------+----------
+ ac      | check_a | c       | t          |           0 | (a <> 0)
+ bc      | check_b | c       | t          |           0 | (b <> 0)
+ cc      | check_a | c       | f          |           1 | (a <> 0)
+ cc      | check_b | c       | t          |           0 | (b <> 0)
+ cc      | check_c | c       | t          |           0 | (c <> 0)
+(5 rows)
+
+drop table cc;
+drop table bc;
+drop table ac;
+create table p1(f1 int);
+create table p2(f2 int);
+create table c1(f3 int) inherits(p1,p2);
+insert into c1 values(1,-1,2);
+alter table p2 add constraint cc check (f2>0);  -- fail
+ERROR:  check constraint "cc" is violated by some row
+alter table p2 add check (f2>0);  -- check it without a name, too
+ERROR:  check constraint "p2_f2_check" is violated by some row
+delete from c1;
+insert into c1 values(1,1,2);
+alter table p2 add check (f2>0);
+insert into c1 values(1,-1,2);  -- fail
+ERROR:  new row for relation "c1" violates check constraint "p2_f2_check"
+create table c2(f3 int) inherits(p1,p2);
+\d c2
+      Table "public.c2"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ f1     | integer |          
+ f2     | integer |          
+ f3     | integer |          
+Check constraints:
+    "p2_f2_check" CHECK (f2 > 0)
+Inherits: p1,
+          p2
+
+create table c3 (f4 int) inherits(c1,c2);
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging multiple inherited definitions of column "f2"
+NOTICE:  merging multiple inherited definitions of column "f3"
+\d c3
+      Table "public.c3"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ f1     | integer |          
+ f2     | integer |          
+ f3     | integer |          
+ f4     | integer |          
+Check constraints:
+    "p2_f2_check" CHECK (f2 > 0)
+Inherits: c1,
+          c2
+
+drop table p1 cascade;
+NOTICE:  drop cascades to table c2
+NOTICE:  drop cascades to table c3
+NOTICE:  drop cascades to constraint p2_f2_check on table c3
+NOTICE:  drop cascades to constraint p2_f2_check on table c2
+NOTICE:  drop cascades to table c1
+NOTICE:  drop cascades to constraint p2_f2_check on table c1
+drop table p2 cascade;
+create table pp1 (f1 int);
+create table cc1 (f2 text, f3 int) inherits (pp1);
+alter table pp1 add column a1 int check (a1 > 0);
+\d cc1
+      Table "public.cc1"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ f1     | integer |          
+ f2     | text    |          
+ f3     | integer |          
+ a1     | integer |          
+Check constraints:
+    "pp1_a1_check" CHECK (a1 > 0)
+Inherits: pp1
+
+create table cc2(f4 float) inherits(pp1,cc1);
+NOTICE:  merging multiple inherited definitions of column "f1"
+NOTICE:  merging multiple inherited definitions of column "a1"
+\d cc2
+          Table "public.cc2"
+ Column |       Type       | Modifiers 
+--------+------------------+-----------
+ f1     | integer          |          
+ a1     | integer          |          
+ f2     | text             |          
+ f3     | integer          |          
+ f4     | double precision |          
+Check constraints:
+    "pp1_a1_check" CHECK (a1 > 0)
+Inherits: pp1,
+          cc1
+
+alter table pp1 add column a2 int check (a2 > 0);
+NOTICE:  merging definition of column "a2" for child "cc2"
+NOTICE:  merging constraint "pp1_a2_check" with inherited definition
+\d cc2
+          Table "public.cc2"
+ Column |       Type       | Modifiers 
+--------+------------------+-----------
+ f1     | integer          |          
+ a1     | integer          |          
+ f2     | text             |          
+ f3     | integer          |          
+ f4     | double precision |          
+ a2     | integer          |          
+Check constraints:
+    "pp1_a1_check" CHECK (a1 > 0)
+    "pp1_a2_check" CHECK (a2 > 0)
+Inherits: pp1,
+          cc1
+
+drop table pp1 cascade;
+NOTICE:  drop cascades to table cc2
+NOTICE:  drop cascades to constraint pp1_a1_check on table cc2
+NOTICE:  drop cascades to constraint pp1_a2_check on table cc2
+NOTICE:  drop cascades to table cc1
+NOTICE:  drop cascades to constraint pp1_a1_check on table cc1
+NOTICE:  drop cascades to constraint pp1_a2_check on table cc1
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 81cc70612d5..46aacd1bef8 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -389,19 +389,20 @@ select test2 from atacc2;
 drop table atacc2 cascade;
 drop table atacc1;
 
--- let's try only to add only to the parent
+-- adding only to a parent is disallowed as of 8.4
 
 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;
+create table atacc2 (test2 int) inherits (atacc1);
+-- fail:
+alter table only atacc1 add constraint foo check (test>0);
+-- ok:
+alter table only atacc2 add constraint foo check (test>0);
+-- check constraint not there on parent
+insert into atacc1 (test) values (-3);
+insert into atacc1 (test) values (3);
+-- check constraint is there on child
+insert into atacc2 (test) values (-3);
+insert into atacc2 (test) values (3);
 drop table atacc2;
 drop table atacc1;
 
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index b0499a64928..1730a485756 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -196,3 +196,83 @@ drop function p2text(p2);
 drop table c1;
 drop table p2;
 drop table p1;
+
+CREATE TABLE ac (aa TEXT);
+alter table ac add constraint ac_check check (aa is not null);
+CREATE TABLE bc (bb TEXT) INHERITS (ac);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+
+insert into ac (aa) values (NULL);
+insert into bc (aa) values (NULL);
+
+alter table bc drop constraint ac_check;  -- fail, disallowed
+alter table ac drop constraint ac_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+
+-- try the unnamed-constraint case
+alter table ac add check (aa is not null);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+
+insert into ac (aa) values (NULL);
+insert into bc (aa) values (NULL);
+
+alter table bc drop constraint ac_aa_check;  -- fail, disallowed
+alter table ac drop constraint ac_aa_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+
+alter table ac add constraint ac_check check (aa is not null);
+alter table bc no inherit ac;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+alter table bc drop constraint ac_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+alter table ac drop constraint ac_check;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+
+drop table bc;
+drop table ac;
+
+create table ac (a int constraint check_a check (a <> 0));
+create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2;
+
+drop table bc;
+drop table ac;
+
+create table ac (a int constraint check_a check (a <> 0));
+create table bc (b int constraint check_b check (b <> 0));
+create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+
+alter table cc no inherit bc;
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2;
+
+drop table cc;
+drop table bc;
+drop table ac;
+
+create table p1(f1 int);
+create table p2(f2 int);
+create table c1(f3 int) inherits(p1,p2);
+insert into c1 values(1,-1,2);
+alter table p2 add constraint cc check (f2>0);  -- fail
+alter table p2 add check (f2>0);  -- check it without a name, too
+delete from c1;
+insert into c1 values(1,1,2);
+alter table p2 add check (f2>0);
+insert into c1 values(1,-1,2);  -- fail
+create table c2(f3 int) inherits(p1,p2);
+\d c2
+create table c3 (f4 int) inherits(c1,c2);
+\d c3
+drop table p1 cascade;
+drop table p2 cascade;
+
+create table pp1 (f1 int);
+create table cc1 (f2 text, f3 int) inherits (pp1);
+alter table pp1 add column a1 int check (a1 > 0);
+\d cc1
+create table cc2(f4 float) inherits(pp1,cc1);
+\d cc2
+alter table pp1 add column a2 int check (a2 > 0);
+\d cc2
+drop table pp1 cascade;