Check for non-deterministic FK collations before upgrade

From: Kirill Reshke <reshkekirill(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Check for non-deterministic FK collations before upgrade
Date: 2026-10-03 17:32:33
Message-ID: CALdSSPhTRixbaTADO87Gy+BK0_HaS_6N1E9pNfp35DnMBdO4-w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Check-for-foreign-key-with-non-deterministic-coll.patch application/octet-stream 11.1 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2026-10-03 18:22:12 Re: Regression tests failures due to concurrent grants
Previous Message Pierre Forstmann 2026-10-03 16:58:08 Re: How to run test coverage on Debian 13 ?