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.
