Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message

> Hi,
> when I insert/update many rows at once using INSERT ... SELECT into a
> table which has plenty of CHECK constraints, the error message that
> Postgres returns has no indication of which row failed the constraint
> check. The attached patch tries to provide information in a similar way
> to how duplicate items in a UNIQUE constraint are handled.

> Originally, I tried to simply check the new row's t_ctid, but it was
> always (0,0) -- I guess that's expected, maybe it's still in memory at
> that time and maybe such nodes don't have a ctid assigned yet.

> Please let me know if this patch is suitable for inclusion. It's based
> on REL9_0_STABLE, because that's the version I'm running.

> I'd like to thank intgr on IRC for his feedback when I was wondering
> about the t_ctid.

> With kind regards,
> Jan

Hi Jan / all.

I'm looking for a simple patch to review and this one doesn't look too
complicate.

The patch seens to be useful, it adds a better feedback.

First, I couldn't apply it as in the email, even in REL9_0_STABLE: the
offset doesn't look right. Which commit are your repository in?

Anyway, I could copy / paste it at the correct place, using the
current master. I could compile it, put a postgres with it running and
it's working:

postgres=# create table test1(id serial primary key, value text);
NOTICE:  CREATE TABLE will create implicit sequence "test1_id_seq" for
serial column "test1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test1_pkey" for table "test1"
CREATE TABLE
postgres=# ALTER TABLE test1 ADD CONSTRAINT must_be_unique unique (value);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index
"must_be_unique" for table "test1"
ALTER TABLE
postgres=# insert into test1 values (default, 'Hello World');
INSERT 0 1
postgres=# insert into test1 values (default, 'Hello World');
ERROR:  duplicate key value violates unique constraint "must_be_unique"
DETAIL:  Key (value)=(Hello World) already exists.

The patch I've used:

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index fd7a9ed..57894cf 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1574,10 +1574,32 @@ ExecConstraints(ResultRelInfo *resultRelInfo,        const char *failed;
        if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) != NULL)
+        {
+            StringInfoData buf;
+            int natts = rel->rd_att->natts;
+            int i;
+            initStringInfo(&buf);
+            for (i = 0; i < natts; ++i)
+            {
+                char *val;
+                Oid foutoid;
+                bool typisvarlena;
+                getTypeOutputInfo(rel->rd_att->attrs[i]->atttypid, &foutoid,
&typisvarlena);
+                if (slot->tts_isnull[i])
+                    val = "NULL";
+                else
+                    val = OidOutputFunctionCall(foutoid, slot->tts_values[i]);
+                if (i > 0)
+                    appendStringInfoString(&buf, ", ");
+                appendStringInfoString(&buf, val);
+            }            ereport(ERROR,                    (errcode(ERRCODE_CHECK_VIOLATION),
errmsg("newrow for relation \"%s\" violates check constraint \"%s\"", 
-                            RelationGetRelationName(rel), failed)));
+                        RelationGetRelationName(rel), failed),
+                    errdetail("New row with data (%s) violates check constraint \"%s\".",
+                        buf.data, failed)));
+        }    }}


--
José Arthur Benetasso Villanova