Build in some knowledge about foreign-key relationships in the catalogs.

This follows in the spirit of commit dfb75e478, which created primary
key and uniqueness constraints to improve the visibility of constraints
imposed on the system catalogs.  While our catalogs contain many
foreign-key-like relationships, they don't quite follow SQL semantics,
in that the convention for an omitted reference is to write zero not
NULL.  Plus, we have some cases in which there are arrays each of whose
elements is supposed to be an FK reference; SQL has no way to model that.
So we can't create actual foreign key constraints to describe the
situation.  Nonetheless, we can collect and use knowledge about these
relationships.

This patch therefore adds annotations to the catalog header files to
declare foreign-key relationships.  (The BKI_LOOKUP annotations cover
simple cases, but we weren't previously distinguishing which such
columns are allowed to contain zeroes; we also need new markings for
multi-column FK references.)  Then, Catalog.pm and genbki.pl are
taught to collect this information into a table in a new generated
header "system_fk_info.h".  The only user of that at the moment is
a new SQL function pg_get_catalog_foreign_keys(), which exposes the
table to SQL.  The oidjoins regression test is rewritten to use
pg_get_catalog_foreign_keys() to find out which columns to check.
Aside from removing the need for manual maintenance of that test
script, this allows it to cover numerous relationships that were not
checked by the old implementation based on findoidjoins.  (As of this
commit, 217 relationships are checked by the test, versus 181 before.)

Discussion: https://postgr.es/m/3240355.1612129197@sss.pgh.pa.us

2 files changed, 55 insertions, 2 deletions

diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml
index 036a72c81e9..6d3c5be67f8 100644
--- a/doc/src/sgml/bki.sgml
+++ b/doc/src/sgml/bki.sgml
@@ -474,10 +474,15 @@
 
     <listitem>
      <para>
-      In such a column, all entries must use the symbolic format except
-      when writing <literal>0</literal> for InvalidOid.  (If the column is
+      In some catalog columns, it's allowed for entries to be zero instead
+      of a valid reference.  If this is allowed, write
+      <literal>BKI_LOOKUP_OPT</literal> instead
+      of <literal>BKI_LOOKUP</literal>.  Then you can
+      write <literal>0</literal> for an entry.  (If the column is
       declared <type>regproc</type>, you can optionally
       write <literal>-</literal> instead of <literal>0</literal>.)
+      Except for this special case, all entries in
+      a <literal>BKI_LOOKUP</literal> column must be symbolic references.
       <filename>genbki.pl</filename> will warn about unrecognized names.
      </para>
     </listitem>
@@ -554,6 +559,22 @@
     therefore no need for the bootstrap backend to deal with symbolic
     references.
    </para>
+
+   <para>
+    It's desirable to mark OID reference columns
+    with <literal>BKI_LOOKUP</literal> or <literal>BKI_LOOKUP_OPT</literal>
+    even if the catalog has no initial data that requires lookup.  This
+    allows <filename>genbki.pl</filename> to record the foreign key
+    relationships that exist in the system catalogs.  That information is
+    used in the regression tests to check for incorrect entries.  See also
+    the macros <literal>DECLARE_FOREIGN_KEY</literal>,
+    <literal>DECLARE_FOREIGN_KEY_OPT</literal>,
+    <literal>DECLARE_ARRAY_FOREIGN_KEY</literal>,
+    and <literal>DECLARE_ARRAY_FOREIGN_KEY_OPT</literal>, which are
+    used to declare foreign key relationships that are too complex
+    for <literal>BKI_LOOKUP</literal> (typically, multi-column foreign
+    keys).
+   </para>
   </sect2>
 
   <sect2 id="system-catalog-auto-array-types">
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 081f04ce1a9..b7150510aba 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22792,6 +22792,38 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
+         <primary>pg_get_catalog_foreign_keys</primary>
+        </indexterm>
+        <function>pg_get_catalog_foreign_keys</function> ()
+        <returnvalue>setof record</returnvalue>
+        ( <parameter>fktable</parameter> <type>regclass</type>,
+          <parameter>fkcols</parameter> <type>text[]</type>,
+          <parameter>pktable</parameter> <type>regclass</type>,
+          <parameter>pkcols</parameter> <type>text[]</type>,
+          <parameter>is_array</parameter> <type>boolean</type>,
+          <parameter>is_opt</parameter> <type>boolean</type> )
+       </para>
+       <para>
+        Returns a set of records describing the foreign key relationships
+        that exist within the <productname>PostgreSQL</productname> system
+        catalogs.
+        The <parameter>fktable</parameter> column contains the name of the
+        referencing catalog, and the <parameter>fkcols</parameter> column
+        contains the name(s) of the referencing column(s).  Similarly,
+        the <parameter>pktable</parameter> column contains the name of the
+        referenced catalog, and the <parameter>pkcols</parameter> column
+        contains the name(s) of the referenced column(s).
+        If <parameter>is_array</parameter> is true, the last referencing
+        column is an array, each of whose elements should match some entry
+        in the referenced catalog.
+        If <parameter>is_opt</parameter> is true, the referencing column(s)
+        are allowed to contain zeroes instead of a valid reference.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
          <primary>pg_get_constraintdef</primary>
         </indexterm>
         <function>pg_get_constraintdef</function> ( <parameter>constraint</parameter> <type>oid</type> <optional>, <parameter>pretty</parameter> <type>boolean</type> </optional> )