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.