Re: BUG #17245: Index corruption involving deduplicated entries

On 30.10.2021 21:46, Peter Geoghegan wrote:
> 
> Attached is a draft patch that fixes the problem.

Thank you!

Going to install the patched version on the production system to see how 
it fares (and I expect good things).

Tried against the re-extracted snapshot, looks good (everything still 
needs reindexing, obviously, but that is beside the point right now):

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_len" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  scanned index "page_main_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s
INFO:  scanned index "page_mediawiki_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  scanned index "page_project_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  scanned index "page_talk_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  scanned index "page_user_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  scanned index "page_utalk_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  scanned index "page_pkey" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 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.08 s
INFO:  scanned index "page_random" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.14 s
INFO:  scanned index "name_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.19 s
INFO:  scanned index "ts2_page_title" to remove 251 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.04 s
INFO:  table "page": removed 251 dead item identifiers in 215 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "page_pkey" now contains 46056 row versions in 131 pages
DETAIL:  190 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "name_title" now contains 46056 row versions in 330 pages
DETAIL:  211 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_len" now contains 51848 row versions in 61 pages
DETAIL:  681 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_main_title" now contains 3569 row versions in 18 pages
DETAIL:  147 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_mediawiki_title" now contains 15 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_project_title" now contains 1 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_random" now contains 46056 row versions in 127 pages
DETAIL:  251 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_redirect_namespace_len" now contains 47663 row 
versions in 68 pages
DETAIL:  265 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_talk_title" now contains 35 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_user_title" now contains 735 row versions in 6 pages
DETAIL:  6 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "page_utalk_title" now contains 44 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "ts2_page_title" now contains 46056 row versions in 211 pages
DETAIL:  251 index row versions were removed.
0 index pages were newly deleted.
0 index pages are currently deleted, of which 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 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.00 s, system: 0.01 s, elapsed: 0.50 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

-- 
K. R.