Recast "ONLY" column CHECK constraints as NO INHERIT

The original syntax wasn't universally loved, and it didn't allow its
usage in CREATE TABLE, only ALTER TABLE.  It now works everywhere, and
it also allows using ALTER TABLE ONLY to add an uninherited CHECK
constraint, per discussion.

The pg_constraint column has accordingly been renamed connoinherit.

This commit partly reverts some of the changes in
61d81bd28dbec65a6b144e0cd3d0bfe25913c3ac, particularly some pg_dump and
psql bits, because now pg_get_constraintdef includes the necessary NO
INHERIT within the constraint definition.

Author: Nikhil Sontakke
Some tweaks by me

6 files changed, 80 insertions, 19 deletions

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index eba0493089f..890a51fd9e8 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -235,7 +235,7 @@ Check constraints:
     "con1foo" CHECK (a > 0)
 Inherits: constraint_rename_test
 
-ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);
+ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK NO INHERIT (b > 0);
 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
 \d constraint_rename_test
@@ -246,8 +246,8 @@ Table "public.constraint_rename_test"
  b      | integer | 
  c      | integer | 
 Check constraints:
-    "con2bar" (ONLY) CHECK (b > 0)
     "con1foo" CHECK (a > 0)
+    "con2bar" CHECK NO INHERIT (b > 0)
 Number of child tables: 1 (Use \d+ to list them.)
 
 \d constraint_rename_test2
@@ -275,8 +275,8 @@ Table "public.constraint_rename_test"
 Indexes:
     "con3foo" PRIMARY KEY, btree (a)
 Check constraints:
-    "con2bar" (ONLY) CHECK (b > 0)
     "con1foo" CHECK (a > 0)
+    "con2bar" CHECK NO INHERIT (b > 0)
 Number of child tables: 1 (Use \d+ to list them.)
 
 \d constraint_rename_test2
@@ -643,7 +643,7 @@ drop table atacc1;
 create table atacc1 (test int);
 create table atacc2 (test2 int) inherits (atacc1);
 -- ok:
-alter table only atacc1 add constraint foo check (test>0);
+alter table atacc1 add constraint foo check no inherit (test>0);
 -- check constraint is not there on child
 insert into atacc2 (test) values (-3);
 -- check constraint is there on parent
@@ -652,7 +652,7 @@ ERROR:  new row for relation "atacc1" violates check constraint "foo"
 DETAIL:  Failing row contains (-3).
 insert into atacc1 (test) values (3);
 -- fail, violating row:
-alter table only atacc2 add constraint foo check (test>0);
+alter table atacc2 add constraint foo check no inherit (test>0);
 ERROR:  check constraint "foo" is violated by some row
 drop table atacc2;
 drop table atacc1;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 92a64c8dba2..6613fea84ac 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -598,17 +598,17 @@ select * from d;
 
 -- Test non-inheritable parent constraints
 create table p1(ff1 int);
-alter table only p1 add constraint p1chk check (ff1 > 0);
+alter table p1 add constraint p1chk check no inherit (ff1 > 0);
 alter table p1 add constraint p2chk check (ff1 > 10);
--- conisonly should be true for ONLY constraint
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.conisonly from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2;
- relname | conname | contype | conislocal | coninhcount | conisonly 
----------+---------+---------+------------+-------------+-----------
+-- connoinherit should be true for NO INHERIT constraint
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | connoinherit 
+---------+---------+---------+------------+-------------+--------------
  p1      | p1chk   | c       | t          |           0 | t
  p1      | p2chk   | c       | t          |           0 | f
 (2 rows)
 
--- Test that child does not inherit ONLY constraints
+-- Test that child does not inherit NO INHERIT constraints
 create table c1 () inherits (p1);
 \d p1
       Table "public.p1"
@@ -616,7 +616,7 @@ create table c1 () inherits (p1);
 --------+---------+-----------
  ff1    | integer | 
 Check constraints:
-    "p1chk" (ONLY) CHECK (ff1 > 0)
+    "p1chk" CHECK NO INHERIT (ff1 > 0)
     "p2chk" CHECK (ff1 > 10)
 Number of child tables: 1 (Use \d+ to list them.)
 
diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source
index b84d51e9e52..37d06b0127a 100644
--- a/src/test/regress/input/constraints.source
+++ b/src/test/regress/input/constraints.source
@@ -144,6 +144,34 @@ SELECT * FROM INSERT_CHILD;
 DROP TABLE INSERT_CHILD;
 
 --
+-- Check NO INHERIT type of constraints and inheritance
+--
+
+CREATE TABLE ATACC1 (TEST INT
+	CHECK NO INHERIT (TEST > 0));
+
+CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);
+-- check constraint is not there on child
+INSERT INTO ATACC2 (TEST) VALUES (-3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST) VALUES (-3);
+DROP TABLE ATACC1 CASCADE;
+
+CREATE TABLE ATACC1 (TEST INT, TEST2 INT
+	CHECK (TEST > 0), CHECK NO INHERIT (TEST2 > 10));
+
+CREATE TABLE ATACC2 () INHERITS (ATACC1);
+-- check constraint is there on child
+INSERT INTO ATACC2 (TEST) VALUES (-3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST) VALUES (-3);
+-- check constraint is not there on child
+INSERT INTO ATACC2 (TEST2) VALUES (3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST2) VALUES (3);
+DROP TABLE ATACC1 CASCADE;
+
+--
 -- Check constraints on INSERT INTO
 --
 
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source
index 3e02e8dbd44..f37c9b1369b 100644
--- a/src/test/regress/output/constraints.source
+++ b/src/test/regress/output/constraints.source
@@ -228,6 +228,39 @@ SELECT * FROM INSERT_CHILD;
 
 DROP TABLE INSERT_CHILD;
 --
+-- Check NO INHERIT type of constraints and inheritance
+--
+CREATE TABLE ATACC1 (TEST INT
+	CHECK NO INHERIT (TEST > 0));
+CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);
+-- check constraint is not there on child
+INSERT INTO ATACC2 (TEST) VALUES (-3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST) VALUES (-3);
+ERROR:  new row for relation "atacc1" violates check constraint "atacc1_test_check"
+DETAIL:  Failing row contains (-3).
+DROP TABLE ATACC1 CASCADE;
+NOTICE:  drop cascades to table atacc2
+CREATE TABLE ATACC1 (TEST INT, TEST2 INT
+	CHECK (TEST > 0), CHECK NO INHERIT (TEST2 > 10));
+CREATE TABLE ATACC2 () INHERITS (ATACC1);
+-- check constraint is there on child
+INSERT INTO ATACC2 (TEST) VALUES (-3);
+ERROR:  new row for relation "atacc2" violates check constraint "atacc1_test_check"
+DETAIL:  Failing row contains (-3, null).
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST) VALUES (-3);
+ERROR:  new row for relation "atacc1" violates check constraint "atacc1_test_check"
+DETAIL:  Failing row contains (-3, null).
+-- check constraint is not there on child
+INSERT INTO ATACC2 (TEST2) VALUES (3);
+-- check constraint is there on parent
+INSERT INTO ATACC1 (TEST2) VALUES (3);
+ERROR:  new row for relation "atacc1" violates check constraint "atacc1_test2_check"
+DETAIL:  Failing row contains (null, 3).
+DROP TABLE ATACC1 CASCADE;
+NOTICE:  drop cascades to table atacc2
+--
 -- Check constraints on INSERT INTO
 --
 DELETE FROM INSERT_TBL;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 50c58d23e18..5c03123b4e0 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -218,7 +218,7 @@ ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fa
 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
 \d constraint_rename_test
 \d constraint_rename_test2
-ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);
+ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK NO INHERIT (b > 0);
 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
 \d constraint_rename_test
@@ -500,14 +500,14 @@ drop table atacc1;
 create table atacc1 (test int);
 create table atacc2 (test2 int) inherits (atacc1);
 -- ok:
-alter table only atacc1 add constraint foo check (test>0);
+alter table atacc1 add constraint foo check no inherit (test>0);
 -- check constraint is not there on child
 insert into atacc2 (test) values (-3);
 -- check constraint is there on parent
 insert into atacc1 (test) values (-3);
 insert into atacc1 (test) values (3);
 -- fail, violating row:
-alter table only atacc2 add constraint foo check (test>0);
+alter table atacc2 add constraint foo check no inherit (test>0);
 drop table atacc2;
 drop table atacc1;
 
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 43f910f24a1..6e6921aa9cd 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -140,12 +140,12 @@ select * from d;
 
 -- Test non-inheritable parent constraints
 create table p1(ff1 int);
-alter table only p1 add constraint p1chk check (ff1 > 0);
+alter table p1 add constraint p1chk check no inherit (ff1 > 0);
 alter table p1 add constraint p2chk check (ff1 > 10);
--- conisonly should be true for ONLY constraint
-select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.conisonly from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2;
+-- connoinherit should be true for NO INHERIT constraint
+select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2;
 
--- Test that child does not inherit ONLY constraints
+-- Test that child does not inherit NO INHERIT constraints
 create table c1 () inherits (p1);
 \d p1
 \d c1