Fix pg_dump for inherited validated not-null constraints

When a child constraint is validated and the parent constraint it
derives from isn't, pg_dump must be coerced into printing the child
constraint; failing to do would result in a dump that restores the
constraint as not valid, which would be incorrect.

Co-authored-by: jian he <jian.universality@gmail.com>
Co-authored-by: Álvaro Herrera <alvherre@kurilemu.de>
Reported-by: jian he <jian.universality@gmail.com>
Message-id: https://postgr.es/m/CACJufxGHNNMc0E2JphUqJMzD3=bwRSuAEVBF5ekgkG8uY0Q3hg@mail.gmail.com

2 files changed, 52 insertions, 0 deletions

diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index eba4c0be0d8..ad6aaab7385 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1625,6 +1625,40 @@ EXECUTE get_nnconstraint_info('{notnull_part1_upg, notnull_part1_1_upg, notnull_
  notnull_part1_upg   | notnull_con | f            | t          |           0
 (4 rows)
 
+-- Inheritance test tables for pg_upgrade
+create table constr_parent (a int);
+create table constr_child (a int) inherits (constr_parent);
+NOTICE:  merging column "a" with inherited definition
+alter table constr_parent add not null a not valid;
+alter table constr_child validate constraint constr_parent_a_not_null;
+EXECUTE get_nnconstraint_info('{constr_parent, constr_child}');
+    tabname    |         conname          | convalidated | conislocal | coninhcount 
+---------------+--------------------------+--------------+------------+-------------
+ constr_child  | constr_parent_a_not_null | t            | f          |           1
+ constr_parent | constr_parent_a_not_null | f            | t          |           0
+(2 rows)
+
+create table constr_parent2 (a int);
+create table constr_child2 () inherits (constr_parent2);
+alter table constr_parent2 add not null a not valid;
+alter table constr_child2 validate constraint constr_parent2_a_not_null;
+EXECUTE get_nnconstraint_info('{constr_parent2, constr_child2}');
+    tabname     |          conname          | convalidated | conislocal | coninhcount 
+----------------+---------------------------+--------------+------------+-------------
+ constr_child2  | constr_parent2_a_not_null | t            | f          |           1
+ constr_parent2 | constr_parent2_a_not_null | f            | t          |           0
+(2 rows)
+
+create table constr_parent3 (a int not null);
+create table constr_child3 () inherits (constr_parent2, constr_parent3);
+NOTICE:  merging multiple inherited definitions of column "a"
+EXECUTE get_nnconstraint_info('{constr_parent3, constr_child3}');
+    tabname     |          conname          | convalidated | conislocal | coninhcount 
+----------------+---------------------------+--------------+------------+-------------
+ constr_child3  | constr_parent2_a_not_null | t            | f          |           2
+ constr_parent3 | constr_parent3_a_not_null | t            | t          |           0
+(2 rows)
+
 DEALLOCATE get_nnconstraint_info;
 -- end NOT NULL NOT VALID
 -- Comments
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 5d6d749c150..337baab7ced 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -979,6 +979,24 @@ INSERT INTO notnull_part1_3_upg values(NULL,1);
 ALTER TABLE notnull_part1_3_upg add CONSTRAINT nn3 NOT NULL a NOT VALID;
 ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_3_upg FOR VALUES IN (NULL,5);
 EXECUTE get_nnconstraint_info('{notnull_part1_upg, notnull_part1_1_upg, notnull_part1_2_upg, notnull_part1_3_upg}');
+
+-- Inheritance test tables for pg_upgrade
+create table constr_parent (a int);
+create table constr_child (a int) inherits (constr_parent);
+alter table constr_parent add not null a not valid;
+alter table constr_child validate constraint constr_parent_a_not_null;
+EXECUTE get_nnconstraint_info('{constr_parent, constr_child}');
+
+create table constr_parent2 (a int);
+create table constr_child2 () inherits (constr_parent2);
+alter table constr_parent2 add not null a not valid;
+alter table constr_child2 validate constraint constr_parent2_a_not_null;
+EXECUTE get_nnconstraint_info('{constr_parent2, constr_child2}');
+
+create table constr_parent3 (a int not null);
+create table constr_child3 () inherits (constr_parent2, constr_parent3);
+EXECUTE get_nnconstraint_info('{constr_parent3, constr_child3}');
+
 DEALLOCATE get_nnconstraint_info;
 
 -- end NOT NULL NOT VALID