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