Upgrade failed PG::UniqueViolation -- could not create unique index "unique_post_links_ccnew"

AI-generated summary

The discussion revolves around a PG::UniqueViolation error during an upgrade to 2.8.0.beta2, specifically related to creating a unique index on post_links. pfaffman encountered the issue, which was traced to duplicate URLs in the TopicLink table. A workaround involved appending badX to duplicates, though concerns about future implications were raised. codinghorror noted that such index corruption bugs were common in older PostgreSQL versions and suggested upgrading to PG12 or PG13 resolved the issue for many. sam confirmed the problem was addressed in their infrastructure, attributing it to safety issues in the upgrade script for PostgreSQL, particularly related to libc changes between versions. While the issue is now largely resolved in the broader ecosystem, legacy upgrades may still face it, and improving the upgrade script remains a challenge.

I’ve got a standard install that is failing to upgrade to 2.8.0.beta2 with this error:

PG::UniqueViolation: ERROR:  could not create unique index "unique_post_links_ccnew"

it’s from

I, [2021-06-25T18:35:47.756688 #1]  INFO -- : == 20210525112226 RemoveLengthConstrainFromTopicLinkUrl: migrating ============

It doesn’t look like a corrupt index.

Does this help?

[6] pry(main)> TopicLink.where(url: bad_url).pluck(:id, :topic_id, :post_id, :user_id)
=> [[15733, 14351, 63616, 911], [16131, 14505, 63986, 911], [27141, 14505, 63986, 1152]]

I ended up just appending a badX to the duplicate URLs and then it worked. I’m not quite clear what problems this might generate in the future…

Lots of so-called “impossible” index corruption bugs in older versions of Postgres, in our experience. Are you still seeing this?

I think that I haven’t seen it since upgrading to pg12.

We went all the way to PG13, and I think that mostly solved it (corrupt indexes) for us @sam?

@saj has gotten to the bottom of this issue. We resolved it in our infra. Most self hosters have since worked around it.

The mechanism we used in the past to upgrade PG in our open source image had some inherit safety issues due to libc changing under the hood between PG versions.

I think the issue is now mostly gone in the wider ecosystem, though some old upgrades may still face it. It is going to be a nightmare fixing our existing open source upgrade script and deal with the enormous matrix of PG upgrades.

That said at least we are very aware of the source of the issue now and are better poised to address it going forward.