Re: BUG #17245: Index corruption involving deduplicated entries

On 26.10.2021 23:36, Peter Geoghegan wrote:
> On Mon, Oct 25, 2021 at 2:29 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> A few more questions for you, if you don't mind:
>
> 1. Can you tell me which tables the 4 known-corrupt indexes
> (page_redirect_namespace_len, page_len, transcode_key_idx, and
> page_main_title) are defined against?

page and transcode.

> 2. Can you show me the definition of the table or tables, using \d from psql?

azurlane_wiki=> \d mediawiki.page
Table "mediawiki.page"
Column | Type | Collation | Nullable |
Default
--------------------+--------------------------+-----------+----------+-------------------------------------------------
page_id | integer | | not null |
nextval('mediawiki.page_page_id_seq'::regclass)
page_namespace | integer | | not null |
page_title | text | | not null |
page_restrictions | text | | |
page_is_redirect | smallint | | not null | 0
page_is_new | smallint | | not null | 0
page_random | double precision | | not null |
random()
page_touched | timestamp with time zone | | not null |
page_links_updated | timestamp with time zone | | |
page_latest | integer | | not null |
page_len | integer | | not null |
page_content_model | text | | |
page_lang | text | | |
titlevector | tsvector | | |
Indexes:
"page_pkey" PRIMARY KEY, btree (page_id)
"name_title" UNIQUE, btree (page_namespace, page_title)
"page_len" btree (page_len)
"page_main_title" btree (page_title text_pattern_ops) WHERE
page_namespace = 0
"page_mediawiki_title" btree (page_title text_pattern_ops) WHERE
page_namespace = 8
"page_project_title" btree (page_title text_pattern_ops) WHERE
page_namespace = 4
"page_random" btree (page_random)
"page_redirect_namespace_len" btree (page_is_redirect,
page_namespace, page_len)
"page_talk_title" btree (page_title text_pattern_ops) WHERE
page_namespace = 1
"page_user_title" btree (page_title text_pattern_ops) WHERE
page_namespace = 2
"page_utalk_title" btree (page_title text_pattern_ops) WHERE
page_namespace = 3
"ts2_page_title" gist (titlevector)
Referenced by:
TABLE "mediawiki.cu_changes" CONSTRAINT
"cu_changes_cuc_page_id_fkey" FOREIGN KEY (cuc_page_id) REFERENCES
mediawiki.page(page_id) ON DELETE SET NULL
TABLE "mediawiki.revision" CONSTRAINT "revision_rev_page_fkey"
FOREIGN KEY (rev_page) REFERENCES mediawiki.page(page_id) ON DELETE
CASCADE DEFERRABLE INITIALLY DEFERRED
Triggers:
page_deleted AFTER DELETE ON mediawiki.page FOR EACH ROW EXECUTE
FUNCTION mediawiki.page_deleted()
ts2_page_title BEFORE INSERT OR UPDATE ON mediawiki.page FOR EACH
ROW EXECUTE FUNCTION mediawiki.ts2_page_title()

azurlane_wiki=> \d mediawiki.transcode
Table
"mediawiki.transcode"
Column | Type | Collation |
Nullable | Default
--------------------------+--------------------------+-----------+----------+-----------------------------------------------------------
transcode_id | integer | | not
null | nextval('mediawiki.transcode_transcode_id_seq'::regclass)
transcode_image_name | character varying(255) | | not
null |
transcode_key | character varying(48) | | not
null |
transcode_error | text | | not
null |
transcode_time_addjob | timestamp with time zone | |
|
transcode_time_startwork | timestamp with time zone | |
|
transcode_time_success | timestamp with time zone | |
|
transcode_time_error | timestamp with time zone | |
|
transcode_final_bitrate | integer | | not
null |
Indexes:
"transcode_key_idx" btree (transcode_key)
"transcode_name_key" UNIQUE, btree (transcode_image_name,
transcode_key)
"transcode_time_inx" btree (transcode_time_addjob,
transcode_time_startwork, transcode_time_success, transcode_time_error)

> 3. Do you notice any consistent pattern here? For example, are foreign
> keys involved?

It does not look like it:

[xiatian(at)freebsd ~]$ pg_dump -U azurlane_wiki -d azurlane_wiki -s | grep
-i FOREIGN
ADD CONSTRAINT account_credentials_acd_user_fkey FOREIGN KEY
(acd_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL;
ADD CONSTRAINT account_requests_acr_user_fkey FOREIGN KEY
(acr_user) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL;
ADD CONSTRAINT cu_changes_cuc_page_id_fkey FOREIGN KEY
(cuc_page_id) REFERENCES mediawiki.page(page_id) ON DELETE SET NULL;
ADD CONSTRAINT cu_changes_cuc_user_fkey FOREIGN KEY (cuc_user)
REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL;
ADD CONSTRAINT cu_log_cul_target_id_fkey FOREIGN KEY
(cul_target_id) REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL;
ADD CONSTRAINT cu_log_cul_user_fkey FOREIGN KEY (cul_user)
REFERENCES mediawiki.mwuser(user_id) ON DELETE SET NULL;
ADD CONSTRAINT revision_rev_page_fkey FOREIGN KEY (rev_page)
REFERENCES mediawiki.page(page_id) ON DELETE CASCADE DEFERRABLE
INITIALLY DEFERRED;
ADD CONSTRAINT user_newtalk_user_id_fkey FOREIGN KEY (user_id)
REFERENCES mediawiki.mwuser(user_id) ON DELETE CASCADE DEFERRABLE
INITIALLY DEFERRED;

> 4. How do the table or tables tend to get updated?

Regarding page:
New rows are added rarely, usually once a week or two, in small batches
(separate transactions).
The page_title field is almost never updated (pages are moved extremely
rarely).
Updates to other fields, like page_len, happen multiple times daily,
during page edits, and sometimes happen in large batches but as separate
transactions.

Regarding transcode:
I assume inserts happen with each file upload, often in large batches
(separate transactions). Updates happen whenever a transcode job runs,
which is usually also in batches.

I can check the wiki's recent changes and upload log for more precise data.

> Also:
> 5. Do you use CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY much?>

I assume not, `find . -name "*.php" | xargs grep -i "CONCURRENTLY"`
against MediaWiki includes/ gives no SQL results, source code comments only.

> If my hypothesis is true, then we might expect problems to not even go
> away following a REINDEX. Or maybe they would go away sometimes, but
> not other times.

REINDEX *seems* to have helped in my case (tested with both
page_main_title and the three that showed up in pg_amcheck output). I am
considering writing a script that would SELECT COUNT(*) for each
page_title to detect the next possible page_main_title index corruption.

--
K. R.