The following bug has been logged on the website:
Bug reference: 17261
Logged by: Marcus Gartner
Email address: marcus@cockroachlabs.com
PostgreSQL version: 14.0
Operating system: macOS Big Sur 11.6
Description:
It is possible to break foreign key referential integrity when the FK
columns have different types and updates are cascaded from the parent
relation to the child relation. As far as I can tell from the documentation
on FKs
(https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK)
this behavior is not expected. The example below shows how to reproduce the
issue.
This behavior is present on 14.0 and 13.3. I did not test any other
versions.
-- To reproduce:
CREATE TABLE p (d DECIMAL(10, 2) PRIMARY KEY);
CREATE TABLE c (d DECIMAL(10, 0) REFERENCES p(d) ON UPDATE CASCADE);
INSERT INTO p VALUES (1.00);
INSERT INTO c VALUES (1);
-- Update the parent row value to 1.45.
UPDATE p SET d = 1.45 WHERE d = 1.00;
SELECT * FROM p;
-- d
-- ------
-- 1.45
-- The FK constraint integrity is not upheld.
-- I would expect the update to have failed, because 1 (the
-- value of the assignment cast from 1.45 to DECIMAL(10, 0))
-- does not exist in p.
SELECT * FROM c;
-- d
-- ---
-- 1
