Re: altering a column's collation leaves an invalid foreign key

On 25.10.24 08:23, jian he wrote:
> ri_KeysEqual definitely deserves some comments.
> for rel_is_pk, the equality is collation agnostic;
> for rel_is_pk is false, the equality is collation aware.
>
> for example:
> DROP TABLE IF EXISTS fktable, pktable;
> CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
> CREATE TABLE fktable (x text collate case_insensitive REFERENCES
> pktable on update restrict on delete restrict);
> INSERT INTO pktable VALUES ('A'), ('Å');
> INSERT INTO fktable VALUES ('a');
> update pktable set x = 'a' where x = 'A';
> ERROR: update or delete on table "pktable" violates foreign key
> constraint "fktable_x_fkey" on table "fktable"
> DETAIL: Key (x)=(A) is still referenced from table "fktable".
> this should not happen?

Apparently this is intentional. It's the difference between RESTRICT
and NO ACTION. In ri_restrict(), there is a comment:

/*
* If another PK row now exists providing the old key values, we should
* not do anything. However, this check should only be made in the NO
* ACTION case; in RESTRICT cases we don't wish to allow another
row to be
* substituted.
*/

In any case, this patch does not change this behavior. It exists in old
versions as well.