From cb726256f061001909430350bc3800a1c90e9010 Mon Sep 17 00:00:00 2001
From: Joel Jacobson <joel@compiler.org>
Date: Thu, 28 May 2026 17:33:15 +0200
Subject: [PATCH v8 3/3] Add information_schema.view_constraint_usage

Add the information_schema.view_constraint_usage view.  It reports
table constraints that are used by a view definition, including the
key-join proof dependencies recorded for FOR KEY joins.

Report only table constraints on ordinary and partitioned tables owned
by a currently enabled role.  Omit constraint types that are not table
constraints.

Add regression coverage for inner and outer FOR KEY joins, and for
constraint dependencies recorded through a view on the referenced side.

Authors: Joel Jacobson, Andreas Karlsson
---
 doc/src/sgml/information_schema.sgml       |  91 +++++++++++++++++++
 src/backend/catalog/information_schema.sql |  37 ++++++++
 src/test/regress/expected/key_join.out     | 101 +++++++++++++++++++++
 src/test/regress/sql/key_join.sql          |  86 ++++++++++++++++++
 4 files changed, 315 insertions(+)

diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 4be4f1ef1ef..797b8539c8e 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -9360,6 +9360,97 @@ ORDER BY c.ordinal_position;
      </row>
     </tbody>
    </tgroup>
+ </table>
+ </sect1>
+
+ <sect1 id="infoschema-view-constraint-usage">
+  <title><literal>view_constraint_usage</literal></title>
+
+  <para>
+   The view <literal>view_constraint_usage</literal> identifies all
+   table constraints that are used by the query expression of a view
+   (the <command>SELECT</command> statement that defines the view).  For
+   views that contain <literal>FOR KEY</literal> joins, this includes
+   table-constraint proof sources such as referential constraints and the
+   primary-key or unique constraints that prove referenced-side uniqueness.
+   These rows identify the table-constraint dependencies whose removal or
+   alteration would invalidate the stored view definition.  A constraint is
+   only included if the table that contains the constraint is owned by a
+   currently enabled role.
+  </para>
+
+  <table>
+   <title><structname>view_constraint_usage</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>view_catalog</structfield> <type>sql_identifier</type>
+      </para>
+      <para>
+       Name of the database that contains the view (always the current database)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>view_schema</structfield> <type>sql_identifier</type>
+      </para>
+      <para>
+       Name of the schema that contains the view
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>view_name</structfield> <type>sql_identifier</type>
+      </para>
+      <para>
+       Name of the view
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>constraint_catalog</structfield> <type>sql_identifier</type>
+      </para>
+      <para>
+       Name of the database that contains the constraint that is used
+       by the view (always the current database)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>constraint_schema</structfield> <type>sql_identifier</type>
+      </para>
+      <para>
+       Name of the schema that contains the constraint that is used by
+       the view
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>constraint_name</structfield> <type>sql_identifier</type>
+      </para>
+      <para>
+       Name of the constraint that is used by the view
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
   </table>
  </sect1>
 
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 4f0e2492937..eaadab13492 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -2515,6 +2515,43 @@ CREATE VIEW view_column_usage AS
 GRANT SELECT ON view_column_usage TO PUBLIC;
 
 
+/*
+ * VIEW_CONSTRAINT_USAGE
+ */
+
+CREATE VIEW view_constraint_usage AS
+    SELECT DISTINCT
+           CAST(current_database() AS sql_identifier) AS view_catalog,
+           CAST(nv.nspname AS sql_identifier) AS view_schema,
+           CAST(v.relname AS sql_identifier) AS view_name,
+           CAST(current_database() AS sql_identifier) AS constraint_catalog,
+           CAST(nc.nspname AS sql_identifier) AS constraint_schema,
+           CAST(c.conname AS sql_identifier) AS constraint_name
+
+    FROM pg_depend dv
+         JOIN pg_class v ON v.oid = dv.refobjid
+         JOIN pg_namespace nv ON nv.oid = v.relnamespace
+         JOIN pg_depend dc ON dc.objid = dv.objid
+            AND dc.classid = 'pg_catalog.pg_rewrite'::regclass
+            AND dc.refclassid = 'pg_catalog.pg_constraint'::regclass
+            AND dc.refobjsubid = 0
+            AND dc.deptype = 'k'
+         JOIN pg_constraint c ON c.oid = dc.refobjid
+         JOIN pg_namespace nc ON nc.oid = c.connamespace
+         JOIN pg_class r ON r.oid = c.conrelid
+
+    WHERE dv.refclassid = 'pg_catalog.pg_class'::regclass
+          AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
+          AND dv.deptype = 'i'
+          AND v.relkind = 'v'
+          AND c.contypid = 0
+          AND c.contype NOT IN ('t', 'x')
+          AND r.relkind IN ('r', 'p')
+          AND pg_has_role(r.relowner, 'USAGE');
+
+GRANT SELECT ON view_constraint_usage TO PUBLIC;
+
+
 /*
  * 6.79
  * VIEW_PERIOD_USAGE
diff --git a/src/test/regress/expected/key_join.out b/src/test/regress/expected/key_join.out
index 1d5e7668de8..b9c6e64e604 100644
--- a/src/test/regress/expected/key_join.out
+++ b/src/test/regress/expected/key_join.out
@@ -1722,6 +1722,107 @@ DETAIL:  This inner join could filter rows from q. Referencing columns q (c_e_id
 DROP VIEW ne3_right_nested_v;
 DROP TABLE ne3_b, ne3_c, ne3_e, ne3_a;
 DROP TABLE t2_nn, t1_nn;
+--
+-- Test information_schema.view_constraint_usage
+--
+CREATE TABLE vcusage_parent
+(
+    id INTEGER,
+    CONSTRAINT vcusage_parent_pkey PRIMARY KEY (id)
+);
+CREATE TABLE vcusage_child
+(
+    id INTEGER,
+    parent_id INTEGER CONSTRAINT vcusage_child_parent_id_not_null NOT NULL,
+    CONSTRAINT vcusage_child_pkey PRIMARY KEY (id),
+    CONSTRAINT vcusage_child_parent_id_fkey
+        FOREIGN KEY (parent_id) REFERENCES vcusage_parent (id)
+);
+CREATE VIEW vcusage_plain_view AS
+SELECT c.parent_id
+FROM vcusage_child c;
+CREATE VIEW vcusage_key_view AS
+SELECT c.id AS child_id, p.id AS parent_id
+FROM vcusage_child c
+-- accepted
+JOIN vcusage_parent p FOR KEY (id) <- c (parent_id);
+SELECT *
+FROM information_schema.view_constraint_usage
+WHERE view_name LIKE 'vcusage_%'
+ORDER BY view_name, constraint_name;
+ view_catalog | view_schema |    view_name     | constraint_catalog | constraint_schema |         constraint_name          
+--------------+-------------+------------------+--------------------+-------------------+----------------------------------
+ regression   | public      | vcusage_key_view | regression         | public            | vcusage_child_parent_id_fkey
+ regression   | public      | vcusage_key_view | regression         | public            | vcusage_child_parent_id_not_null
+ regression   | public      | vcusage_key_view | regression         | public            | vcusage_parent_pkey
+(3 rows)
+
+CREATE OR REPLACE VIEW vcusage_key_view AS
+SELECT c.id AS child_id, p.id AS parent_id
+FROM vcusage_child c
+-- accepted
+LEFT JOIN vcusage_parent p FOR KEY (id) <- c (parent_id);
+SELECT *
+FROM information_schema.view_constraint_usage
+WHERE view_name LIKE 'vcusage_%'
+ORDER BY view_name, constraint_name;
+ view_catalog | view_schema |    view_name     | constraint_catalog | constraint_schema |       constraint_name        
+--------------+-------------+------------------+--------------------+-------------------+------------------------------
+ regression   | public      | vcusage_key_view | regression         | public            | vcusage_child_parent_id_fkey
+ regression   | public      | vcusage_key_view | regression         | public            | vcusage_parent_pkey
+(2 rows)
+
+CREATE VIEW vcusage_parent_view AS
+SELECT id
+FROM vcusage_parent;
+CREATE VIEW vcusage_key_via_plain AS
+SELECT c.id AS child_id, p.id AS parent_id
+FROM vcusage_parent_view p
+JOIN vcusage_child c FOR KEY (parent_id) -> p (id);
+SELECT d.deptype,
+       d.refclassid::regclass::text AS refclass,
+       count(*) AS ndeps
+FROM pg_depend d
+JOIN pg_rewrite r ON r.oid = d.objid
+JOIN pg_class v ON v.oid = r.ev_class
+WHERE d.classid = 'pg_rewrite'::regclass
+  AND v.relname = 'vcusage_key_via_plain'
+  AND d.refclassid IN ('pg_class'::regclass, 'pg_constraint'::regclass)
+  AND d.deptype IN ('n', 'k')
+GROUP BY d.deptype, d.refclassid
+ORDER BY d.deptype, refclass;
+ deptype |   refclass    | ndeps 
+---------+---------------+-------
+ k       | pg_class      |     3
+ k       | pg_constraint |     3
+ n       | pg_class      |     3
+(3 rows)
+
+SELECT count(*) AS normal_constraint_deps
+FROM pg_depend d
+JOIN pg_rewrite r ON r.oid = d.objid
+JOIN pg_class v ON v.oid = r.ev_class
+WHERE d.classid = 'pg_rewrite'::regclass
+  AND v.relname = 'vcusage_key_via_plain'
+  AND d.refclassid = 'pg_constraint'::regclass
+  AND d.deptype = 'n';
+ normal_constraint_deps 
+------------------------
+                      0
+(1 row)
+
+CREATE OR REPLACE VIEW vcusage_parent_view AS
+SELECT id
+FROM vcusage_parent
+WHERE id > 0;
+ERROR:  key join from referencing relation c to referenced relation p cannot be proven
+DETAIL:  Not every c (parent_id) value can be proven to have a matching p row. Referenced relation p is filtered before this key join.
+ALTER TABLE vcusage_parent ENABLE ROW LEVEL SECURITY;
+ERROR:  key join from referencing relation c to referenced relation p cannot be proven
+DETAIL:  There is no matching foreign key constraint for c (parent_id) referencing p (id).
+DROP VIEW vcusage_key_via_plain, vcusage_parent_view, vcusage_key_view,
+          vcusage_plain_view;
+DROP TABLE vcusage_child, vcusage_parent;
 CREATE TABLE customer_addresses
 (
     customer_id INTEGER NOT NULL,
diff --git a/src/test/regress/sql/key_join.sql b/src/test/regress/sql/key_join.sql
index 15d23ca9d39..ff30565ea37 100644
--- a/src/test/regress/sql/key_join.sql
+++ b/src/test/regress/sql/key_join.sql
@@ -1255,6 +1255,92 @@ DROP TABLE ne3_b, ne3_c, ne3_e, ne3_a;
 
 DROP TABLE t2_nn, t1_nn;
 
+--
+-- Test information_schema.view_constraint_usage
+--
+CREATE TABLE vcusage_parent
+(
+    id INTEGER,
+    CONSTRAINT vcusage_parent_pkey PRIMARY KEY (id)
+);
+
+CREATE TABLE vcusage_child
+(
+    id INTEGER,
+    parent_id INTEGER CONSTRAINT vcusage_child_parent_id_not_null NOT NULL,
+    CONSTRAINT vcusage_child_pkey PRIMARY KEY (id),
+    CONSTRAINT vcusage_child_parent_id_fkey
+        FOREIGN KEY (parent_id) REFERENCES vcusage_parent (id)
+);
+
+CREATE VIEW vcusage_plain_view AS
+SELECT c.parent_id
+FROM vcusage_child c;
+
+CREATE VIEW vcusage_key_view AS
+SELECT c.id AS child_id, p.id AS parent_id
+FROM vcusage_child c
+-- accepted
+JOIN vcusage_parent p FOR KEY (id) <- c (parent_id);
+
+SELECT *
+FROM information_schema.view_constraint_usage
+WHERE view_name LIKE 'vcusage_%'
+ORDER BY view_name, constraint_name;
+
+CREATE OR REPLACE VIEW vcusage_key_view AS
+SELECT c.id AS child_id, p.id AS parent_id
+FROM vcusage_child c
+-- accepted
+LEFT JOIN vcusage_parent p FOR KEY (id) <- c (parent_id);
+
+SELECT *
+FROM information_schema.view_constraint_usage
+WHERE view_name LIKE 'vcusage_%'
+ORDER BY view_name, constraint_name;
+
+CREATE VIEW vcusage_parent_view AS
+SELECT id
+FROM vcusage_parent;
+
+CREATE VIEW vcusage_key_via_plain AS
+SELECT c.id AS child_id, p.id AS parent_id
+FROM vcusage_parent_view p
+JOIN vcusage_child c FOR KEY (parent_id) -> p (id);
+
+SELECT d.deptype,
+       d.refclassid::regclass::text AS refclass,
+       count(*) AS ndeps
+FROM pg_depend d
+JOIN pg_rewrite r ON r.oid = d.objid
+JOIN pg_class v ON v.oid = r.ev_class
+WHERE d.classid = 'pg_rewrite'::regclass
+  AND v.relname = 'vcusage_key_via_plain'
+  AND d.refclassid IN ('pg_class'::regclass, 'pg_constraint'::regclass)
+  AND d.deptype IN ('n', 'k')
+GROUP BY d.deptype, d.refclassid
+ORDER BY d.deptype, refclass;
+
+SELECT count(*) AS normal_constraint_deps
+FROM pg_depend d
+JOIN pg_rewrite r ON r.oid = d.objid
+JOIN pg_class v ON v.oid = r.ev_class
+WHERE d.classid = 'pg_rewrite'::regclass
+  AND v.relname = 'vcusage_key_via_plain'
+  AND d.refclassid = 'pg_constraint'::regclass
+  AND d.deptype = 'n';
+
+CREATE OR REPLACE VIEW vcusage_parent_view AS
+SELECT id
+FROM vcusage_parent
+WHERE id > 0;
+
+ALTER TABLE vcusage_parent ENABLE ROW LEVEL SECURITY;
+
+DROP VIEW vcusage_key_via_plain, vcusage_parent_view, vcusage_key_view,
+          vcusage_plain_view;
+DROP TABLE vcusage_child, vcusage_parent;
+
 CREATE TABLE customer_addresses
 (
     customer_id INTEGER NOT NULL,
-- 
2.52.0

