Re: How am I supposed to fix this?

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