Re: not null constraints, again - Mailing list pgsql-hackers

On 2024-Sep-09, jian he wrote:

> bold idea. print out the constraint name: violates not-null constraint \"%s\"
> for the following code:
>                 ereport(ERROR,
>                         (errcode(ERRCODE_NOT_NULL_VIOLATION),
>                          errmsg("null value in column \"%s\" of
> relation \"%s\" violates not-null constraint",
>                                 NameStr(att->attname),
>                                 RelationGetRelationName(orig_rel)),
>                          val_desc ? errdetail("Failing row contains
> %s.", val_desc) : 0,
>                          errtablecol(orig_rel, attrChk)));

I gave this a quick run, but I'm not sure it actually improves things
much.  Here's one change from the regression tests.  What do you think?

 INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
 -ERROR:  null value in column "i" of relation "reloptions_test" violates not-null constraint
 +ERROR:  null value in column "i" of relation "reloptions_test" violates not-null constraint
"reloptions_test_i_not_null"

What do I get from having the constraint name?  It's not like I'm going
to drop the constraint and retry the insert.

Here's the POC-quality patch for this.  I changes a lot of regression
tests, which I don't patch here.  (But that's not the reason for me
thinking that this isn't worth it.)

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 29e186fa73d..d84137f4f43 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1907,6 +1907,7 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
  * have been converted from the original input tuple after tuple routing.
  * 'resultRelInfo' is the final result relation, after tuple routing.
  */
+#include "catalog/pg_constraint.h"
 void
 ExecConstraints(ResultRelInfo *resultRelInfo,
                 TupleTableSlot *slot, EState *estate)
@@ -1932,6 +1933,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
                 char       *val_desc;
                 Relation    orig_rel = rel;
                 TupleDesc    orig_tupdesc = RelationGetDescr(rel);
+                char       *conname;
 
                 /*
                  * If the tuple has been routed, it's been converted to the
@@ -1970,14 +1972,24 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
                                                          tupdesc,
                                                          modifiedCols,
                                                          64);
+                {
+                    HeapTuple    tuple;
+                    Form_pg_constraint conForm;
+
+                    tuple = findNotNullConstraintAttnum(RelationGetRelid(orig_rel),
+                                                        att->attnum);
+                    conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+                    conname = pstrdup(NameStr(conForm->conname));
+                }
 
                 ereport(ERROR,
-                        (errcode(ERRCODE_NOT_NULL_VIOLATION),
-                         errmsg("null value in column \"%s\" of relation \"%s\" violates not-null constraint",
-                                NameStr(att->attname),
-                                RelationGetRelationName(orig_rel)),
-                         val_desc ? errdetail("Failing row contains %s.", val_desc) : 0,
-                         errtablecol(orig_rel, attrChk)));
+                        errcode(ERRCODE_NOT_NULL_VIOLATION),
+                        errmsg("null value in column \"%s\" of relation \"%s\" violates not-null constraint \"%s\"",
+                               NameStr(att->attname),
+                               RelationGetRelationName(orig_rel),
+                               conname),
+                        val_desc ? errdetail("Failing row contains %s.", val_desc) : 0,
+                        errtablecol(orig_rel, attrChk));
             }
         }
     }
-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/