On Tue, Aug 6, 2019 at 10:19 AM Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
> The question is how much other data corruption is there ...
Larry could try running amcheck on the other indexes. Just the basic
bt_check_index() checks should be enough to detect problems like this.
They can be run fairly non-disruptively. Something like this should do
it:
SELECT bt_index_check(index => c.oid),
c.relname,
c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC;
If this takes too long, you can always adjust the query to only verify
system indexes or TOAST indexes.
--
Peter Geoghegan
