1--
2-- MISC_SANITY
3-- Sanity checks for common errors in making system tables that don't fit
4-- comfortably into either opr_sanity or type_sanity.
5--
6-- Every test failure in this file should be closely inspected.
7-- The description of the failing test should be read carefully before
8-- adjusting the expected output.  In most cases, the queries should
9-- not find *any* matching entries.
10--
11-- NB: run this test early, because some later tests create bogus entries.
12-- **************** pg_depend ****************
13-- Look for illegal values in pg_depend fields.
14-- classid/objid can be zero, but only in 'p' entries
15SELECT *
16FROM pg_depend as d1
17WHERE refclassid = 0 OR refobjid = 0 OR
18      deptype NOT IN ('a', 'e', 'i', 'n', 'p') OR
19      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
20      (deptype = 'p' AND (classid != 0 OR objid != 0 OR objsubid != 0));
21 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
22---------+-------+----------+------------+----------+-------------+---------
23(0 rows)
24
25-- **************** pg_shdepend ****************
26-- Look for illegal values in pg_shdepend fields.
27-- classid/objid can be zero, but only in 'p' entries
28SELECT *
29FROM pg_shdepend as d1
30WHERE refclassid = 0 OR refobjid = 0 OR
31      deptype NOT IN ('a', 'o', 'p', 'r') OR
32      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
33      (deptype = 'p' AND (dbid != 0 OR classid != 0 OR objid != 0 OR objsubid != 0));
34 dbid | classid | objid | objsubid | refclassid | refobjid | deptype
35------+---------+-------+----------+------------+----------+---------
36(0 rows)
37
38-- Check each OID-containing system catalog to see if its lowest-numbered OID
39-- is pinned.  If not, and if that OID was generated during initdb, then
40-- perhaps initdb forgot to scan that catalog for pinnable entries.
41-- Generally, it's okay for a catalog to be listed in the output of this
42-- test if that catalog is scanned by initdb.c's setup_depend() function;
43-- whatever OID the test is complaining about must have been added later
44-- in initdb, where it intentionally isn't pinned.  Legitimate exceptions
45-- to that rule are listed in the comments in setup_depend().
46do $$
47declare relnm text;
48  reloid oid;
49  shared bool;
50  lowoid oid;
51  pinned bool;
52begin
53for relnm, reloid, shared in
54  select relname, oid, relisshared from pg_class
55  where EXISTS(
56      SELECT * FROM pg_attribute
57      WHERE attrelid = pg_class.oid AND attname = 'oid')
58    and relkind = 'r' and oid < 16384 order by 1
59loop
60  execute 'select min(oid) from ' || relnm into lowoid;
61  continue when lowoid is null or lowoid >= 16384;
62  if shared then
63    pinned := exists(select 1 from pg_shdepend
64                     where refclassid = reloid and refobjid = lowoid
65                     and deptype = 'p');
66  else
67    pinned := exists(select 1 from pg_depend
68                     where refclassid = reloid and refobjid = lowoid
69                     and deptype = 'p');
70  end if;
71  if not pinned then
72    raise notice '% contains unpinned initdb-created object(s)', relnm;
73  end if;
74end loop;
75end$$;
76NOTICE:  pg_constraint contains unpinned initdb-created object(s)
77NOTICE:  pg_database contains unpinned initdb-created object(s)
78NOTICE:  pg_extension contains unpinned initdb-created object(s)
79NOTICE:  pg_rewrite contains unpinned initdb-created object(s)
80NOTICE:  pg_tablespace contains unpinned initdb-created object(s)
81-- **************** pg_class ****************
82-- Look for system tables with varlena columns but no toast table. All
83-- system tables with toastable columns should have toast tables, with
84-- the following exceptions:
85-- 1. pg_class, pg_attribute, and pg_index, due to fear of recursive
86-- dependencies as toast tables depend on them.
87-- 2. pg_largeobject and pg_largeobject_metadata.  Large object catalogs
88-- and toast tables are mutually exclusive and large object data is handled
89-- as user data by pg_upgrade, which would cause failures.
90SELECT relname, attname, atttypid::regtype
91FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
92WHERE c.oid < 16384 AND
93      reltoastrelid = 0 AND
94      relkind = 'r' AND
95      attstorage != 'p'
96ORDER BY 1, 2;
97         relname         |    attname    |   atttypid
98-------------------------+---------------+--------------
99 pg_attribute            | attacl        | aclitem[]
100 pg_attribute            | attfdwoptions | text[]
101 pg_attribute            | attmissingval | anyarray
102 pg_attribute            | attoptions    | text[]
103 pg_class                | relacl        | aclitem[]
104 pg_class                | reloptions    | text[]
105 pg_class                | relpartbound  | pg_node_tree
106 pg_index                | indexprs      | pg_node_tree
107 pg_index                | indpred       | pg_node_tree
108 pg_largeobject          | data          | bytea
109 pg_largeobject_metadata | lomacl        | aclitem[]
110(11 rows)
111
112