I came across an edge case in how our foreign key implementation works
that I think is not SQL conforming. It has to do with how updates to
values that "look" different but compare as equal are cascaded. A
simple case involves float -0 vs. 0, but relevant cases also arise with
citext and case-insensitive collations.
Consider this example:
create table pktable2 (a float8, b float8, primary key (a, b));
create table fktable2 (x float8, y float8,
foreign key (x, y) references pktable2 (a, b) on update cascade);
insert into pktable2 values ('-0', '-0');
insert into fktable2 values ('-0', '-0');
update pktable2 set a = '0' where a = '-0';
What happens now?
select * from pktable2;
a | b
---+----
0 | -0
(1 row)
-- buggy: did not update fktable2.x
select * from fktable2;
x | y
----+----
-0 | -0
(1 row)
This happens because ri_KeysEqual() compares the old and new rows and
decides that because they are "equal", the ON UPDATE actions don't need
to be run.
The SQL standard seems clear that ON CASCADE UPDATE means that an
analogous UPDATE should be run on matching rows in the foreign key
table, so the current behavior is wrong.
Moreover, if another column is also updated, like update pktable2 set a
= '0', b = '5', then the old and new rows are no longer equal, and so x
will get updated in fktable2. So the context creates inconsistencies.
The fix is that in these cases we have ri_KeysEqual() use a more
low-level form of equality, like for example record_image_eq does. In
fact, we can take the same code. See attached patches.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
