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.