Thread: BUG #18665: Breaking a foreign constraint: the error message may be more detailed

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

PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

By and large, we don't include objects' schema names in error messages
ever.  I'm not sure why this specific one should break that habit.

If you want to (re)start a conversation about whether more error
messages should include schema names, you can ... but a bug report
is not the mechanism for doing that.  This is not a bug, it's
acting as designed and in keeping with a lot of other code.

            regards, tom lane