On Thu, Nov 4, 2021 at 8:18 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Thu, Nov 4, 2021 at 11:08 AM Maxim Boguk <maxim.boguk@gmail.com> wrote:
> > select bt_index_check('pg_toast.pg_toast_2624976286_index', true);
> > DEBUG: verifying consistency of tree structure for index
> > "pg_toast_2624976286_index"
> > DEBUG: verifying level 3 (true root level)
> > DEBUG: verifying level 2
> > DEBUG: verifying level 1
> > DEBUG: verifying level 0 (leaf level)
> > DEBUG: leaf block 715360 of index "pg_toast_2624976286_index" has no
> > first data item
> > DEBUG: verifying that tuples from index "pg_toast_2624976286_index"
> > are present in "pg_toast_2624976286"
> > ERROR: heap tuple (59561917,1) from table "pg_toast_2624976286" lacks
> > matching index tuple within index "pg_toast_2624976286_index"
> > HINT: Retrying verification using the function
> > bt_index_parent_check() might provide a more specific error.
>
> That's an unusually large TOAST table. It's at least ~454.42GiB, based
> on this error. Is the block number 59561917 near the end of the table?
select pg_size_pretty(pg_relation_size('pg_toast.pg_toast_2624976286'));
pg_size_pretty
----------------
473 GB
now... and yes during the time of error page 59561917 was very close
to the end of the table.
There was a high chance (but not 100%) that the corresponding main
table entry had been inserted during reindex CONCURRENTLY of the toast
index run.
We have base backup and wal archive so theoretically it's possible to
restore sequence of writes which lead to error,
but given huge size of relation in interest (and even bigger size of
whole database 10+TB) and large amount of writes it's a complicated
task (especially when I not really sure what exactly to look for in
waldump output).
--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/
Phone RU: +7 985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему
больно когда я так делаю ещё раз?"