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

On 19.11.24 17:27, Peter Eisentraut wrote:
> On 14.11.24 09:04, Peter Eisentraut wrote:
>> 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.
>
> Back to this.  First, there is no bug above.  This is all working
> correctly, I was just confused.
>
> I made a few patches to clarify this:
>
> 1. We were using the wrong error code for RESTRICT.  A RESTRICT
> violation is not the same as a foreign-key violation.  (The foreign key
> might in theory still be satisfied, but RESTRICT prevents the action
> anyway.)  I fixed that.
>
> 2. Added some tests to illustrate all of this (similar to above).  I
> used case-insensitive collations, which I think is easiest to
> understand, but there is nothing special about that.
>
> 3. Some documentation updates to explain some of the differences between
> NO ACTION and RESTRICT better.

I have committed these patches. I think that concludes this thread.