src/test/regress/expected/misc_sanity_external_fts.out

--
-- MISC_SANITY
-- Sanity checks for common errors in making system tables that don't fit
-- comfortably into either opr_sanity or type_sanity.
--
-- Every test failure in this file should be closely inspected.
-- The description of the failing test should be read carefully before
-- adjusting the expected output.  In most cases, the queries should
-- not find *any* matching entries.
--
-- 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 | 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));
 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_foreign_data_wrapper contains unpinned initdb-created object(s)
NOTICE:  pg_foreign_server 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
-- the following exceptions:
-- 1. pg_class, pg_attribute, and pg_index, due to fear of recursive
-- dependencies as toast tables depend on them.
-- 2. pg_largeobject and pg_largeobject_metadata.  Large object catalogs
-- and toast tables are mutually exclusive and large object data is handled
-- as user data by pg_upgrade, which would cause failures.
-- GPDB: A few GPDB catalog tables are also missing toast tables. Not
-- for any particular reason, but the fields are only used to store short
-- system-generated values, so they don't need toasting.
SELECT relname, attname, atttypid::regtype
FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
WHERE c.oid < 16384 AND
      reltoastrelid = 0 AND
      relkind = 'r' AND
      attstorage != 'p'
ORDER BY 1, 2;
         relname          |      attname       |   atttypid   
--------------------------+--------------------+--------------
 gp_configuration_history | desc               | text
 gp_version_at_initdb     | productversion     | text
 gp_warehouse             | warehouse_name     | text
 pg_attribute             | attacl             | aclitem[]
 pg_attribute             | attfdwoptions      | text[]
 pg_attribute             | attmissingval      | anyarray
 pg_attribute             | attoptions         | text[]
 pg_class                 | relacl             | aclitem[]
 pg_class                 | reloptions         | text[]
 pg_class                 | relpartbound       | pg_node_tree
 pg_foreign_table_seg     | ftsoptions         | text[]
 pg_index                 | indexprs           | pg_node_tree
 pg_index                 | indpred            | pg_node_tree
 pg_largeobject           | data               | bytea
 pg_largeobject_metadata  | lomacl             | aclitem[]
 pg_resgroupcapability    | value              | text
 pg_resourcetype          | resdefaultsetting  | text
 pg_resourcetype          | resdisabledsetting | text
 pg_resqueuecapability    | ressetting         | text
 pg_stat_last_operation   | stasubtype         | text
 pg_stat_last_shoperation | stasubtype         | text
 pg_tag                   | allowed_values     | text[]
 pg_tag_description       | tagvalue           | text
 pg_task                  | command            | text
 pg_task                  | database           | text
 pg_task                  | jobname            | text
 pg_task                  | nodename           | text
 pg_task                  | schedule           | text
 pg_task                  | username           | text
 pg_task_run_history      | command            | text
 pg_task_run_history      | database           | text
 pg_task_run_history      | return_message     | text
 pg_task_run_history      | status             | text
 pg_task_run_history      | username           | text
(34 rows)

-- system catalogs without primary keys
--
-- Current exceptions:
-- * pg_depend, pg_shdepend don't have a unique key
SELECT relname
FROM pg_class
WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'r'
      AND pg_class.oid NOT IN (SELECT indrelid FROM pg_index WHERE indisprimary)
      AND relname NOT like 'gp_segment_configuration'
ORDER BY 1;
         relname          
--------------------------
 gp_configuration_history
 gp_distribution_policy
 gp_fastsequence
 gp_id
 gp_matview_aux
 gp_matview_tables
 gp_partition_template
 gp_version_at_initdb
 gp_warehouse
 pg_appendonly
 pg_attribute_encoding
 pg_auth_time_constraint
 pg_compression
 pg_depend
 pg_extprotocol
 pg_foreign_table_seg
 pg_password_history
 pg_proc_callback
 pg_profile
 pg_resgroup
 pg_resgroupcapability
 pg_resourcetype
 pg_resqueue
 pg_resqueuecapability
 pg_shdepend
 pg_stat_last_operation
 pg_stat_last_shoperation
 pg_type_encoding
(28 rows)

-- system catalog unique indexes not wrapped in a constraint
-- (There should be none.)
SELECT relname
FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid
WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'i'
      AND i.indisunique
      AND c.oid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY 1;
 relname 
---------
(0 rows)