MIME-Version: 1.0
References: <78d824e0-b21e-480d-a252-e4b84bc2c24b@illuminatedcomputing.com>
 <69f0a887-a4ed-49cf-9d63-923891006658@illuminatedcomputing.com>
 <CACJufxE8BePAtzoedMo5Fyznif_6vWy0-aq7WjaDd09nd6k4YQ@mail.gmail.com>
 <CACJufxEE_dma4zLh2ydfAfRQPWmJ=M6iV2J-T20BpXm9V0Dzvw@mail.gmail.com>
 <CACJufxF5W9XUdFcYayyfuKBcxw830q=B0xuA-SYBo7XyMULZrg@mail.gmail.com>
 <CACJufxE3U650j=acBuksB+D9GoGVwU0Q2q+7b-BiF6muyp8_qA@mail.gmail.com> <216627.1717791128@sss.pgh.pa.us>
In-Reply-To: <216627.1717791128@sss.pgh.pa.us>
From: jian he <jian.universality@gmail.com>
Date: Sat, 8 Jun 2024 12:14:53 +0800
Message-ID: <CACJufxHNvXvC+2SgqNN2Y8QEfkD3VjR_47KiUGKyKcJcqeeAnw@mail.gmail.com>
Subject: Re: altering a column's collation leaves an invalid foreign key
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Paul Jungwirth <pj@illuminatedcomputing.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxHNvXvC%2B2SgqNN2Y8QEfkD3VjR_47KiUGKyKcJcqeeAnw%40mail.gmail.com>
Precedence: bulk

On Sat, Jun 8, 2024 at 4:12=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> jian he <jian.universality@gmail.com> writes:
> >> * in TryReuseForeignKey, we can pass the information that our primary
> >> key old collation is nondeterministic
> >> and old collation !=3D new collation to the foreign key constraint.
>
> I have a basic question about this: why are we allowing FKs to be
> based on nondeterministic collations at all?  ISTM that that breaks
> the assumption that there is exactly one referenced row for any
> referencing row.
>

for FKs nondeterministic,
I think that would require the PRIMARY KEY collation to not be
indeterministic also.

for example:
CREATE COLLATION ignore_accent_case (provider =3D icu, deterministic =3D
false, locale =3D 'und-u-ks-level1');
DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE ignore_accent_case PRIMARY KEY);
CREATE TABLE fktable (x text REFERENCES pktable on update cascade on
delete cascade);
INSERT INTO pktable VALUES ('A');
INSERT INTO fktable VALUES ('a');
INSERT INTO fktable VALUES ('A');
update pktable set x  =3D '=C3=85';
table fktable;


if FK is nondeterministic, then it looks PK more like FK.
the following example, one FK row is referenced by two PK rows.

DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE "C" PRIMARY KEY);
CREATE TABLE fktable (x text COLLATE ignore_accent_case REFERENCES
pktable on update cascade on delete cascade);
INSERT INTO pktable VALUES ('A'), ('=C3=85');
INSERT INTO fktable VALUES ('A');

begin; delete from pktable where x =3D '=C3=85'; TABLE fktable; rollback;
begin; delete from pktable where x =3D 'A'; TABLE fktable; rollback;