Dear Peter,
so the 2nd query look like this:
SELECT bt_index_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;
and it's output listed below:
DEBUG: verifying that tuples from index "price_model_product_id_latest_idx" are present in "price_model"
DEBUG: finished verifying presence of 5598051 tuples from table "price_model" with bitset 48.61% set
DEBUG: verifying consistency of tree structure for index "name_original_idx_s"
DEBUG: verifying level 3 (true root level)
DEBUG: verifying level 2
DEBUG: verifying level 1
ERROR: item order invariant violated for index "name_original_idx_s"
DETAIL: Lower index tid=(11900,58) (points to index tid=(858,1)) higher index tid=(11900,59) (points to index tid=(859,1)) page lsn=1CE8/D3E85550.
develop052021=#
Thanks for your support!
сб, 15 мая 2021 г. в 00:25, Peter Geoghegan <
pg@bowt.ie>:
On Fri, May 14, 2021 at 12:12 PM Alex F <phoedos16@gmail.com> wrote:
> Dear Peter,
> Honestly don't know if you expect a response with amcheck results but anyway will paste it here:
It is helpful -- thanks!
It should be possible to avoid this problem by reindexing. Of course
it's important to eliminate whatever the source of the corruption is,
which might be much harder.
Could you execute exactly the same query, only this time use
"bt_index_check(index => c.oid, heapallindexed => true)" in place of
the bt_index_parent_check() call from the original query? Maybe there
is something more to be learned by just focussing on the leaf pages,
and not failing earlier on, in the parent pages. The less thorough
bt_index_check() function can sometimes show something interesting by
failing later than bt_index_parent_check() would fail with the same
index.
I note that the amcheck error message that you showed happens between
level 2 and level 1, neither of which are leaf level (that's level 0)
-- only leaf pages can have posting list tuples. To me this suggests
that the chances of corruption being a bug in deduplication
specifically are very remote (it's more likely to be a bug in some
other place, even). I'm always curious about real world corruption, so
I'd still appreciate seeing the bt_index_check() variant query's
output just to satisfy myself that that's what it is.
--
Peter Geoghegan