Feedback-ID: ie0a040ee:Fastmail
Message-ID: <a876bf93-61c6-4715-9faa-cda85b78c1f9@eisentraut.org>
Date: Tue, 18 Jun 2024 10:50:24 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: altering a column's collation leaves an invalid foreign key
To: jian he <jian.universality@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Paul Jungwirth <pj@illuminatedcomputing.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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>
 <CACJufxHNvXvC+2SgqNN2Y8QEfkD3VjR_47KiUGKyKcJcqeeAnw@mail.gmail.com>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <CACJufxHNvXvC+2SgqNN2Y8QEfkD3VjR_47KiUGKyKcJcqeeAnw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a876bf93-61c6-4715-9faa-cda85b78c1f9%40eisentraut.org>
Precedence: bulk

On 08.06.24 06:14, jian he wrote:
> 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'), ('Å');
> INSERT INTO fktable VALUES ('A');

Yes, this is a problem.  The RI checks are done with the collation of 
the primary key.

The comment at ri_GenerateQualCollation() says "the SQL standard 
specifies that RI comparisons should use the referenced column's 
collation".  But this is not what it says in my current copy.

... [ digs around ISO archives ] ...

Yes, this was changed in SQL:2016 to require the collation on the PK 
side and the FK side to match at constraint creation time.  The argument 
made is exactly the same we have here.  This was actually already the 
rule in SQL:1999 but was then relaxed in SQL:2003 and then changed back 
because it was a mistake.

We probably don't need to enforce this for deterministic collations, 
which would preserve some backward compatibility.

I'll think some more about what steps to take to solve this and what to 
do about back branches etc.