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	2196	December 17, 2020
Import failing with `could not create unique index` Installation	6	2654	July 13, 2020
Upgrade Issues: Failed Upgrade due to Duplicate Key, Failed Snapshot Restore Installation	7	1616	April 25, 2023
Corrupt indexes in PG12, how do I fix? Installation	44	10789	January 26, 2023
Forum offline due to failed rebuilds on Tests-Pass Installation	1	893	December 11, 2020

PG13 compatibility issue 🔥

Related Topics