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

On 14.11.24 03:21, jian he wrote:
> On Wed, Nov 13, 2024 at 8:56 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>>
>> https://stackoverflow.com/questions/14921668/difference-between-restrict-and-no-action
>> mentioned about the difference between "no action" and "restrict".
>>
>> RI_FKey_restrict_upd comments also says:
>>
>> * The SQL standard intends that this referential action occur exactly when
>> * the update is performed, rather than after. This appears to be
>> * the only difference between "NO ACTION" and "RESTRICT". In Postgres
>> * we still implement this as an AFTER trigger, but it's non-deferrable.
>>
>> 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';
>
> my previous email was too verbose.
> my point is this: given PK, FK both are case_insensitive. and
> PK side values are all being referenced.
>
> case like `update pktable set x = 'a' where x = 'A';`
> Do we consider PK side value changed?
> it seems not mentioned anywhere.

I think the PostgreSQL documentation is selling the difference between
NO ACTION and RESTRICT a bit short. The SQL standard says this:

> ON UPDATE RESTRICT: any change to a referenced column in the
> referenced table is prohibited if there is a matching row.

and

> If a non-null value of a referenced column RC in the referenced table
> is updated to a value that is distinct from the current value of RC,
> then, [...] If UR specifies RESTRICT and there exists some matching
> row, then an exception con- dition is raised [...]

So this is exactly what is happening here.

You can also reproduce this with things that are not strings with
collations. You just need to find a type that has values that are
"equal" but "distinct", which is not common, but it exists, for example
0.0 and -0.0 in floats. Example:

create table parent (val float8 primary key);
insert into parent values ('0.0');

create table child (id int, val float8 references parent (val));

insert into child values (1, '0.0');
insert into child values (2, '-0.0');

update parent set val = '-0.0'; -- ok with NO ACTION

but

create table child (id int, val float8 references parent (val) on
update restrict);

insert into child values (1, '0.0');
insert into child values (2, '-0.0');

update parent set val = '-0.0'; -- error with RESTRICT

So this is a meaningful difference.

There is also a bug here in that the update in the case of NO ACTION
doesn't actually run, because it thinks the values are the same and the
update can be skipped.

I think there is room for improvement here, in the documentation, the
tests, and maybe in the code. And while these are thematically related
to this thread, they are actually separate issues.

I propose that I go ahead with committing the v7 patch (with your typo
fixes) and then we continue discussing these other issues afterwards in
a separate thread.

Given this overall picture, I'm also less and less inclined to do any
backpatching bug fixing here. The status of this feature combination in
the backbranches is just "don't push it too hard". Maybe someone has
some feasible mitigation ideas, but I haven't seen any yet.