From: Kamigishi Rei <iijima.yun@koumakan.jp>
To: Peter Geoghegan <pg@bowt.ie>
To: Andres Freund <andres@anarazel.de>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
Date: Sat, 30 Oct 2021 22:42:07 +0300
Message-ID: <308eb378-dce3-0529-e6ee-eb8e4ea026f7@koumakan.jp> (raw)
In-Reply-To: <CAH2-WzkN5aESSLfK7-yrYgsXxYUi__VzG4XpZFwXm98LUtoWuQ@mail.gmail.com>
References: <20211028220124.n4vulzxp6i6fsq4t@alap3.anarazel.de>
<551936fa-9ba8-aed1-7ae1-c77d5920101c@koumakan.jp>
<9c7433f6-65b3-9f57-e8a3-4414d0cf0c53@koumakan.jp>
<399affa1-c9c5-9b3e-c14f-b24ecb86b303@koumakan.jp>
<20211029201848.zixfp5wk2hvkeaq2@alap3.anarazel.de>
<ed307eed-ef62-c33b-3b25-fcea76713417@koumakan.jp>
<20211029213048.q5ltta6dze3sykh3@alap3.anarazel.de>
<20211029215902.7um7dmzl4lveuk3h@alap3.anarazel.de>
<8809ef6d-d247-5ca8-820d-b9daec61e818@koumakan.jp>
<20211030023740.qbnsl2xaoh2grq3d@alap3.anarazel.de>
<20211030041217.sp2le3hvtmzfghwo@alap3.anarazel.de>
<CAH2-WzkN5aESSLfK7-yrYgsXxYUi__VzG4XpZFwXm98LUtoWuQ@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: iijima.yun@koumakan.jp, pg@bowt.ie, andres@anarazel.de, amit.kapila16@gmail.com, andrew@tao11.riddles.org.uk, alvherre@alvh.no-ip.org, pgsql-bugs@lists.postgresql.org, thomas.munro@gmail.com
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
In-Reply-To: <308eb378-dce3-0529-e6ee-eb8e4ea026f7@koumakan.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox