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
13
14-- **************** pg_depend ****************
15
16-- Look for illegal values in pg_depend fields.
17-- classid/objid can be zero, but only in 'p' entries
18
19SELECT *
20FROM pg_depend as d1
21WHERE refclassid = 0 OR refobjid = 0 OR
22      deptype NOT IN ('a', 'e', 'i', 'n', 'p') OR
23      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
24      (deptype = 'p' AND (classid != 0 OR objid != 0 OR objsubid != 0));
25
26-- **************** pg_shdepend ****************
27
28-- Look for illegal values in pg_shdepend fields.
29-- classid/objid can be zero, but only in 'p' entries
30
31SELECT *
32FROM pg_shdepend as d1
33WHERE refclassid = 0 OR refobjid = 0 OR
34      deptype NOT IN ('a', 'o', 'p', 'r') OR
35      (deptype != 'p' AND (classid = 0 OR objid = 0)) OR
36      (deptype = 'p' AND (dbid != 0 OR classid != 0 OR objid != 0 OR objsubid != 0));
37
38
39-- Check each OID-containing system catalog to see if its lowest-numbered OID
40-- is pinned.  If not, and if that OID was generated during initdb, then
41-- perhaps initdb forgot to scan that catalog for pinnable entries.
42-- Generally, it's okay for a catalog to be listed in the output of this
43-- test if that catalog is scanned by initdb.c's setup_depend() function;
44-- whatever OID the test is complaining about must have been added later
45-- in initdb, where it intentionally isn't pinned.  Legitimate exceptions
46-- to that rule are listed in the comments in setup_depend().
47-- Currently, pg_rewrite is also listed by this check, even though it is
48-- covered by setup_depend().  That happens because there are no rules in
49-- the pinned data, but initdb creates some intentionally-not-pinned views.
50
51do $$
52declare relnm text;
53  reloid oid;
54  shared bool;
55  lowoid oid;
56  pinned bool;
57begin
58for relnm, reloid, shared in
59  select relname, oid, relisshared from pg_class
60  where EXISTS(
61      SELECT * FROM pg_attribute
62      WHERE attrelid = pg_class.oid AND attname = 'oid')
63    and relkind = 'r' and oid < 16384 order by 1
64loop
65  execute 'select min(oid) from ' || relnm into lowoid;
66  continue when lowoid is null or lowoid >= 16384;
67  if shared then
68    pinned := exists(select 1 from pg_shdepend
69                     where refclassid = reloid and refobjid = lowoid
70                     and deptype = 'p');
71  else
72    pinned := exists(select 1 from pg_depend
73                     where refclassid = reloid and refobjid = lowoid
74                     and deptype = 'p');
75  end if;
76  if not pinned then
77    raise notice '% contains unpinned initdb-created object(s)', relnm;
78  end if;
79end loop;
80end$$;
81
82-- **************** pg_class ****************
83
84-- Look for system tables with varlena columns but no toast table. All
85-- system tables with toastable columns should have toast tables, with
86-- the following exceptions:
87-- 1. pg_class, pg_attribute, and pg_index, due to fear of recursive
88-- dependencies as toast tables depend on them.
89-- 2. pg_largeobject and pg_largeobject_metadata.  Large object catalogs
90-- and toast tables are mutually exclusive and large object data is handled
91-- as user data by pg_upgrade, which would cause failures.
92
93SELECT relname, attname, atttypid::regtype
94FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
95WHERE c.oid < 16384 AND
96      reltoastrelid = 0 AND
97      relkind = 'r' AND
98      attstorage != 'p'
99ORDER BY 1, 2;
100
101
102-- system catalogs without primary keys
103--
104-- Current exceptions:
105-- * pg_depend, pg_shdepend don't have a unique key
106SELECT relname
107FROM pg_class
108WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'r'
109      AND pg_class.oid NOT IN (SELECT indrelid FROM pg_index WHERE indisprimary)
110ORDER BY 1;
111
112
113-- system catalog unique indexes not wrapped in a constraint
114-- (There should be none.)
115SELECT relname
116FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid
117WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'i'
118      AND i.indisunique
119      AND c.oid NOT IN (SELECT conindid FROM pg_constraint)
120ORDER BY 1;
121