From: Kyotaro Horiguchi Date: Mon, 28 Aug 2023 14:38:58 +0900 Subject: [PATCH 1/2] Fix not-null constraint representation in \d+ The recent commit b0e96f3119 added the description about not-null constraints in the output of \d+ command. It hided constraints' origin when it is marked as NO INHERIT. Show their origin irrespective of the NO INHERIT state. --- src/bin/psql/describe.c | 22 +++++++--- src/test/regress/expected/constraints.out | 12 ++--- src/test/regress/expected/create_table.out | 6 +-- .../regress/expected/create_table_like.out | 6 +-- src/test/regress/expected/foreign_data.out | 44 +++++++++---------- src/test/regress/expected/generated.out | 2 +- src/test/regress/expected/inherit.out | 30 ++++++------- 7 files changed, 66 insertions(+), 56 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index bac94a338c..3bf1c0cb97 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3079,16 +3079,26 @@ describeOneTableDetails(const char *schemaname, /* Might be an empty set - that's ok */ for (i = 0; i < tuples; i++) { + bool noinherit = PQgetvalue(result, i, 2)[0] == 't'; bool islocal = PQgetvalue(result, i, 3)[0] == 't'; bool inherited = PQgetvalue(result, i, 4)[0] == 't'; - printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s", + printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"", PQgetvalue(result, i, 0), - PQgetvalue(result, i, 1), - PQgetvalue(result, i, 2)[0] == 't' ? - " NO INHERIT" : - islocal && inherited ? _(" (local, inherited)") : - inherited ? _(" (inherited)") : ""); + PQgetvalue(result, i, 1)); + if (inherited || noinherit) + { + appendPQExpBufferChar(&buf, ':'); + + if (inherited) + { + if (islocal) + appendPQExpBufferStr(&buf, _(" local")); + appendPQExpBufferStr(&buf, _(" inherited")); + } + if (noinherit) + appendPQExpBufferStr(&buf, _(" uninheritable")); + } printTableAddFooter(&cont, buf.data); } diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index b7de50ad6a..d4adbaa518 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -914,7 +914,7 @@ ALTER TABLE cnn_parent ADD PRIMARY KEY (b); a | integer | | | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited) + "cnn_grandchild_b_not_null" NOT NULL "b": local inherited Inherits: cnn_child Child tables: cnn_grandchild2 @@ -925,7 +925,7 @@ Child tables: cnn_grandchild2 a | integer | | | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (inherited) + "cnn_grandchild_b_not_null" NOT NULL "b": inherited Inherits: cnn_grandchild, cnn_child2 @@ -951,7 +951,7 @@ ERROR: multiple primary keys for table "cnn_parent" are not allowed a | integer | | | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited) + "cnn_grandchild_b_not_null" NOT NULL "b": local inherited Inherits: cnn_child Child tables: cnn_grandchild2 @@ -962,7 +962,7 @@ Child tables: cnn_grandchild2 a | integer | | | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (inherited) + "cnn_grandchild_b_not_null" NOT NULL "b": inherited Inherits: cnn_grandchild, cnn_child2 @@ -988,7 +988,7 @@ ALTER TABLE cnn_parent ADD PRIMARY KEY USING INDEX b_uq; a | integer | | | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (local, inherited) + "cnn_grandchild_b_not_null" NOT NULL "b": local inherited Inherits: cnn_child Child tables: cnn_grandchild2 @@ -999,7 +999,7 @@ Child tables: cnn_grandchild2 a | integer | | | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "cnn_grandchild_b_not_null" NOT NULL "b" (inherited) + "cnn_grandchild_b_not_null" NOT NULL "b": inherited Inherits: cnn_grandchild, cnn_child2 diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 344d05233a..293e4292e9 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -855,7 +855,7 @@ drop table test_part_coll_posix; Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) Not-null constraints: - "part_b_b_not_null" NOT NULL "b" (local, inherited) + "part_b_b_not_null" NOT NULL "b": local inherited -- Both partition bound and partition key in describe output \d+ part_c @@ -868,7 +868,7 @@ Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) Not-null constraints: - "part_c_b_not_null" NOT NULL "b" (local, inherited) + "part_c_b_not_null" NOT NULL "b": local inherited Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) -- a level-2 partition's constraint will include the parent's expressions @@ -881,7 +881,7 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) Not-null constraints: - "part_c_b_not_null" NOT NULL "b" (inherited) + "part_c_b_not_null" NOT NULL "b": inherited -- Show partition count in the parent's describe output -- Tempted to include \d+ output listing partitions with bound info but diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 61956773ff..6604d0c7fa 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -360,7 +360,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) Not-null constraints: - "ctlt1_inh_a_not_null" NOT NULL "a" (local, inherited) + "ctlt1_inh_a_not_null" NOT NULL "a": local inherited Inherits: ctlt1 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; @@ -383,7 +383,7 @@ Check constraints: "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) Not-null constraints: - "ctlt13_inh_a_not_null" NOT NULL "a" (inherited) + "ctlt13_inh_a_not_null" NOT NULL "a": inherited Inherits: ctlt1, ctlt3 @@ -403,7 +403,7 @@ Check constraints: "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) Not-null constraints: - "ctlt13_like_a_not_null" NOT NULL "a" (inherited) + "ctlt13_like_a_not_null" NOT NULL "a": inherited Inherits: ctlt1 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 1dfe23cc1e..17cdd7e5b3 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1421,7 +1421,7 @@ Child tables: ft2, FOREIGN c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: - "fd_pt1_c1_not_null" NOT NULL "c1" (inherited) + "fd_pt1_c1_not_null" NOT NULL "c1": inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1474,7 +1474,7 @@ Child tables: ft2, FOREIGN c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1497,7 +1497,7 @@ NOTICE: merging column "c3" with inherited definition c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1512,7 +1512,7 @@ Child tables: ct3, c2 | text | | | | extended | | c3 | date | | | | plain | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (inherited) + "ft2_c1_not_null" NOT NULL "c1": inherited Inherits: ft2 \d+ ft3 @@ -1523,7 +1523,7 @@ Inherits: ft2 c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: - "ft3_c1_not_null" NOT NULL "c1" (local, inherited) + "ft3_c1_not_null" NOT NULL "c1": local inherited Server: s0 Inherits: ft2 @@ -1563,8 +1563,8 @@ Child tables: ft2, FOREIGN c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) - "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited + "fd_pt1_c7_not_null" NOT NULL "c7": inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1584,8 +1584,8 @@ Child tables: ct3, c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (inherited) - "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) + "ft2_c1_not_null" NOT NULL "c1": inherited + "fd_pt1_c7_not_null" NOT NULL "c7": inherited Inherits: ft2 \d+ ft3 @@ -1601,8 +1601,8 @@ Inherits: ft2 c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | Not-null constraints: - "ft3_c1_not_null" NOT NULL "c1" (local, inherited) - "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) + "ft3_c1_not_null" NOT NULL "c1": local inherited + "fd_pt1_c7_not_null" NOT NULL "c7": inherited Server: s0 Inherits: ft2 @@ -1649,8 +1649,8 @@ Child tables: ft2, FOREIGN c7 | integer | | | | | plain | | c8 | text | | | | | external | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) - "fd_pt1_c6_not_null" NOT NULL "c6" (inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited + "fd_pt1_c6_not_null" NOT NULL "c6": inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1682,7 +1682,7 @@ Child tables: ft2, FOREIGN c2 | text | | | | | extended | | c3 | date | | | | | plain | | Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1729,7 +1729,7 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1780,7 +1780,7 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1815,7 +1815,7 @@ Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1846,7 +1846,7 @@ Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) Not-null constraints: - "ft2_c1_not_null" NOT NULL "c1" (local, inherited) + "ft2_c1_not_null" NOT NULL "c1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1881,7 +1881,7 @@ Check constraints: "f2_check" CHECK (f2 <> ''::text) "fd_pt1chk2" CHECK (f2 <> ''::text) Not-null constraints: - "ft2_c1_not_null" NOT NULL "f1" (local, inherited) + "ft2_c1_not_null" NOT NULL "f1": local inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1942,7 +1942,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Not-null constraints: - "fd_pt2_c1_not_null" NOT NULL "c1" (inherited) + "fd_pt2_c1_not_null" NOT NULL "c1": inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2024,7 +2024,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) + "fd_pt2_1_c1_not_null" NOT NULL "c1": inherited Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2058,7 +2058,7 @@ Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Check constraints: "p21chk" CHECK (c2 <> ''::text) Not-null constraints: - "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) + "fd_pt2_1_c1_not_null" NOT NULL "c1": inherited "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index dc97ed3fe0..4a1744f129 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -316,7 +316,7 @@ NOTICE: merging column "b" with inherited definition b | integer | | | generated always as (a * 22) stored | plain | | x | integer | | | | plain | | Not-null constraints: - "gtestx_a_not_null" NOT NULL "a" (inherited) + "gtestx_a_not_null" NOT NULL "a": inherited Inherits: gtest1 CREATE TABLE gtestxx_1 (a int NOT NULL, b int); diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 6daca12340..c80e398f71 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -2007,7 +2007,7 @@ Child tables: cc2 f4 | double precision | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: - "nn" NOT NULL "a2" (inherited) + "nn" NOT NULL "a2": inherited Inherits: pp1, cc1 @@ -2031,7 +2031,7 @@ Child tables: cc1, f3 | integer | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) + "pp1_f1_not_null" NOT NULL "f1": inherited "nn" NOT NULL "a2" Inherits: pp1 Child tables: cc2 @@ -2046,8 +2046,8 @@ Child tables: cc2 f4 | double precision | | | | plain | | a2 | integer | | not null | | plain | | Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) - "nn" NOT NULL "a2" (inherited) + "pp1_f1_not_null" NOT NULL "f1": inherited + "nn" NOT NULL "a2": inherited Inherits: pp1, cc1 @@ -2065,7 +2065,7 @@ alter table cc1 alter column a2 drop not null; f3 | integer | | | | plain | | a2 | integer | | | | plain | | Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) + "pp1_f1_not_null" NOT NULL "f1": inherited Inherits: pp1 Child tables: cc2 @@ -2082,7 +2082,7 @@ ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2" f4 | double precision | | | | plain | | a2 | integer | | | | plain | | Not-null constraints: - "pp1_f1_not_null" NOT NULL "f1" (inherited) + "pp1_f1_not_null" NOT NULL "f1": inherited Inherits: pp1, cc1 @@ -2112,8 +2112,8 @@ create table inh_child () inherits (inh_parent1, inh_parent2); a | integer | | not null | | plain | | b | integer | | not null | | plain | | Not-null constraints: - "nn" NOT NULL "a" (inherited) - "inh_child_b_not_null" NOT NULL "b" (inherited) + "nn" NOT NULL "a": inherited + "inh_child_b_not_null" NOT NULL "b": inherited Inherits: inh_parent1, inh_parent2 @@ -2147,9 +2147,9 @@ select conrelid::regclass, conname, contype, conkey, d | integer | | not null | | plain | | e | integer | | | | plain | | Not-null constraints: - "inh_child_a_not_null" NOT NULL "a" (inherited) - "inh_child_b_not_null" NOT NULL "b" (inherited) - "inh_child_d_not_null" NOT NULL "d" (inherited) + "inh_child_a_not_null" NOT NULL "a": inherited + "inh_child_b_not_null" NOT NULL "b": inherited + "inh_child_d_not_null" NOT NULL "d": inherited Inherits: inh_parent1, inh_parent2 @@ -2188,7 +2188,7 @@ Inherits: inh_nn_parent --------+---------+-----------+----------+---------+---------+--------------+------------- a | integer | | not null | | plain | | Not-null constraints: - "inh_nn_parent_a_not_null" NOT NULL "a" NO INHERIT + "inh_nn_parent_a_not_null" NOT NULL "a": uninheritable Child tables: inh_nn_child, inh_nn_child2 @@ -2223,7 +2223,7 @@ Child tables: inh_child1 --------+---------+-----------+----------+---------+---------+--------------+------------- f1 | integer | | not null | | plain | | Not-null constraints: - "inh_child1_f1_not_null" NOT NULL "f1" (local, inherited) + "inh_child1_f1_not_null" NOT NULL "f1": local inherited Inherits: inh_parent Child tables: inh_child2 @@ -2233,7 +2233,7 @@ Child tables: inh_child2 --------+---------+-----------+----------+---------+---------+--------------+------------- f1 | integer | | not null | | plain | | Not-null constraints: - "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited) + "inh_child2_f1_not_null" NOT NULL "f1": local inherited Inherits: inh_child1 select conrelid::regclass, conname, contype, coninhcount, conislocal @@ -2277,7 +2277,7 @@ Child tables: inh_child2, --------+---------+-----------+----------+---------+---------+--------------+------------- f1 | integer | | not null | | plain | | Not-null constraints: - "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited) + "inh_child2_f1_not_null" NOT NULL "f1": local inherited Inherits: inh_child1 select conrelid::regclass, conname, contype, coninhcount, conislocal -- 2.39.3 ----Next_Part(Mon_Aug_28_16_16_58_2023_869)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Add-tests-for-d-not-null-constraints.patch"