From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: yannsalaun1@gmail.com
Subject: BUG #17945: Different order of definition of a constraint causes constraint violation
Date: Thu, 25 May 2023 14:39:13 +0000
Message-ID: <17945-e6eb09170cd63524@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17945
Logged by: Yann Salaün
Email address: yannsalaun1@gmail.com
PostgreSQL version: 15.3
Operating system: Linux
Description:
This is a bug report with a reproducible case where different orders of
definition of a SQL constraint causes different behavior.
To be more specific, if the constraint is defined inside the table
definition as in
```sql
CREATE TABLE node (
id integer PRIMARY KEY,
parent_id integer REFERENCES node(id) ON DELETE CASCADE
);
```
the behavior is not the same as when the constraint is defined after all
tables are defined (like in the output of pg_dump) as in
```sql
CREATE TABLE node (
id integer PRIMARY KEY,
parent_id integer
);
-- define all other tables...
ALTER TABLE node
ADD CONSTRAINT nodes_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
node(id) ON DELETE CASCADE;
```
The reproductible case comes from an application with tables modelling
filesystem trees. When a parent node is deleted, children are deleted in
cascade via foreign key constraints.
Here is the full psql script to reproduce the problem:
```sql
-- Schema definition
CREATE TABLE node (
id integer PRIMARY KEY,
parent_id integer
);
CREATE TABLE dir (
id integer PRIMARY KEY REFERENCES node(id) ON DELETE CASCADE
);
CREATE TABLE file (
id integer PRIMARY KEY REFERENCES node(id) ON DELETE CASCADE
);
CREATE TABLE t (
main_dir_id integer REFERENCES dir(id) ON DELETE SET NULL,
main_file_id integer REFERENCES file(id) ON DELETE SET NULL,
other_file_id integer REFERENCES file(id) ON DELETE SET NULL
);
-- Constraint definition. If we inline this constraint in the table
definition, the SQL error below disappears.
ALTER TABLE node
ADD CONSTRAINT nodes_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
node(id) ON DELETE CASCADE;
-- Data insertion
-- main_dir
INSERT INTO node (id) VALUES (1);
INSERT INTO dir (id) VALUES (1);
-- main_file in main_dir
INSERT INTO node (id, parent_id) VALUES (2, 1);
INSERT INTO file (id) VALUES (2);
-- other_file
INSERT INTO node (id) VALUES (3);
INSERT INTO file (id) VALUES (3);
INSERT INTO t (main_dir_id, main_file_id, other_file_id) VALUES (1, 2, 3);
-- Data deletion in a transaction.
BEGIN;
-- First, delete other_file (this sets t.other_file_id to NULL via ON DELETE
SET NULL)
DELETE FROM node WHERE id = 3;
-- Then delete main_dir (this sets t.main_dir_id and t.main_file_id to NULL
via ON DELETE SET NULL)
DELETE FROM node WHERE id = 1;
-- The second DELETE statement returns the following error.
-- ERROR: 23503: insert or update on table "t" violates foreign key
constraint "t_main_file_id_fkey"
-- DETAIL: Key (main_file_id)=(2) is not present in table "file".
-- SCHEMA NAME: public
-- TABLE NAME: t
-- CONSTRAINT NAME: t_main_file_id_fkey
-- LOCATION: ri_ReportViolation, ri_triggers.c:2528
ROLLBACK;
```
I believe this psql script is sufficient to reproduce the bug. Let me know
if that's not the case, I would be happy to provide more details.
Thanks for your answer.
Yann
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, yannsalaun1@gmail.com
Subject: Re: BUG #17945: Different order of definition of a constraint causes constraint violation
In-Reply-To: <17945-e6eb09170cd63524@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