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