Re: not null constraints, again

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/