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,
Hi Jan / all.

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

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"
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"
postgres=# insert into test1 values (default, 'Hello World');
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,
+ if (slot->tts_isnull[i])
+ val = "NULL";
+ else
+ val = OidOutputFunctionCall(foutoid, slot->tts_values[i]);
+ if (i > 0)
+ appendStringInfoString(&buf, ", ");
+ appendStringInfoString(&buf, val);
+ }
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

Search Discussions

Discussion Posts


Follow ups

Related Discussions



site design / logo © 2022 Grokbase