Corrupt indexes in PG12, how do I fix?

Sorry for bumping this once again, I got a very strange

discourse=# REINDEX SCHEMA CONCURRENTLY public;
ERROR:  could not create unique index "index_tags_on_name_ccnew"
DETAIL:  Key (name)=(chronicillness) is duplicated.

I’m assuming it can be fixed by the solution suggested above by @riking but I’m unable to figure out how to modify the syntax to suit my case. :frowning:

1 Like

See:

So you have 2 things you can do if you see this error:

  1. You can work around the issue … in this case the table tags has a duplicate row with the name chronicillness

    1. Do a data explorer query searching for the rows select * from tags where name = 'chronicillness

    2. Delete the duplicate:

      ./launcher enter app
      rails c
      Tag.find_by(id: ID_YOU_FOUND_IN_DATA_EXPLORER).destroy   
      
  2. If you have a backup of your database and details you would like to share with Peter… share with Peter either privately or on the PG mailing list.

8 Likes

So I have a discourse generated backup of postgres 10. will that be helpful? can I Just pull the postgres part from the archive and send that?

I am no sure… what Peter would likely need is for you to stop your database and copy the entire contents of the folder containing the database.

Best way to find out is email him.

2 Likes

Sure, I’ll drop him an email to figure this out :slight_smile:

NB: I got this when I ran the SQL in data explorer

id name topic_count created_at updated_at pm_topic_count target_tag
1710 chronicillness 2 2019-12-03T17:49:17.395Z 2019-12-03T17:49:17.395Z 0 NULL

will

Tag.find_by(id: 1710).destroy

do the trick?

2 Likes

oh … the index is on LOWER(name)

Run the query like so:

select * from tags where name ilike 'chronicillness'

You should be getting 2 rows.

2 Likes
select * from tags where name ilike 'chronicillness'
id name topic_count created_at updated_at pm_topic_count target_tag
329 chronicillness 12 2017-08-22T00:17:38.824Z 2017-08-22T00:17:38.824Z 0 NULL
1710 chronicillness 2 2019-12-03T17:49:17.395Z 2019-12-03T17:49:17.395Z 0 NULL

could it be a - in between tag names?
i see two tags: chronicillness and chronic-illness here.

1 Like

Well then that is the issue … I guess you delete 1710. 2 topics will be missing that tag afterwards.

3 Likes
#<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.

EDIT: comes from postgres itself
https://www.postgresql.org/docs/12/sql-reindex.html

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')
4 Likes