I just upgraded a 1.8 million post site to 2.7.0.beta2 and with it the PG10 to PG13 upgrade. I saw that beta3 just came out and has “improved database migration performance” so I went ahead and upgraded again.
When I try to do a
reindex concurrently, I get
WARNING: cannot reindex invalid index "public.allowed_pm_users_pkey_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_allowed_pm_users_on_user_id_and_allowed_pm_user_id_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_allowed_pm_users_on_allowed_pm_user_id_and_user_id_ccnew" concurrently, skipping
I was going to drop and reindex those, but I don’t see any
ccnew in the current source, so I’m not sure what to do.
Looks like the
_ccnew index is part of how the concurrent reindex happens:
- Create a new index in the catalogs which is a copycat of the one reindexed (with some exceptions, for example partition indexes don’t have their inheritance dependency registered at creation, but at swap time). This new, temporary is suffixed with “_ccnew”. Bref.
Can you reindex those indexes without the
or, from later in that post:
Then, REINDEX TABLE CONCURRENTLY will skip invalid indexes because in the event of successive and multiple failures then the number of indexes would just ramp up, doubling at each run, causing a lot of bloat on the follow-up reindex operations:
It is however possible to reindex invalid indexes with just REINDEX INDEX CONCURRENTLY:
_ccnew are indexes that were tried to be created by a previous
reindex concurrently but couldn’t be done, usually because they are violating a uniqueness check. The failed attempts to
reindex concurrently will sit in there and should be dropped manually.
The second time you run
reindex concurrently, PostgreSQL will skip those failure artifacts.
Thanks Rafael! That did it. I
dropped those indexes and did another concurrent reindex with no issues.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.