1VACUUM;
2
3--
4-- sanity check, if we don't have indices the test will take years to
5-- complete.  But skip TOAST relations (since they will have varying
6-- names depending on the current OID counter) as well as temp tables
7-- of other backends (to avoid timing-dependent behavior).
8--
9
10-- temporarily disable fancy output, so catalog changes create less diff noise
11\a\t
12
13SELECT relname, relhasindex
14   FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace
15   WHERE relkind IN ('r', 'p') AND (nspname ~ '^pg_temp_') IS NOT TRUE
16   ORDER BY relname;
17
18-- restore normal output mode
19\a\t
20
21--
22-- another sanity check: every system catalog that has OIDs should have
23-- a unique index on OID.  This ensures that the OIDs will be unique,
24-- even after the OID counter wraps around.
25-- We exclude non-system tables from the check by looking at nspname.
26--
27SELECT relname, nspname
28 FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace JOIN pg_attribute a ON (attrelid = c.oid AND attname = 'oid')
29 WHERE relkind = 'r' and c.oid < 16384
30     AND ((nspname ~ '^pg_') IS NOT FALSE)
31     AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid
32                     AND indkey[0] = a.attnum AND indnatts = 1
33                     AND indisunique AND indimmediate);
34
35-- check that relations without storage don't have relfilenode
36SELECT relname, relkind
37  FROM pg_class
38 WHERE relkind IN ('v', 'c', 'f', 'p', 'I')
39       AND relfilenode <> 0;
40