Re: altering a column's collation leaves an invalid foreign key - Mailing list pgsql-hackers

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.