From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: nat@makarevitch.org
Subject: BUG #18665: Breaking a foreign constraint: the error message may be more detailed
Date: Mon, 21 Oct 2024 01:51:22 +0000
Message-ID: <18665-95be0e49a07d758c@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18665
Logged by: Nat Makarevitch
Email address: nat@makarevitch.org
PostgreSQL version: 17.0
Operating system: Linux
Description:
A query breaking a foreign key triggers a "cannot truncate a table
referenced in a foreign key constraint" error. In the DETAIL section of
this message the child table name isn't fully qualified: it omits its schema
name.
Stating this schema name would be useful. Case in point: the child table is
in a schema not named in the SEARCH_PATH and another child table bearing the
same name and foreign key exists in a SCHEMA stated in the SEARCH_PATH.
Suggestion: in this DETAIL string please show the complete child's table
name (=> prefixed by its schema name).
To exhibit the gain:
show search_path;
create schema not_in_searchpath ;
create table public.parent (id integer generated by default as identity
primary key, name text); create table public.child(name_id integer
references public.parent(id), nickname text);
create table not_in_searchpath.child(name_id integer references
public.parent(id), nickname text); insert into public.parent(name) values
('Foo'); select * from public.parent; truncate public.parent;
Corresponding session (commented):
search_path
═════════════
public
(1 row)
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
id │ name
════╪══════
1 │ Foo
(1 row)
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "child" references "parent".
HINT: Truncate table "child" at the same time, or use TRUNCATE ...
CASCADE.
table child;
name_id │ nickname
═════════╪══════════
(0 rows)
-- at this stage I'm baffled: "child" (this is public.child!) is empty. The
culprit is the not_in_searchpath.child table , and the DETAIL section of
the error message would be more helpful to me by stating it.
Thank you
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, nat@makarevitch.org
Subject: Re: BUG #18665: Breaking a foreign constraint: the error message may be more detailed
In-Reply-To: <18665-95be0e49a07d758c@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