Check for non-deterministic FK collations before upgrade
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254039psql -h localhost -U postgresBuilt from patchset v1 (message #1), October 23, 2026 at 04:24 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254039_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254039_1 && git checkout t254039_1Patchset v1 (message #1) is on t254039_1
Since PG 18/9321d2fdf808, a foreign key with different collations on the key
columns is rejected if either collation is nondeterministic.
Upgrading such cluster would fail.
```
pg_restore: error: could not execute query: ERROR: foreign key
constraint "fk_c_k_fkey" cannot be implemented
DETAIL: Key columns "k" of the referencing table and "k" of the
referenced table have incompatible collations: "C" and "ci_nd". If
either collation is nondeterministic, then both collations have to be
the same.
Command was: ALTER TABLE ONLY "public"."fk_c"
ADD CONSTRAINT "fk_c_k_fkey" FOREIGN KEY ("k") REFERENCES
"public"."fk_p"("k");
```
Add a per-database pre-check of the old cluster reporting exactly the
shapes the new cluster would reject
--
Best regards,
Kirill Reshke
Hi Kirill,
I think this is a useful pre-upgrade check.
or by removing the constraint and recreating it after the
upgrade.
Recreating the same FK after upgrade still fails, even with NOT VALID.
Could we remove that alternative, or clarify that the collations still
need to be made compatible?
Maybe the successful test could keep FKs with identical nondeterministic
collations and with different deterministic collations? Currently it has
no FK left, so rejecting all collated FKs would also pass the test.
Thank you!
Best regards, Andrey Borodin.