Обсуждение: BUG #19352: SQL Error messages do not include schema name along with table or data object name.

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"

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

Actually, these error reports do include a schema name among other
things -- it's just hidden from view.  For psql it's very easy to make
them visible, just use

  \set VERBOSITY verbose

Then the error message includes more detail:

=# insert into sh.t2 values (1);
ERROR:  23503: insert or update on table "t2" violates foreign key constraint "t2_i_fkey"
DETAIL:  Key (i)=(1) is not present in table "t".
SCHEMA NAME:  sh
TABLE NAME:  t2
CONSTRAINT NAME:  t2_i_fkey
LOCATION:  ri_ReportViolation, ri_triggers.c:2770

Other clients would have their own ways to display that information.

Kirill Reshke wrote:

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

You're not foolish, but the schema name is attached to the table.
You're correct that there can be more than one constraint with the same
name in a schema, but there cannot be more than one constraint with the
same name on the _table_, which is itself uniquely identified by the
schema and its name.

So you could argue that we could improve this by schema-qualifying the
table name (not the constraint name) in this message:

  ERROR:  23503: insert or update on table "sh.t2" violates foreign key constraint "t2_i_fkey"

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"No me acuerdo, pero no es cierto.  No es cierto, y si fuera cierto,
 no me acuerdo."                 (Augusto Pinochet a una corte de justicia)

Kirill Reshke <reshkekirill@gmail.com> writes:
> On Fri, 12 Dec 2025 at 13:33, PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> Ideally this error message would be clearer if the schema name was included
>> with the data table or object name.

> 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.

Yeah, at a guess we'd be talking about touching a couple of thousand
places if we were to apply such a policy uniformly.  This has been
debated in the past and the answer has always come out "no"; not
just because of the work, but because it'd make errors more verbose
but frequently not more helpful.

As Álvaro notes nearby, we do have provisions for reporting associated
schema names as secondary error fields, although I think that's only
been implemented in a few dozen places.

            regards, tom lane