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

[pfaffman (Jay Pfaffman)]

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…

[codinghorror (Jeff Atwood)]

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

[pfaffman (Jay Pfaffman)]

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

[codinghorror (Jeff Atwood)]

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

[sam (Sam Saffron)]

@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.