From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: denisvermylen@gmail.com
Subject: BUG #17223: Foreign key SET NULL depends on constraints order
Date: Tue, 12 Oct 2021 13:58:41 +0000
Message-ID: <17223-2c62a8409b46f961@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17223
Logged by: Denis Vermylen
Email address: denisvermylen@gmail.com
PostgreSQL version: 12.8
Operating system: Ubuntu 20.04
Description:
Hello,
I've encountered an interesting issue where it turned out the order of
foreign key constraints creation mattered in allowing a certain transaction
or not.
The simplified test case:
```
root@me:~# createdb test
root@me:~# psql test --quiet -c "
CREATE TABLE branch (
id int PRIMARY KEY
);
CREATE TABLE commit (
id int PRIMARY KEY,
branch_id int,
CONSTRAINT commit_branch_fk FOREIGN KEY(branch_id) REFERENCES branch(id)
ON DELETE CASCADE
);
CREATE TABLE build (
id int PRIMARY KEY,
name varchar(100),
branch_id int,
commit_id int,
CONSTRAINT build_commit_fk FOREIGN KEY(commit_id) REFERENCES commit(id)
ON DELETE SET NULL,
CONSTRAINT build_branch_fk FOREIGN KEY(branch_id) REFERENCES branch(id)
ON DELETE SET NULL
);
INSERT INTO branch VALUES (1);
INSERT INTO commit VALUES (1, 1);
INSERT INTO build VALUES (1, 'one', 1, 1);"
root@me:~# psql test -c "BEGIN;UPDATE build SET name = 'one';DELETE FROM
branch;ROLLBACK;"
```
The last transaction here works fine because the order of the foreign keys
was compatible. Should we have added the foreign keys afterwards in the
reverse order, or if we drop & re-add the `commit_branch_fk` constraint,
doing the same transaction no longer works:
```
root@me:~# psql test --quiet -c "
ALTER TABLE commit DROP CONSTRAINT commit_branch_fk;
ALTER TABLE commit ADD CONSTRAINT commit_branch_fk FOREIGN KEY (branch_id)
REFERENCES branch(id) ON DELETE CASCADE;"
root@me:~# psql test -c "BEGIN;UPDATE build SET name = 'one';DELETE FROM
branch;ROLLBACK;"
ERROR: insert or update on table "build" violates foreign key constraint
"build_commit_fk"
DETAIL: Key (commit_id)=(1) is not present in table "commit".
```
From a user's perspective, the error doesn't make sense. The key is not
present, but it's supposed to be set to NULL according to the FK constraint,
why does it raise an error? I also couldn't find any documentation on FK
constraints order being relevant in the documentation.
Side-note: Doing a plain-text dump can also modify the order of the
constraints as it seems to order them alphabetically. With these constraint
names dumping the DB without the issue and restoring it introduces the
problem.
```
root@me:~# createdb test2 && pg_dump test | psql test2 --quiet
root@me:~# psql test2 -c "BEGIN;UPDATE build SET name = 'one' WHERE id =
1;DELETE FROM branch WHERE id = 1;ROLLBACK;"
ERROR: insert or update on table "build" violates foreign key constraint
"build_commit_fk"
DETAIL: Key (commit_id)=(1) is not present in table "commit".
```
Thanks for reading,
Denis
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, denisvermylen@gmail.com
Subject: Re: BUG #17223: Foreign key SET NULL depends on constraints order
In-Reply-To: <17223-2c62a8409b46f961@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