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.