PG13 compatibility issue 🔥

ted · October 18, 2024, 4:35am

I have a PR up here that should work for PG13 as well.

github.com/discourse/discourse

FIX: Make problem check tracker unique constraint work on PG13

discourse:main ← discourse:fix/problem-check-target-pg13

opened 03:40AM - 18 Oct 24 UTC

Drenmi

+32 -11

### What is this change? In #29169 we added a `NULLS NOT DISTINCT` option to …the unique index on `problem_check_trackers`. This is to enforce uniqueness even when the `target` is `NULL`. (Postgres considers all `NULL`s to be distinct by default.) However, this only works in PG15. In PG13 it does nothing. This commit adds a default dummy string value `__NULL__` to `target`. Since it's a string, PG13 will be able to correctly identify duplicate records. ### Is it safe to run this? Adding a default value will lock the table and can cause issues on large tables, but the `problem_check_trackers` table is constrained by the number of problem check classes, and is in the ballpark of 10-100 rows.

We’re still considering how realistic this case is in the wild, and how much we should potentially invest in working around it.

Topic		Replies	Views
Backup failed due to PG/SQL errors Installation	9	2210	December 17, 2020
Import failing with `could not create unique index` Installation	6	2663	July 13, 2020
Upgrade Issues: Failed Upgrade due to Duplicate Key, Failed Snapshot Restore Installation	7	1618	April 25, 2023
Corrupt indexes in PG12, how do I fix? Installation	44	10843	January 26, 2023
Forum offline due to failed rebuilds on Tests-Pass Installation	1	895	December 11, 2020

PG13 compatibility issue 🔥

Related topics