Date: Mon, 25 Oct 2021 16:53:07 +0200
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Herman verschooten <Herman@verschooten.net>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: ERROR:  posting list tuple with 20 items cannot be split at
 offset 168
Message-ID: <20211025145307.zvnclgqhmum4x6et@localhost>
References: <8CDB73C1-E3AF-40A6-BA81-8AFE174C6402@verschooten.net>
 <20211025114159.tfeirihcojzzcxed@localhost>
 <C2E49533-17CB-4090-9A5A-3B0F065704A5@verschooten.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <C2E49533-17CB-4090-9A5A-3B0F065704A5@verschooten.net>
Archived-At: 
 <https://www.postgresql.org/message-id/20211025145307.zvnclgqhmum4x6et%40localhost>
Precedence: bulk

> On Mon, Oct 25, 2021 at 02:41:43PM +0200, Herman verschooten wrote:
> I made a mistake apparently on checking the wrong thing.
> someone on slack helped me.
>
> tranman_production=#  SELECT bt_index_check(c.oid), c.relname, c.relpages
> tranman_production-# FROM pg_index i
> tranman_production-# JOIN pg_opclass op ON i.indclass[0] = op.oid
> tranman_production-# JOIN pg_am am ON op.opcmethod = am.oid
> tranman_production-# JOIN pg_class c ON i.indexrelid = c.oid
> tranman_production-# JOIN pg_namespace n ON c.relnamespace = n.oid
> tranman_production-# WHERE am.amname = 'btree' AND n.nspname = 'public'
> tranman_production-# -- Don't check temp tables, which may be from another session:
> tranman_production-# AND c.relpersistence != 't'
> tranman_production-# -- Function may throw an error when this is omitted:
> tranman_production-# AND i.indisready AND i.indisvalid
> tranman_production-# ORDER BY c.relpages;
> ERROR:  item order invariant violated for index "index_products_on_group"
> DETAIL:  Lower index tid=(1,353) (points to heap tid=(19,8)) higher index tid=(1,354) (points to heap tid=(14,14)) page lsn=5/CEC25DA8.
> tranman_production=#  SELECT bt_index_check(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' AND n.nspname = 'public'
> -- 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 i.indisready AND i.indisvalid
> ORDER BY c.relpages;
> ERROR:  item order invariant violated for index "index_products_on_group"
> DETAIL:  Lower index tid=(1,353) (points to heap tid=(19,8)) higher index tid=(1,354) (points to heap tid=(14,14)) page lsn=5/CEC25DA8.
>
> I dropped that index and ran it again, and kept on dropping indexes
>
> ERROR:  item order invariant violated for index "index_cleanings_on_receipt"
> DETAIL:  Lower index tid=(39,21) (points to heap tid=(238,98)) higher index tid=(39,22) (points to heap tid=(243,7)) page lsn=1/B853F98.
>
> ERROR:  item order invariant violated for index "index_freights_on_reference_4"
> DETAIL:  Lower index tid=(34,51) (points to heap tid=(67,7)) higher index tid=(34,52) (points to heap tid=(84,2)) page lsn=6/7EFB68.
>
> ERROR:  item order invariant violated for index "index_freights_on_reference_3"
> DETAIL:  Lower index tid=(38,82) (points to heap tid=(362,75)) higher index tid=(38,83) (points to heap tid=(194,21)) page lsn=4/FEA79090.
>
> ERROR:  item order invariant violated for index "index_freights_on_reference_2"
> DETAIL:  Lower index tid=(10,44) (points to heap tid=(176,26)) higher index tid=(10,45) (points to heap tid=(215,60)) page lsn=5/46A4D90.
>
> ERROR:  item order invariant violated for index "index_freights_on_reference_1"
> DETAIL:  Lower index tid=(43,214) (points to heap tid=(112,46)) higher index tid=(43,215) (points to heap tid=(112,43)) page lsn=5/C3999990.
>
>
> Now no more errors are displayed, and I can run my initial update again, with the index created.
>
> I recreated the 6 indexes and everyhting still works.
>
> Herman

Does it mean that everything is fine after recreating
index_freights_on_reference_<d> indexes? Well, that would mean they were
somehow corrupted, and reindexing is the way to go. Although it's not
clear for me, why the problem was temporarily avoided by dropping
another index on cmr_received.

PS: One small tip, please reply to all in the mailing list, otherwise it
would be harder for others to understand what happened with the discussion.