Thread

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

Kirill Reshke <reshkekirill@gmail.com> — 2025-12-12T09:00:52Z

On Fri, 12 Dec 2025 at 13:33, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19352
> Logged by:          David Keeshin
> Email address:      keeshd@keeshinds.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