From: | Peter Geoghegan <pg(at)bowt(dot)ie> |
---|---|
To: | Paul McGarry <paul(at)paulmcgarry(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: ERROR: posting list tuple with 2 items cannot be split at offset 17 |
Date: | 2023-02-09 17:27:08 |
Message-ID: | CAH2-Wz=T6GY1E5n4dE9pfyugGKz9qK=+R2rJoJSEwk9VQ4i47A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Feb 8, 2023 at 11:54 PM Paul McGarry <paul(at)paulmcgarry(dot)com> wrote:
> But if it is the problem, why did the update start working after I recreated the other index?
There is no reason why reindexing another index ought to have had that
effect. The likely explanation is that subsequent updates used a
successor version heap TID that didn't overlap with some existing
posting list in whatever way. If you repeat the update again and
again, and get an error each time, the incoming TID will differ each
time. Eventually you won't get an error, because at some point there
won't be a posting-list-TID range overlap for some new successor TID
that leads to the insert/posting list split code detecting a problem.
It's also possible that a concurrent autovacuum "fixed" the issue.
The amcheck error shows a problem in an internal page, which cannot
have posting list tuples -- which suggests broad corruption. An issue
with collation instability due to an OS update does seem likely.
Note that the hardening/defensive checks in this area have increased.
I added an additional defensive check to 13.4, and followed up with
another similar check in 13.5. It looks like the error you've seen
("ERROR: posting list tuple with 2 items cannot be split at offset
17") comes from the initial 13.4 hardening, since I'd expect the
additional 13.5 hardening to catch the same issue sooner, with a
different error message (something like "table tid from new index
tuple (%u,%u) cannot find insert offset between offsets %u and %u of
block %u in index \"%s\"").
> I think I should now:
> - recreate the widget_name_idx on the problem servers
> - run bt_index_check across all other indexes
>
> Any suggestions on what else I should look into, in particular anything I should check before upgrading the remaining 13.8 DB to 13.9?
I recommend running amcheck on all indexes, or at least all
possibly-affected text indexes.
--
Peter Geoghegan
From | Date | Subject | |
---|---|---|---|
Next Message | Dominique Devienne | 2023-02-09 17:35:42 | Re: WHERE col = ANY($1) extended to 2 or more columns? |
Previous Message | Adrian Klaver | 2023-02-09 16:59:48 | Re: ERROR: posting list tuple with 2 items cannot be split at offset 17 |