Re: Error message inconsistency

On Sun, 24 Mar 2019 at 13:02, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Sat, Mar 23, 2019 at 4:33 AM Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)com> wrote:
> >
> > On Fri, Mar 22, 2019 at 2:25 PM Simon Riggs <simon(at)2ndquadrant(dot)com>
> wrote:
> > >
> > > As noted by a PostgreSQL user to me, error messages for NOT NULL
> constraints are inconsistent - they do not mention the relation name in the
> message, as all other variants of this message do. e.g.
> > >
> > > postgres=# create table nn (id integer not null);
> > > CREATE TABLE
> > > postgres=# insert into nn values (NULL);
> > > ERROR: null value in column "id" violates not-null constraint
> > > DETAIL: Failing row contains (null).
> > >
> > > postgres=# create table nn2 (id integer check (id is not null));
> > > CREATE TABLE
> > > postgres=# insert into nn2 values (NULL);
> > > ERROR: new row for relation "nn2" violates check constraint
> "nn2_id_check"
> > > DETAIL: Failing row contains (null).
> > >
> > > I propose the attached patch as a fix, changing the wording (of the
> first case) to
> > > ERROR: null value in column "id" for relation "nn" violates not-null
> constraint
> > >
>
> I think we are inconsistent for a similar message at a few other
> places as well. See, below two messages:
>
> column \"%s\" contains null values
> column \"%s\" of table \"%s\" contains null values
>
> If we decide to change this case, then why not change another place
> which has a similar symptom?
>

Yes, lets do that.

I'm passing on feedback, so if it applies in other cases, I'm happy to
change other common cases also for the benefit of users.

Do you have a list of cases you'd like to see changed?

> > > It causes breakage in multiple tests, which is easy to fix once/if we
> agree to change.
> > >
> >
> > I totally agree with that change because I already get some negative
> feedback from users about this message too.
> >
>
> What kind of negative feedback did you get from users? If I see in
> the log file, the message is displayed as :
>
> 2019-03-24 18:12:49.331 IST [6348] ERROR: null value in column "id"
> violates not-null constraint
> 2019-03-24 18:12:49.331 IST [6348] DETAIL: Failing row contains (null).
> 2019-03-24 18:12:49.331 IST [6348] STATEMENT: insert into nn values
> (NULL);
>
> So, it is not difficult to identify the relation.
>

The user is not shown the failing statement, and if they are, it might have
been generated for them.

Your example assumes the user has access to the log, that
log_min_error_statement is set appropriately and that the user can locate
their log entries to identify the table name. The log contains timed
entries but the user may not be aware of the time of the error accurately
enough to locate the correct statement amongst many others.

If the statement is modified by triggers or rules, then you have no chance.

e.g. add this to the above example:

create or replace rule rr as on insert to nn2 do instead insert into nn
values (new.*);

and its clear that the LOG of the statement, even if it is visible, is
misleading since the SQL refers to table nn, but the error is generated by
the insert into table nn2.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services