Corrupt indexes in PG12, how do I fix?

#<Tag:0x00005607bb92cf48
 id: 1710,
 name: "chronicillness",
 topic_count: 0,
 created_at: Tue, 03 Dec 2019 17:49:17 UTC +00:00,
 updated_at: Tue, 03 Dec 2019 17:49:17 UTC +00:00,
 pm_topic_count: 0,
 target_tag_id: nil>

Why do I feel like it was a dwarf that wasn’t associated with anything?

Deleting the tag brought more stuff!

discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING:  cannot reindex invalid index "public.tags_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_tags_on_name_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_tags_on_lower_name_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_309322_index_ccnew" concurrently, skipping
ERROR:  could not create unique index "index_tags_on_name_ccnew1"
DETAIL:  Key (name)=(time-management) is duplicated.
1 Like

Same fix :slight_smile: you got to repeat the process.

1 Like

That’s what I was doing. but are those warnings okay? I don’t have to bother about those right?

yeah all warnings are correct, you have a corrupt index, it needs fixing first.

3 Likes

so all copycats are now gone and I’m only left with these warnings while reindexing:

discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING:  cannot reindex invalid index "public.tags_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_tags_on_name_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_tags_on_lower_name_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.tags_pkey_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_tags_on_name_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_tags_on_lower_name_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_309322_index_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_309322_index_ccnew1" concurrently, skipping
REINDEX

is there anything that should be done to get rid of these warnings or we have to live with these.

1 Like

This is confusing to me… we have no index names than end with ccnew … did you create these by hand?

1 Like

I think those are used while migration from postgres 10 to 12 as I’ve seen these in almost every user whose indexes have had issues. E.g.
https://meta.discourse.org/t/postgresql-12-update/151236/208?u=itsbhanusharma

https://meta.discourse.org/t/postgresql-12-update/151236/237?u=itsbhanusharma

EDIT: comes from postgres itself

The recommended recovery method in such cases is to drop the invalid index and try again to perform REINDEX CONCURRENTLY . The concurrent index created during the processing has a name ending in the suffix ccnew , or ccold if it is an old index definition which we failed to drop. Invalid indexes can be dropped using DROP INDEX , including invalid toast indexes.

4 Likes

ouch … are the proper indexes as well already on the table?

You can list the indexes on the table using data exporer I think.

If the correct ones are there, then you can just junk these problem indexes.

2 Likes

Let me check. I’ll report back

1 Like

So the Indexes seem to exist

Duplicate indexes also exist for problem indexes with added ccnew or ccnew1

image

image

I don’t know a way to check if those indexes are valid but if nuking them is an option, I’ll be happy to nuke and reindex.

EDIT: will DROP INDEX work as suggested here ?

2 Likes

Yeah drop them all … no idea how they go there … DROP INDEX is what you want to use.

./launcher enter app
rails c
DB.exec('drop index tags_pkey_ccnew1')
5 Likes

I’m just speculating but here is my tinfoil theory:

I ran Reindex concurrently twice (so first ccnew, ccnew1)
and both the times it presented errors.

maybe when it fails, it just doesn’t revert, it just leaves the garbage and quits.

8 Likes

This sounds very very very likely.

4 Likes

Just adding my $0.02 here,

Toast indexes can’t be dropped this way,
I had to:

su postgres
psql 
\connect discourse
drop index pg_toast.pg_toast_309322_index_ccnew;
drop index pg_toast.pg_toast_309322_index_ccnew1;

The above just applies to pg toast because discourse user does not have access to that index.
PG::InsufficientPrivilege: ERROR: permission denied for schema pg_toast

5 Likes

dropping toast indexes is super scary. I guess you have no choice here though. Yeah you need to do that from psql.

5 Likes

and here comes the happy reindex:

discourse=# REINDEX SCHEMA CONCURRENTLY public;
REINDEX

:partying_face:

6 Likes

Great catch! I found all indices with “ccnew” in the name via this query.

psql
\connect discourse
select tablename,indexname,indexdef from pg_indexes where indexname LIKE '%ccnew%';

incoming_referer.csv (7.2 KB)

Turned out I had a whopping 30 of them, all on the incoming_referers table. So I verified all the ccnew indices were actually duplicates via the indexdef column in this query.

select indexname,indexdef from pg_indexes where tablename = 'incoming_referers';

ccnew.csv (6.6 KB)

And then dropped all of them successfully.

DROP INDEX incoming_referers_pkey_ccnew;
DROP INDEX incoming_referers_pkey_ccnew_ccnew;
DROP INDEX incoming_referers_pkey_ccnew1;
...and so on for all 30

At that point I reindexed the whole schema again and it again was unable to rebuild two of the same incoming_referers ccnew indices, and also found three pg_toast indices. I dropped them and then reindexed the whole schema yet again, again more errors, found a bunch more ccnew indices in the discourse schema, reindexed a third time…

I can’t get a full reindex to complete without errors, it keeps creating and then failing to rebuild new ccnew indexes every time. After 4 full rebuilds I deleted the ccnew indexes then gave up on it. I guess I could try to rebuild non-concurrently but that would cause a bunch of downtime.

Anyway, my guess is most users upgrading from PG10 to 12 who tried to reindex afterwards have these extra ccnew indexes and they should all be deleted. They’ll just take up space and multiply disk write I/O for no benefit.

6 Likes

I ran it’s equivalent in Data-Explorer. was slightly easier to manage.

What I did was that I took all the ccnew/ccnew1/ccnew2…ccnewn indexes, nuked them and reindexed. That fixed it for me.

ccnew is a postgres thing of marking the indexes and I think it is some inefficiency in the process that causes these to be left if an indexing fails for some reason.

I can only suggest you first work upon the root cause of the problem. I had duplicated tags that I had to delete before proceeding to nuking indexes. if I was left with even a single duplicated tag, it will not index properly and create another ccnew'n' index and fail.

2 Likes

You’re right, I still have duplicates-- can’t rebuild the non-ccnew indexes non-concurrently. I need to remove these duplicate rows.

discourse=# reindex index index_incoming_referers_on_path_and_incoming_domain_id;
ERROR:  could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
DETAIL:  Key (path, incoming_domain_id)=(/search/, 3433) is duplicated.
discourse=# reindex index index_incoming_referers_on_path_and_incoming_domain_id;
ERROR:  could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
DETAIL:  Key (path, incoming_domain_id)=(/search/, 1861) is duplicated.

What’s really weird is I only see one row with each of those incoming_domain_id values in incoming_referer. So why are they duplicates?

discourse=# select * from incoming_referers where path='/search/' AND incoming_domain_id IN (1861,3433);
  id   |   path   | incoming_domain_id 
-------+----------+--------------------
 42845 | /search/ |               1861
 40763 | /search/ |               3433
(2 rows)

@sam or @riking should I delete these two rows as follows:

DELETE FROM incoming_referers WHERE path='/search/' AND incoming_domain_id IN (1861,3433);

… I guess I’m learning postgres after all, heh.