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

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.