From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: marcus@cockroachlabs.com
Subject: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types
Date: Mon, 01 Nov 2021 21:09:00 +0000
Message-ID: <17261-b27dbaa13eba2220@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, marcus@cockroachlabs.com
Subject: Re: BUG #17261: FK ON UPDATE CASCADE can break referential integrity with columns of different types
In-Reply-To: <17261-b27dbaa13eba2220@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox