Re: BUG #16833: postgresql 13.1 process crash every hour

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
>