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("new row 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