Feedback-ID: ia2694551:Fastmail
Date: Tue, 10 Sep 2024 16:05:10 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jian he <jian.universality@gmail.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: not null constraints, again
Message-ID: <202409101405.mkfg34imua4j@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CACJufxEt73xXq89siA4Z-2g+k9kxeBd5Q9e6N+LKkXude-J+0A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202409101405.mkfg34imua4j%40alvherre.pgsql>
Precedence: bulk

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/