Re: NOT NULL violation and error-message

Hi all, when trying to insert/update a NOT NULL column with a null-values (in this case the "created"-column), we get this error:

ERROR: null value in column "created" violates not-null constraint

Using JDBC this error-message is what appears in the SQLException.getMessage() which makes it impossible to tell which table the NOT_NULL_VIOLATION happened in. This can be a real pain as this might happen upon transaction-commit using OR-tools like Hibernate. The transaction might involve updating several tables with the same column-name rendering it impossible to extract what the error really is from the error-message. Is there a way to prefix the column-name in the error-message with table-name and maybe also schema-name? The message would then instead read something like:

ERROR: null value in column "public"."mytable"."created" violates not-null constraint

Oracle does this btw...

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

Andreas Joseph Krogh <andreak(at)officenet(dot)no> wrote:

> ERROR: null value in column "created" violates not-null constraint

It is easy to add the table name to the message, but ...

> ERROR: null value in column "public"."mytable"."created" violates not-null constraint
> Oracle does this btw...

Do we have any guideline about the message for identifier names? We've
already had serveral "table.column" messages, but "schema.table.column"
might be preferred if there are tables with the same name in different
schema. In addition, separated quotes ("schema"."table"."column") are
more SQL-ish than single outer quotes. Which should we use?

At any rate, we need to adjust many regression test and .po files
if we change such kinds of messages.

Index: src/backend/executor/execMain.c
===================================================================
--- src/backend/executor/execMain.c (HEAD)
+++ src/backend/executor/execMain.c (fixed)
@@ -1316,7 +1316,8 @@
slot_attisnull(slot, attrChk))
ereport(ERROR,
(errcode(ERRCODE_NOT_NULL_VIOLATION),
- errmsg("null value in column \"%s\" violates not-null constraint",
+ errmsg("null value in column \"%s.%s\" violates not-null constraint",
+ RelationGetRelationName(rel),
NameStr(rel->rd_att->attrs[attrChk - 1]->attname))));
}
}

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

On Tuesday 12. January 2010 11.10.09 Takahiro Itagaki wrote:
>
> Andreas Joseph Krogh <andreak(at)officenet(dot)no> wrote:
>
> > ERROR: null value in column "created" violates not-null constraint
>
> It is easy to add the table name to the message, but ...
>
> > ERROR: null value in column "public"."mytable"."created" violates not-null constraint
> > Oracle does this btw...
>
> Do we have any guideline about the message for identifier names? We've
> already had serveral "table.column" messages, but "schema.table.column"
> might be preferred if there are tables with the same name in different
> schema. In addition, separated quotes ("schema"."table"."column") are
> more SQL-ish than single outer quotes. Which should we use?
>
> At any rate, we need to adjust many regression test and .po files
> if we change such kinds of messages.
>
>
> Index: src/backend/executor/execMain.c
> ===================================================================
> --- src/backend/executor/execMain.c (HEAD)
> +++ src/backend/executor/execMain.c (fixed)
> @@ -1316,7 +1316,8 @@
> slot_attisnull(slot, attrChk))
> ereport(ERROR,
> (errcode(ERRCODE_NOT_NULL_VIOLATION),
> - errmsg("null value in column \"%s\" violates not-null constraint",
> + errmsg("null value in column \"%s.%s\" violates not-null constraint",
> + RelationGetRelationName(rel),
> NameStr(rel->rd_att->attrs[attrChk - 1]->attname))));
> }
> }

+1

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> Do we have any guideline about the message for identifier names?

The issue has come up before. I think that what we really need here is
to implement additional fields in error message reports, so that the
name and schema name of the relevant object can be pulled out of the
message by programs without having to parse the message text. If we had
that, it would reduce the pressure to clutter the basic message text
this way. It would also improve our compliance with the SQL standard,
which says you're supposed to do it like that.

regards, tom lane

On Tue, Jan 12, 2010 at 5:10 AM, Takahiro Itagaki
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
> Andreas Joseph Krogh <andreak(at)officenet(dot)no> wrote:
>
>> ERROR: null value in column "created" violates not-null constraint
>
> It is easy to add the table name to the message, but ...
>
>> ERROR: null value in column "public"."mytable"."created" violates not-null constraint
>> Oracle does this btw...
>
> Do we have any guideline about the message for identifier names? We've
> already had serveral "table.column" messages, but "schema.table.column"
> might be preferred if there are tables with the same name in different
> schema. In addition, separated quotes ("schema"."table"."column") are
> more SQL-ish than single outer quotes. Which should we use?

I feel like we ought to be doing this in a way where the output is
properly escaped. Right now:

test=# create table dork ("""" integer not null);
CREATE TABLE
test=# insert into dork values (null);
ERROR: null value in column """ violates not-null constraint

Suck.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I feel like we ought to be doing this in a way where the output is
> properly escaped. Right now:

Yeah, that's been an occasional irritation for me, even though I
don't actually use quote characters in any of my quoted identifiers.
Also, tab-completion is irritating in not recognizing matching
column names when you start with a quote if the column name is
entirely lower case.

I would complain about generating a mix of quoted and non-quoted
column names in some places, but fixing that for me would
undoubtedly inconvenience others; I guess we have to consider that
behavior a feature. But the other two just look like sloppy coding
to me; it's hard to see any excuse not to call them bugs, albeit
minor ones.

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I feel like we ought to be doing this in a way where the output is
> properly escaped.

This is one of several reasons why code shouldn't be trying to scrape
the names out of the human-readable message.

regards, tom lane