On 08/06/2019 12:30 pm, Peter Geoghegan wrote:
> 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.
ler=# SELECT bt_index_check(index => c.oid),
ler-# c.relname,
ler-# c.relpages
ler-# FROM pg_index i
ler-# JOIN pg_opclass op ON i.indclass[0] = op.oid
ler-# JOIN pg_am am ON op.opcmethod = am.oid
ler-# JOIN pg_class c ON i.indexrelid = c.oid
ler-# JOIN pg_namespace n ON c.relnamespace = n.oid
ler-# WHERE am.amname = 'btree'
ler-# -- Don't check temp tables, which may be from another session:
ler-# AND c.relpersistence != 't'
ler-# -- Function may throw an error when this is omitted:
ler-# AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ler-# ORDER BY c.relpages DESC;
ERROR: function bt_index_check(index => oid) does not exist
LINE 1: SELECT bt_index_check(index => c.oid),
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
ler=#
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: ler@lerctr.org
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106