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.
