Re: BUG #17245: Index corruption involving deduplicated entries

On 30.10.2021 7:12, Andres Freund wrote:

>> Whew, we found the bug, I think. Or at least one that can create exactly this
>> situation.
>> [explanation]
>> I haven't yet checked whether this is a bug introduced in 14, or whether it
>> was possible to hit before as well.
>
> It looks like a v14 issue.
>
> Going a bit further than this, ISTM that once we decide to use
> parallelism for any index, there's no point not using parallelism for
> all the parallel-safe indexes...

Probably of use: on Andrew Gierth's suggestion I performed `VACUUM
VERBOSE mediawiki.page` with the snapshot database:

azurlane_wiki=# vacuum verbose mediawiki.page;
INFO: vacuuming "mediawiki.page"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: scanned index "page_pkey" to remove 251 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
INFO: scanned index "page_random" to remove 251 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
INFO: scanned index "page_redirect_namespace_len" to remove 251 row
versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: scanned index "name_title" to remove 251 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
INFO: scanned index "ts2_page_title" to remove 251 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s
INFO: table "page": removed 251 dead item identifiers in 215 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s
INFO: table "page": found 14 removable, 44250 nonremovable row versions
in 876 out of 912 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2097658
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.18 s.
INFO: vacuuming "pg_toast.pg_toast_19560"
INFO: table "pg_toast_19560": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2097658
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

Here is the list of indices associated with mediawiki.page:

┌────────────────┬─────────────────────────────┬──────────────┐
│ table │ index │ index_method │
├────────────────┼─────────────────────────────┼──────────────┤
│ mediawiki.page │ page_pkey │ btree │
│ mediawiki.page │ name_title │ btree │
│ mediawiki.page │ page_len │ btree │
│ mediawiki.page │ page_main_title │ btree │
│ mediawiki.page │ page_mediawiki_title │ btree │
│ mediawiki.page │ page_project_title │ btree │
│ mediawiki.page │ page_random │ btree │
│ mediawiki.page │ page_redirect_namespace_len │ btree │
│ mediawiki.page │ page_talk_title │ btree │
│ mediawiki.page │ page_user_title │ btree │
│ mediawiki.page │ page_utalk_title │ btree │
│ mediawiki.page │ ts2_page_title │ gist │
└────────────────┴─────────────────────────────┴──────────────┘

VACUUM lists page_redirect_namespace_len, however pg_amcheck still
complains about it afterwards. page_len and page_main_title (and others)
are not listed by VACUUM.

--
K. R.