Replace explicit PIN entries in pg_depend with an OID range test.

As of v14, pg_depend contains almost 7000 "pin" entries recording
the OIDs of built-in objects.  This is a fair amount of bloat for
every database, and it adds time to pg_depend lookups as well as
initdb.  We can get rid of all of those entries in favor of an OID
range check, i.e. "OIDs below FirstUnpinnedObjectId are pinned".

(template1 and the public schema are exceptions.  Those exceptions
are now wired into IsPinnedObject() instead of initdb's code for
filling pg_depend, but it's the same amount of cruft either way.)

The contents of pg_shdepend are modified likewise.

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

2 files changed, 10 insertions, 105 deletions

diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index a67f40198a4..a57fd142a94 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -11,75 +11,26 @@
 -- NB: run this test early, because some later tests create bogus entries.
 -- **************** pg_depend ****************
 -- Look for illegal values in pg_depend fields.
--- classid/objid can be zero, but only in 'p' entries
 SELECT *
 FROM pg_depend as d1
 WHERE refclassid = 0 OR refobjid = 0 OR
-      deptype NOT IN ('a', 'e', 'i', 'n', 'p') OR
-      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
-      (deptype = 'p' AND (classid != 0 OR objid != 0 OR objsubid != 0));
+      classid = 0 OR objid = 0 OR
+      deptype NOT IN ('a', 'e', 'i', 'n', 'x', 'P', 'S');
  classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
 ---------+-------+----------+------------+----------+-------------+---------
 (0 rows)
 
 -- **************** pg_shdepend ****************
 -- Look for illegal values in pg_shdepend fields.
--- classid/objid can be zero, but only in 'p' entries
 SELECT *
 FROM pg_shdepend as d1
 WHERE refclassid = 0 OR refobjid = 0 OR
-      deptype NOT IN ('a', 'o', 'p', 'r') OR
-      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
-      (deptype = 'p' AND (dbid != 0 OR classid != 0 OR objid != 0 OR objsubid != 0));
+      classid = 0 OR objid = 0 OR
+      deptype NOT IN ('a', 'o', 'r', 't');
  dbid | classid | objid | objsubid | refclassid | refobjid | deptype 
 ------+---------+-------+----------+------------+----------+---------
 (0 rows)
 
--- Check each OID-containing system catalog to see if its lowest-numbered OID
--- is pinned.  If not, and if that OID was generated during initdb, then
--- perhaps initdb forgot to scan that catalog for pinnable entries.
--- Generally, it's okay for a catalog to be listed in the output of this
--- test if that catalog is scanned by initdb.c's setup_depend() function;
--- whatever OID the test is complaining about must have been added later
--- in initdb, where it intentionally isn't pinned.  Legitimate exceptions
--- to that rule are listed in the comments in setup_depend().
--- Currently, pg_rewrite is also listed by this check, even though it is
--- covered by setup_depend().  That happens because there are no rules in
--- the pinned data, but initdb creates some intentionally-not-pinned views.
-do $$
-declare relnm text;
-  reloid oid;
-  shared bool;
-  lowoid oid;
-  pinned bool;
-begin
-for relnm, reloid, shared in
-  select relname, oid, relisshared from pg_class
-  where EXISTS(
-      SELECT * FROM pg_attribute
-      WHERE attrelid = pg_class.oid AND attname = 'oid')
-    and relkind = 'r' and oid < 16384 order by 1
-loop
-  execute 'select min(oid) from ' || relnm into lowoid;
-  continue when lowoid is null or lowoid >= 16384;
-  if shared then
-    pinned := exists(select 1 from pg_shdepend
-                     where refclassid = reloid and refobjid = lowoid
-                     and deptype = 'p');
-  else
-    pinned := exists(select 1 from pg_depend
-                     where refclassid = reloid and refobjid = lowoid
-                     and deptype = 'p');
-  end if;
-  if not pinned then
-    raise notice '% contains unpinned initdb-created object(s)', relnm;
-  end if;
-end loop;
-end$$;
-NOTICE:  pg_database contains unpinned initdb-created object(s)
-NOTICE:  pg_extension contains unpinned initdb-created object(s)
-NOTICE:  pg_rewrite contains unpinned initdb-created object(s)
-NOTICE:  pg_tablespace contains unpinned initdb-created object(s)
 -- **************** pg_class ****************
 -- Look for system tables with varlena columns but no toast table. All
 -- system tables with toastable columns should have toast tables, with
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index 9699f5cc3b3..2c0f87a651f 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -14,70 +14,24 @@
 -- **************** pg_depend ****************
 
 -- Look for illegal values in pg_depend fields.
--- classid/objid can be zero, but only in 'p' entries
 
 SELECT *
 FROM pg_depend as d1
 WHERE refclassid = 0 OR refobjid = 0 OR
-      deptype NOT IN ('a', 'e', 'i', 'n', 'p') OR
-      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
-      (deptype = 'p' AND (classid != 0 OR objid != 0 OR objsubid != 0));
+      classid = 0 OR objid = 0 OR
+      deptype NOT IN ('a', 'e', 'i', 'n', 'x', 'P', 'S');
+
 
 -- **************** pg_shdepend ****************
 
 -- Look for illegal values in pg_shdepend fields.
--- classid/objid can be zero, but only in 'p' entries
 
 SELECT *
 FROM pg_shdepend as d1
 WHERE refclassid = 0 OR refobjid = 0 OR
-      deptype NOT IN ('a', 'o', 'p', 'r') OR
-      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
-      (deptype = 'p' AND (dbid != 0 OR classid != 0 OR objid != 0 OR objsubid != 0));
-
-
--- Check each OID-containing system catalog to see if its lowest-numbered OID
--- is pinned.  If not, and if that OID was generated during initdb, then
--- perhaps initdb forgot to scan that catalog for pinnable entries.
--- Generally, it's okay for a catalog to be listed in the output of this
--- test if that catalog is scanned by initdb.c's setup_depend() function;
--- whatever OID the test is complaining about must have been added later
--- in initdb, where it intentionally isn't pinned.  Legitimate exceptions
--- to that rule are listed in the comments in setup_depend().
--- Currently, pg_rewrite is also listed by this check, even though it is
--- covered by setup_depend().  That happens because there are no rules in
--- the pinned data, but initdb creates some intentionally-not-pinned views.
-
-do $$
-declare relnm text;
-  reloid oid;
-  shared bool;
-  lowoid oid;
-  pinned bool;
-begin
-for relnm, reloid, shared in
-  select relname, oid, relisshared from pg_class
-  where EXISTS(
-      SELECT * FROM pg_attribute
-      WHERE attrelid = pg_class.oid AND attname = 'oid')
-    and relkind = 'r' and oid < 16384 order by 1
-loop
-  execute 'select min(oid) from ' || relnm into lowoid;
-  continue when lowoid is null or lowoid >= 16384;
-  if shared then
-    pinned := exists(select 1 from pg_shdepend
-                     where refclassid = reloid and refobjid = lowoid
-                     and deptype = 'p');
-  else
-    pinned := exists(select 1 from pg_depend
-                     where refclassid = reloid and refobjid = lowoid
-                     and deptype = 'p');
-  end if;
-  if not pinned then
-    raise notice '% contains unpinned initdb-created object(s)', relnm;
-  end if;
-end loop;
-end$$;
+      classid = 0 OR objid = 0 OR
+      deptype NOT IN ('a', 'o', 'r', 't');
+
 
 -- **************** pg_class ****************