Re: BUG #19352: SQL Error messages do not include schema name along with table or data object name.

On Fri, 12 Dec 2025 at 13:33, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19352
> Logged by: David Keeshin
> Email address: keeshd(at)keeshinds(dot)com
> PostgreSQL version: 16.11
> Operating system: Linux
> Description:
>
> Here's an actual error that I received:
>
> "Error moving summary data: Error in up_move_from_stage for control_id
> c5bcfbd9-1f20-42c1-bb0c-be626b3c15eb:
> insert or update on table "followup_data_flow_by_topic" violates
> foreign key constraint "followup_data_flow_by_topic_by_topic_id_fkey2" 23503
> CONTEXT: PL/pgSQL function interview.up_move_from_stage(uuid,boolean) line
> 166 at RAISE"
>
> It's confusing. I have a "stage" schema and a "interview" schema in the
> database. There is a "followup_data_flow_by_topic' table in the stage
> "schema" and one in the "interview" schema. I did just noticed that the
> end of the message does include the schema name for the stored procedure -
> which by the was created as a stored procedure, not a function.
>
> Ideally this error message would be clearer if the schema name was included
> with the data table or object name. i.e.
>
> "Error moving summary data: Error in interview.up_move_from_stage for
> control_id c5bcfbd9-1f20-42c1-bb0c-be626b3c15eb:
> insert or update on table "interview.followup_data_flow_by_topic" violates
> foreign key constraint
> "interview.followup_data_flow_by_topic_by_topic_id_fkey2" 23503
> CONTEXT: PL/pgSQL function interview.up_move_from_stage(uuid,boolean) line
> 166 at RAISE"
>

Well...

> foreign key constraint
> "interview.followup_data_flow_by_topic_by_topic_id_fkey2" 23503

I may be foolish but constraints are not schema-qualified, they just have names.

checkout this:

```
db1=# create schema sh;
CREATE SCHEMA
db1=# create table sh.t(i int);
CREATE TABLE
db1=# alter table sh.t add constraint c check ( i > 0);
ALTER TABLE
db1=# select conname from pg_constraint where conrelid = 'sh.t'::regclass ;
conname
---------
c
(1 row)
db1=# create table sh.t2 (i int);
CREATE TABLE
db1=# alter table sh.t2 add constraint c check ( i > 0);
ALTER TABLE
db1=# select conname from pg_constraint where conrelid = 'sh.t2'::regclass ;
conname
---------
c
(1 row)
```

So, including schema does not uniquely identify constraints either.

Speaking of fully-qualified relation name in error message:

Code which generates this uses RelationGetRelationName, as well as
many other places where we generate user-facing messages:

(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
errmsg("insert or update on table \"%s\" violates foreign key
constraint \"%s\"",
RelationGetRelationName(fk_rel),
NameStr(riinfo->conname)),

So, if we do anything about this, we need to change all of these
places... This is a big amount of work and would be HEAD-only. So, on
pg16, you will still face this behavior, I guess.

--
Best regards,
Kirill Reshke