Hi, On Sun, Mar 14, 2021 at 11:24 PM Peter Geoghegan <p...@bowt.ie> wrote:
> On Sun, Mar 14, 2021 at 6:54 PM Avinash Kumar > <avinash.vallar...@gmail.com> wrote: > > Following may be helpful to understand what I meant. > > > > I have renamed the table and index names before adding it here. > > It should be possible to run amcheck on your database, which will > detect corrupt posting list tuples on Postgres 13. It's a contrib > extension, so you must first run "CREATE EXTENSION amcheck;". From > there, you can run a query like the following (you may want to > customize this): > > SELECT bt_index_parent_check(index => c.oid, heapallindexed => true), > 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 query takes too long to complete you may find it useful to add > something to limit the indexes check, such as: AND n.nspname = > 'public' -- that change to the SQL will make the query just test > indexes from the public schema. > > Do "SET client_min_messages=DEBUG1 " to get a kind of rudimentary > progress indicator, if that seems useful to you. > I see that there are 26 Indexes for which there are 100 to thousands of entries similar to the following. All are of course btree indexes. psql:amchecksql.sql:17: DEBUG: leaf block 1043751 of index "idx_id_mtime" has no first data item And one error as follows. psql:amchecksql.sql:17: ERROR: down-link lower bound invariant violated for index "some_other_index" > > The docs have further information on what this bt_index_parent_check > function does, should you need it: > https://www.postgresql.org/docs/13/amcheck.html > > -- > Peter Geoghegan > -- Regards, Avi.
