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
Date: Mon, 25 Oct 2021 16:53:07 +0200
Message-ID: <20211025145307.zvnclgqhmum4x6et@localhost> (raw)
In-Reply-To: <C2E49533-17CB-4090-9A5A-3B0F065704A5@verschooten.net>
References: <8CDB73C1-E3AF-40A6-BA81-8AFE174C6402@verschooten.net>
<20211025114159.tfeirihcojzzcxed@localhost>
<C2E49533-17CB-4090-9A5A-3B0F065704A5@verschooten.net>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: 9erthalion6@gmail.com, Herman@verschooten.net, pgsql-bugs@lists.postgresql.org
Subject: Re: ERROR: posting list tuple with 20 items cannot be split at offset 168
In-Reply-To: <20211025145307.zvnclgqhmum4x6et@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox