pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18665: Breaking a foreign constraint: the error message may be more detailed
2+ messages / 2 participants
[nested] [flat]
* BUG #18665: Breaking a foreign constraint: the error message may be more detailed
@ 2024-10-21 01:51 PG Bug reporting form <noreply@postgresql.org>
2024-10-21 16:06 ` Re: BUG #18665: Breaking a foreign constraint: the error message may be more detailed Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2024-10-21 01:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: nat@makarevitch.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18665: Breaking a foreign constraint: the error message may be more detailed
2024-10-21 01:51 BUG #18665: Breaking a foreign constraint: the error message may be more detailed PG Bug reporting form <noreply@postgresql.org>
@ 2024-10-21 16:06 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-10-21 16:06 UTC (permalink / raw)
To: nat@makarevitch.org; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-10-21 16:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-21 01:51 BUG #18665: Breaking a foreign constraint: the error message may be more detailed PG Bug reporting form <noreply@postgresql.org>
2024-10-21 16:06 ` Tom Lane <tgl@sss.pgh.pa.us>
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