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
