Subject: Re: cataloguing NOT NULL constraints

From: Peter Eisentraut <[email-masked]>
Date: Wed, 29 Mar 2023 16:46:16 +0200

On 27.03.23 15:55, Peter Eisentraut wrote:
> The information schema should be updated.  I think the following views:
> 
> - CHECK_CONSTRAINTS
> - CONSTRAINT_COLUMN_USAGE
> - DOMAIN_CONSTRAINTS
> - TABLE_CONSTRAINTS
> 
> It looks like these have no test coverage; maybe that could be addressed 
> at the same time.

Here are patches for this.  I haven't included the expected files for 
the tests; this should be checked again that output is correct or the 
changes introduced by this patch set are as expected.

The reason we didn't have tests for this before was probably in part 
because the information schema made up names for not-null constraints 
involving OIDs, so the test wouldn't have been stable.

Feel free to integrate this, or we can add it on afterwards.
From 8cb5f81176e06dded88d49179debddab992ff1ce Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Wed, 29 Mar 2023 16:42:16 +0200
Subject: [PATCH 1/2] Add tests for information schema constraints views

---
 src/test/regress/sql/constraints.sql | 16 ++++++++++++++++
 src/test/regress/sql/domain.sql      | 24 ++++++++++++++++++++++++
 2 files changed, 40 insertions(+)

diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index ae427d25e9..0c8f681b51 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -589,6 +589,22 @@ CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL));
 ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk;
 \d notnull_tbl3
 
+--
+-- Information schema
+--
+
+SELECT * FROM information_schema.check_constraints
+  WHERE constraint_schema = 'public'
+  ORDER BY constraint_name;
+
+SELECT * FROM information_schema.constraint_column_usage
+  WHERE constraint_schema = 'public'
+  ORDER BY table_name, column_name, constraint_name;
+
+SELECT * FROM information_schema.table_constraints
+  WHERE constraint_schema = 'public'
+  ORDER BY constraint_name;
+
 -- Comments
 -- Setup a low-level role to enforce non-superuser checks.
 CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 75703940f9..3096b377eb 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -812,3 +812,27 @@ CREATE TABLE thethings (stuff things);
 alter domain testdomain1 rename constraint unsigned to unsigned_foo;
 alter domain testdomain1 drop constraint unsigned_foo;
 drop domain testdomain1;
+
+
+--
+-- Information schema
+--
+
+SELECT * FROM information_schema.column_domain_usage
+  WHERE domain_schema = 'public' AND table_schema = 'public'
+  ORDER BY domain_name;
+
+SELECT * FROM information_schema.domain_constraints
+  WHERE domain_schema = 'public'
+  ORDER BY constraint_name;
+
+SELECT * FROM information_schema.domains
+  WHERE domain_schema = 'public'
+  ORDER BY domain_name;
+
+SELECT * FROM information_schema.check_constraints
+  WHERE (constraint_schema, constraint_name)
+        IN (SELECT constraint_schema, constraint_name
+            FROM information_schema.domain_constraints
+            WHERE domain_schema = 'public')
+  ORDER BY constraint_name;
-- 
2.40.0

From 093905fb9bddd073b93128893ecceae5da6801d5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Wed, 29 Mar 2023 16:42:37 +0200
Subject: [PATCH 2/2] Update information schema for catalogued not-null
 constraints

---
 src/backend/catalog/information_schema.sql | 57 ++--------------------
 1 file changed, 5 insertions(+), 52 deletions(-)

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 0555e9bc03..414fd0c6ba 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -435,31 +435,15 @@ CREATE VIEW check_constraints AS
     SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
            CAST(rs.nspname AS sql_identifier) AS constraint_schema,
            CAST(con.conname AS sql_identifier) AS constraint_name,
-           CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
+           CAST(CASE con.contype WHEN 'c' THEN left(substring(pg_get_constraintdef(con.oid) from 8), -1)
+                                 WHEN 'n' THEN substring(pg_get_constraintdef(con.oid) from 10) || ' IS NOT NULL' END AS character_data)
              AS check_clause
     FROM pg_constraint con
            LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
            LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
            LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
     WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
-      AND con.contype = 'c'
-
-    UNION
-    -- not-null constraints
-
-    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
-           CAST(n.nspname AS sql_identifier) AS constraint_schema,
-           CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
-           CAST(a.attname || ' IS NOT NULL' AS character_data)
-             AS check_clause
-    FROM pg_namespace n, pg_class r, pg_attribute a
-    WHERE n.oid = r.relnamespace
-      AND r.oid = a.attrelid
-      AND a.attnum > 0
-      AND NOT a.attisdropped
-      AND a.attnotnull
-      AND r.relkind IN ('r', 'p')
-      AND pg_has_role(r.relowner, 'USAGE');
+      AND con.contype IN ('c', 'n');
 
 GRANT SELECT ON check_constraints TO PUBLIC;
 
@@ -822,7 +806,7 @@ CREATE VIEW constraint_column_usage AS
             AND d.classid = 'pg_catalog.pg_constraint'::regclass
             AND d.objid = c.oid
             AND c.connamespace = nc.oid
-            AND c.contype = 'c'
+            AND c.contype IN ('c', 'n')
             AND r.relkind IN ('r', 'p')
             AND NOT a.attisdropped
 
@@ -1832,6 +1816,7 @@ CREATE VIEW table_constraints AS
            CAST(r.relname AS sql_identifier) AS table_name,
            CAST(
              CASE c.contype WHEN 'c' THEN 'CHECK'
+                            WHEN 'n' THEN 'CHECK'
                             WHEN 'f' THEN 'FOREIGN KEY'
                             WHEN 'p' THEN 'PRIMARY KEY'
                             WHEN 'u' THEN 'UNIQUE' END
@@ -1856,38 +1841,6 @@ CREATE VIEW table_constraints AS
           AND c.contype NOT IN ('t', 'x')  -- ignore nonstandard constraints
           AND r.relkind IN ('r', 'p')
           AND (NOT pg_is_other_temp_schema(nr.oid))
-          AND (pg_has_role(r.relowner, 'USAGE')
-               -- SELECT privilege omitted, per SQL standard
-               OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
-               OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
-
-    UNION ALL
-
-    -- not-null constraints
-
-    SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
-           CAST(nr.nspname AS sql_identifier) AS constraint_schema,
-           CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
-           CAST(current_database() AS sql_identifier) AS table_catalog,
-           CAST(nr.nspname AS sql_identifier) AS table_schema,
-           CAST(r.relname AS sql_identifier) AS table_name,
-           CAST('CHECK' AS character_data) AS constraint_type,
-           CAST('NO' AS yes_or_no) AS is_deferrable,
-           CAST('NO' AS yes_or_no) AS initially_deferred,
-           CAST('YES' AS yes_or_no) AS enforced,
-           CAST(NULL AS yes_or_no) AS nulls_distinct
-
-    FROM pg_namespace nr,
-         pg_class r,
-         pg_attribute a
-
-    WHERE nr.oid = r.relnamespace
-          AND r.oid = a.attrelid
-          AND a.attnotnull
-          AND a.attnum > 0
-          AND NOT a.attisdropped
-          AND r.relkind IN ('r', 'p')
-          AND (NOT pg_is_other_temp_schema(nr.oid))
           AND (pg_has_role(r.relowner, 'USAGE')
                -- SELECT privilege omitted, per SQL standard
                OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
-- 
2.40.0

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 31 Aug 2023 12:02:39 +0200

On 2023-Mar-29, Peter Eisentraut wrote:

> Here are patches for this.  I haven't included the expected files for the
> tests; this should be checked again that output is correct or the changes
> introduced by this patch set are as expected.
> 
> The reason we didn't have tests for this before was probably in part because
> the information schema made up names for not-null constraints involving
> OIDs, so the test wouldn't have been stable.
> 

I'm eyeing patch 0002 here.  I noticed that in view check_constraints it
defines the not-null constraint definition as substrings over the
pg_get_constraintdef() function[q1], so I wondered whether it might be
better to join to pg_attribute instead.  I see two options:

1. add a scalar subselect in the select list for each constraint [q2]
2. add a LEFT JOIN to pg_attribute to the main FROM list [q3]
   ON con.conrelid=att.attrelid AND con.conkey[1] = con.attrelid

With just the regression test tables in place, these forms are all
pretty much the same in execution time.  I then created 20k tables with
6 columns each and NOT NULL constraint on each column[4].  That's not a
huge amount but it's credible for a medium-size database with a bunch of
partitions (it's amazing what passes for a medium-size database these
days).  I was surprised to find out that q3 (~130ms) is three times
faster than q2 (~390ms), which is in turn more than twice faster than
your proposed q1 (~870ms).  So unless you have another reason to prefer
it, I think we should use q3 here.

In constraint_column_usage, you're adding a relkind to the catalog scan
that goes through pg_depend for CHECK constraints.  Here we can rely on
a simple conkey[1] check and a separate UNION ALL arm[q5]; this is also
faster when there are many tables.

The third view definition looks ok.  It's certainly very nice to be able
to delete XXX comments there.

[q1]
SELECT current_database()::information_schema.sql_identifier AS constraint_catalog,
    rs.nspname::information_schema.sql_identifier AS constraint_schema,
    con.conname::information_schema.sql_identifier AS constraint_name,
        CASE con.contype
            WHEN 'c'::"char" THEN "left"(SUBSTRING(pg_get_constraintdef(con.oid) FROM 8), '-1'::integer)
            WHEN 'n'::"char" THEN SUBSTRING(pg_get_constraintdef(con.oid) FROM 10) || ' IS NOT NULL'::text                                             
            ELSE NULL::text
        END::information_schema.character_data AS check_clause
   FROM pg_constraint con
     LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace
     LEFT JOIN pg_class c ON c.oid = con.conrelid
     LEFT JOIN pg_type t ON t.oid = con.contypid                                                                                        
  WHERE pg_has_role(COALESCE(c.relowner, t.typowner), 'USAGE'::text) AND (con.contype = ANY (ARRAY['c'::"char", 'n'::"char"]));

[q2]
SELECT current_database()::information_schema.sql_identifier AS constraint_catalog,
    rs.nspname::information_schema.sql_identifier AS constraint_schema,
    con.conname::information_schema.sql_identifier AS constraint_name,
        CASE con.contype
            WHEN 'c'::"char" THEN "left"(SUBSTRING(pg_get_constraintdef(con.oid) FROM 8), '-1'::integer)
            WHEN 'n'::"char" THEN FORMAT('CHECK (%s IS NOT NULL)',
                                         (SELECT attname FROM pg_attribute WHERE attrelid = conrelid AND attnum = conkey[1]))
            ELSE NULL::text
        END::information_schema.character_data AS check_clause
   FROM pg_constraint con
     LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace
     LEFT JOIN pg_class c ON c.oid = con.conrelid
     LEFT JOIN pg_type t ON t.oid = con.contypid
  WHERE pg_has_role(COALESCE(c.relowner, t.typowner), 'USAGE'::text) AND (con.contype = ANY (ARRAY['c'::"char", 'n'::"char"]));

[q3]
SELECT current_database()::information_schema.sql_identifier AS constraint_catalog,
    rs.nspname::information_schema.sql_identifier AS constraint_schema,
    con.conname::information_schema.sql_identifier AS constraint_name,
        CASE con.contype
            WHEN 'c'::"char" THEN "left"(SUBSTRING(pg_get_constraintdef(con.oid) FROM 8), '-1'::integer)
            WHEN 'n'::"char" THEN FORMAT('CHECK (%s IS NOT NULL)', at.attname)                                                                         
            ELSE NULL::text
        END::information_schema.character_data AS check_clause
   FROM pg_constraint con
     LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace
     LEFT JOIN pg_class c ON c.oid = con.conrelid
     LEFT JOIN pg_type t ON t.oid = con.contypid
     LEFT JOIN pg_attribute at ON (con.conrelid = at.attrelid AND con.conkey[1] = at.attnum)
  WHERE pg_has_role(COALESCE(c.relowner, t.typowner), 'USAGE'::text) AND (con.contype = ANY (ARRAY['c'::"char", 'n'::"char"]));

[4]
do $$ begin for i in 0 .. 20000 loop
 execute format('create table t_%s (a1 int not null, a2 int not null, a3 int not null,
    a4 int not null, a5 int not null, a6 int not null);',
   i);
 if i % 1000 = 0 then commit; end if;
end loop; end $$;

[q5]
SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
            CAST(tblschema AS sql_identifier) AS table_schema,
            CAST(tblname AS sql_identifier) AS table_name,
            CAST(colname AS sql_identifier) AS column_name,
            CAST(current_database() AS sql_identifier) AS constraint_catalog,
            CAST(cstrschema AS sql_identifier) AS constraint_schema,
            CAST(cstrname AS sql_identifier) AS constraint_name

     FROM (
         /* check constraints */
         SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
           FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
           WHERE nr.oid = r.relnamespace
             AND r.oid = a.attrelid
             AND d.refclassid = 'pg_catalog.pg_class'::regclass
             AND d.refobjid = r.oid
             AND d.refobjsubid = a.attnum
             AND d.classid = 'pg_catalog.pg_constraint'::regclass
             AND d.objid = c.oid
             AND c.connamespace = nc.oid
             AND c.contype = 'c'
             AND r.relkind IN ('r', 'p')
             AND NOT a.attisdropped

        UNION ALL

        /* not-null constraints */
        SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
          FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c
          WHERE nr.oid = r.relnamespace
	    AND r.oid = a.attrelid
	    AND r.oid = c.conrelid
	    AND a.attnum = c.conkey[1]
	    AND c.connamespace = nc.oid
	    AND c.contype = 'n'
	    AND r.relkind in ('r', 'p')
	    AND not a.attisdropped

         UNION ALL

         /* unique/primary key/foreign key constraints */
         SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
           FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
                pg_constraint c
           WHERE nr.oid = r.relnamespace
             AND r.oid = a.attrelid
             AND nc.oid = c.connamespace
             AND r.oid = CASE c.contype WHEN 'f' THEN c.confrelid ELSE c.conrelid END
             AND a.attnum = ANY (CASE c.contype WHEN 'f' THEN c.confkey ELSE c.conkey END)
             AND NOT a.attisdropped
             AND c.contype IN ('p', 'u', 'f')
             AND r.relkind IN ('r', 'p')

       ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)

     WHERE pg_has_role(x.tblowner, 'USAGE') ;

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/