Re: BUG #17268: Possible corruption in toast index after reindex index concurrently

On Thu, Nov 4, 2021 at 9:47 PM Maxim Boguk <maxim.boguk@gmail.com> wrote:
>
> 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.

UPDATE: the problem base table entry which had erroneous toast data
definitely had been created during the REINDEX run of toast idx.

2021-11-02 13:15:45.469 UTC 2379 postgres@*** from [local] [vxid:24/0
txid:0] [REINDEX] LOG:  duration: 1719120.441 ms  statement: REINDEX
INDEX CONCURRENTLY pg_toast.pg_toast_2624976286_index
and the problem entry had been created at 2021-11-02 13:04:22.192125 UTC.

So there seems some subtle bug with indexing new toast data during
REINDEX INDEX CONCURRENTLY of the toast index.
--
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

"Доктор, вы мне советовали так не делать, но почему мне по-прежнему
больно когда я так делаю ещё раз?"