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