Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf1e1300

Browse files
committed
When a row fails a CHECK constraint, show row's contents in errdetail.
This should make it easier to identify which row is problematic when aninsert or update is processing many rows.The formatting is similar to that for unique-index violation messages,except that we limit field widths to 64 bytes since otherwise the messagecould get unreasonably long. (In particular, there's currently no attemptto quote or escape field values that contain commas etc.)Jan Kundrát, reviewed by Royce Ausburn, somewhat rewritten by me.
1 parent9922fc5 commitf1e1300

File tree

5 files changed

+102
-1
lines changed

5 files changed

+102
-1
lines changed

‎src/backend/executor/execMain.c

Lines changed: 65 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@
4747
#include"commands/tablespace.h"
4848
#include"commands/trigger.h"
4949
#include"executor/execdebug.h"
50+
#include"mb/pg_wchar.h"
5051
#include"miscadmin.h"
5152
#include"optimizer/clauses.h"
5253
#include"parser/parse_clause.h"
@@ -85,6 +86,8 @@ static void ExecutePlan(EState *estate, PlanState *planstate,
8586
DestReceiver*dest);
8687
staticboolExecCheckRTEPerms(RangeTblEntry*rte);
8788
staticvoidExecCheckXactReadOnly(PlannedStmt*plannedstmt);
89+
staticchar*ExecBuildSlotValueDescription(TupleTableSlot*slot,
90+
intmaxfieldlen);
8891
staticvoidEvalPlanQualStart(EPQState*epqstate,EState*parentestate,
8992
Plan*planTree);
9093
staticvoidOpenIntoRel(QueryDesc*queryDesc);
@@ -1585,10 +1588,71 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
15851588
ereport(ERROR,
15861589
(errcode(ERRCODE_CHECK_VIOLATION),
15871590
errmsg("new row for relation \"%s\" violates check constraint \"%s\"",
1588-
RelationGetRelationName(rel),failed)));
1591+
RelationGetRelationName(rel),failed),
1592+
errdetail("Failing row contains %s.",
1593+
ExecBuildSlotValueDescription(slot,64))));
15891594
}
15901595
}
15911596

1597+
/*
1598+
* ExecBuildSlotValueDescription -- construct a string representing a tuple
1599+
*
1600+
* This is intentionally very similar to BuildIndexValueDescription, but
1601+
* unlike that function, we truncate long field values. That seems necessary
1602+
* here since heap field values could be very long, whereas index entries
1603+
* typically aren't so wide.
1604+
*/
1605+
staticchar*
1606+
ExecBuildSlotValueDescription(TupleTableSlot*slot,intmaxfieldlen)
1607+
{
1608+
StringInfoDatabuf;
1609+
TupleDesctupdesc=slot->tts_tupleDescriptor;
1610+
inti;
1611+
1612+
/* Make sure the tuple is fully deconstructed */
1613+
slot_getallattrs(slot);
1614+
1615+
initStringInfo(&buf);
1616+
1617+
appendStringInfoChar(&buf,'(');
1618+
1619+
for (i=0;i<tupdesc->natts;i++)
1620+
{
1621+
char*val;
1622+
intvallen;
1623+
1624+
if (slot->tts_isnull[i])
1625+
val="null";
1626+
else
1627+
{
1628+
Oidfoutoid;
1629+
booltypisvarlena;
1630+
1631+
getTypeOutputInfo(tupdesc->attrs[i]->atttypid,
1632+
&foutoid,&typisvarlena);
1633+
val=OidOutputFunctionCall(foutoid,slot->tts_values[i]);
1634+
}
1635+
1636+
if (i>0)
1637+
appendStringInfoString(&buf,", ");
1638+
1639+
/* truncate if needed */
1640+
vallen=strlen(val);
1641+
if (vallen <=maxfieldlen)
1642+
appendStringInfoString(&buf,val);
1643+
else
1644+
{
1645+
vallen=pg_mbcliplen(val,vallen,maxfieldlen);
1646+
appendBinaryStringInfo(&buf,val,vallen);
1647+
appendStringInfoString(&buf,"...");
1648+
}
1649+
}
1650+
1651+
appendStringInfoChar(&buf,')');
1652+
1653+
returnbuf.data;
1654+
}
1655+
15921656

15931657
/*
15941658
* ExecFindRowMark -- find the ExecRowMark struct for given rangetable index

‎src/test/regress/expected/alter_table.out

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -390,6 +390,7 @@ alter table atacc1 add constraint atacc_test1 check (test>3);
390390
-- should fail
391391
insert into atacc1 (test) values (2);
392392
ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
393+
DETAIL: Failing row contains (2).
393394
-- should succeed
394395
insert into atacc1 (test) values (4);
395396
drop table atacc1;
@@ -415,6 +416,7 @@ alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
415416
-- should fail
416417
insert into atacc1 (test,test2,test3) values (4,4,2);
417418
ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"
419+
DETAIL: Failing row contains (4, 4, 2).
418420
-- should succeed
419421
insert into atacc1 (test,test2,test3) values (4,4,5);
420422
drop table atacc1;
@@ -424,6 +426,7 @@ alter table atacc1 add check (test2>test);
424426
-- should fail for $2
425427
insert into atacc1 (test2, test) values (3, 4);
426428
ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"
429+
DETAIL: Failing row contains (4, 3).
427430
drop table atacc1;
428431
-- inheritance related tests
429432
create table atacc1 (test int);
@@ -433,10 +436,12 @@ alter table atacc2 add constraint foo check (test2>0);
433436
-- fail and then succeed on atacc2
434437
insert into atacc2 (test2) values (-3);
435438
ERROR: new row for relation "atacc2" violates check constraint "foo"
439+
DETAIL: Failing row contains (-3).
436440
insert into atacc2 (test2) values (3);
437441
-- fail and then succeed on atacc3
438442
insert into atacc3 (test2) values (-3);
439443
ERROR: new row for relation "atacc3" violates check constraint "foo"
444+
DETAIL: Failing row contains (null, -3, null).
440445
insert into atacc3 (test2) values (3);
441446
drop table atacc3;
442447
drop table atacc2;
@@ -507,6 +512,7 @@ insert into atacc1 (test) values (3);
507512
-- check constraint is there on child
508513
insert into atacc2 (test) values (-3);
509514
ERROR: new row for relation "atacc2" violates check constraint "foo"
515+
DETAIL: Failing row contains (-3, null).
510516
insert into atacc2 (test) values (3);
511517
drop table atacc2;
512518
drop table atacc1;
@@ -1450,6 +1456,7 @@ NOTICE: merging definition of column "f2" for child "c1"
14501456
insert into p1 values (1,2,'abc');
14511457
insert into c1 values(11,'xyz',33,0); -- should fail
14521458
ERROR: new row for relation "c1" violates check constraint "p1_a1_check"
1459+
DETAIL: Failing row contains (11, xyz, 33, 0).
14531460
insert into c1 values(11,'xyz',33,22);
14541461
select * from p1;
14551462
f1 | a1 | f2
@@ -1537,6 +1544,7 @@ select * from anothertab;
15371544

15381545
insert into anothertab (atcol1, atcol2) values (45, null); -- fails
15391546
ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"
1547+
DETAIL: Failing row contains (45, null).
15401548
insert into anothertab (atcol1, atcol2) values (default, null);
15411549
select * from anothertab;
15421550
atcol1 | atcol2
@@ -2110,5 +2118,6 @@ ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_paren
21102118
-- should fail
21112119
INSERT INTO test_drop_constr_child (c) VALUES (NULL);
21122120
ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
2121+
DETAIL: Failing row contains (null).
21132122
DROP TABLE test_drop_constr_parent CASCADE;
21142123
NOTICE: drop cascades to table test_drop_constr_child

‎src/test/regress/expected/domain.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,7 @@ insert into nulltest values ('a', 'b', 'c', 'd', NULL);
199199
ERROR: domain dcheck does not allow null values
200200
insert into nulltest values ('a', 'b', 'c', 'd', 'a');
201201
ERROR: new row for relation "nulltest" violates check constraint "nulltest_col5_check"
202+
DETAIL: Failing row contains (a, b, c, d, a).
202203
INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd');
203204
ERROR: domain dnotnull does not allow null values
204205
INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');
@@ -216,6 +217,7 @@ CONTEXT: COPY nulltest, line 1, column col5: null input
216217
-- Last row is bad
217218
COPY nulltest FROM stdin;
218219
ERROR: new row for relation "nulltest" violates check constraint "nulltest_col5_check"
220+
DETAIL: Failing row contains (a, b, c, null, a).
219221
CONTEXT: COPY nulltest, line 3: "abc\Na"
220222
select * from nulltest;
221223
col1 | col2 | col3 | col4 | col5

‎src/test/regress/expected/inherit.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -640,6 +640,7 @@ INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
640640
INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
641641
INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */
642642
ERROR: new row for relation "inhg" violates check constraint "foo"
643+
DETAIL: Failing row contains (x, foo, y).
643644
SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
644645
x | xx | y
645646
---+------+---
@@ -721,8 +722,10 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
721722

722723
insert into ac (aa) values (NULL);
723724
ERROR: new row for relation "ac" violates check constraint "ac_check"
725+
DETAIL: Failing row contains (null).
724726
insert into bc (aa) values (NULL);
725727
ERROR: new row for relation "bc" violates check constraint "ac_check"
728+
DETAIL: Failing row contains (null, null).
726729
alter table bc drop constraint ac_check; -- fail, disallowed
727730
ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
728731
alter table ac drop constraint ac_check;
@@ -742,8 +745,10 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
742745

743746
insert into ac (aa) values (NULL);
744747
ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
748+
DETAIL: Failing row contains (null).
745749
insert into bc (aa) values (NULL);
746750
ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
751+
DETAIL: Failing row contains (null, null).
747752
alter table bc drop constraint ac_aa_check; -- fail, disallowed
748753
ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
749754
alter table ac drop constraint ac_aa_check;
@@ -830,6 +835,7 @@ insert into c1 values(1,1,2);
830835
alter table p2 add check (f2>0);
831836
insert into c1 values(1,-1,2); -- fail
832837
ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
838+
DETAIL: Failing row contains (1, -1, 2).
833839
create table c2(f3 int) inherits(p1,p2);
834840
\d c2
835841
Table "public.c2"

‎src/test/regress/output/constraints.source

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,11 +68,14 @@ INSERT INTO CHECK_TBL VALUES (5);
6868
INSERT INTO CHECK_TBL VALUES (4);
6969
INSERT INTO CHECK_TBL VALUES (3);
7070
ERROR: new row for relation "check_tbl" violates check constraint "check_con"
71+
DETAIL: Failing row contains (3).
7172
INSERT INTO CHECK_TBL VALUES (2);
7273
ERROR: new row for relation "check_tbl" violates check constraint "check_con"
74+
DETAIL: Failing row contains (2).
7375
INSERT INTO CHECK_TBL VALUES (6);
7476
INSERT INTO CHECK_TBL VALUES (1);
7577
ERROR: new row for relation "check_tbl" violates check constraint "check_con"
78+
DETAIL: Failing row contains (1).
7679
SELECT '' AS three, * FROM CHECK_TBL;
7780
three | x
7881
-------+---
@@ -88,12 +91,16 @@ CREATE TABLE CHECK2_TBL (x int, y text, z int,
8891
INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
8992
INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
9093
ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
94+
DETAIL: Failing row contains (1, x check failed, -2).
9195
INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
9296
ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
97+
DETAIL: Failing row contains (5, z check failed, 10).
9398
INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
9499
ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
100+
DETAIL: Failing row contains (0, check failed, -2).
95101
INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
96102
ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
103+
DETAIL: Failing row contains (6, check failed, 11).
97104
INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
98105
SELECT '' AS two, * from CHECK2_TBL;
99106
two | x | y | z
@@ -113,6 +120,7 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
113120
CHECK (x + z = 0));
114121
INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
115122
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
123+
DETAIL: Failing row contains (2, -NULL-, -2).
116124
SELECT '' AS zero, * FROM INSERT_TBL;
117125
zero | x | y | z
118126
------+---+---+---
@@ -126,12 +134,15 @@ SELECT 'one' AS one, nextval('insert_seq');
126134

127135
INSERT INTO INSERT_TBL(y) VALUES ('Y');
128136
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
137+
DETAIL: Failing row contains (2, Y, -2).
129138
INSERT INTO INSERT_TBL(y) VALUES ('Y');
130139
INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
131140
ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
141+
DETAIL: Failing row contains (1, -NULL-, -2).
132142
INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7);
133143
INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
134144
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
145+
DETAIL: Failing row contains (5, check failed, -5).
135146
INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
136147
INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
137148
SELECT '' AS four, * FROM INSERT_TBL;
@@ -145,8 +156,10 @@ SELECT '' AS four, * FROM INSERT_TBL;
145156

146157
INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
147158
ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
159+
DETAIL: Failing row contains (5, check failed, 4).
148160
INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
149161
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
162+
DETAIL: Failing row contains (5, check failed, -5).
150163
INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
151164
INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
152165
SELECT '' AS six, * FROM INSERT_TBL;
@@ -168,6 +181,7 @@ SELECT 'seven' AS one, nextval('insert_seq');
168181

169182
INSERT INTO INSERT_TBL(y) VALUES ('Y');
170183
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
184+
DETAIL: Failing row contains (8, Y, -8).
171185
SELECT 'eight' AS one, currval('insert_seq');
172186
one | currval
173187
-------+---------
@@ -199,10 +213,13 @@ CREATE TABLE INSERT_CHILD (cx INT default 42,
199213
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
200214
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
201215
ERROR: new row for relation "insert_child" violates check constraint "insert_child_check"
216+
DETAIL: Failing row contains (7, -NULL-, -7, 42, 6).
202217
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
203218
ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_check"
219+
DETAIL: Failing row contains (6, -NULL-, -7, 42, 7).
204220
INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
205221
ERROR: new row for relation "insert_child" violates check constraint "insert_con"
222+
DETAIL: Failing row contains (6, check failed, -6, 42, 7).
206223
SELECT * FROM INSERT_CHILD;
207224
x | y | z | cx | cy
208225
---+--------+----+----+----
@@ -232,6 +249,7 @@ INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
232249
INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
233250
INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
234251
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
252+
DETAIL: Failing row contains (8, try again, -8).
235253
SELECT '' AS four, * FROM INSERT_TBL;
236254
four | x | y | z
237255
------+---+---------------+----
@@ -251,6 +269,7 @@ UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
251269
UPDATE INSERT_TBL SET x = -z, z = -x;
252270
UPDATE INSERT_TBL SET x = z, z = x;
253271
ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
272+
DETAIL: Failing row contains (-4, Y, 4).
254273
SELECT * FROM INSERT_TBL;
255274
x | y | z
256275
---+---------------+----
@@ -278,6 +297,7 @@ SELECT '' AS two, * FROM COPY_TBL;
278297

279298
COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
280299
ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
300+
DETAIL: Failing row contains (7, check failed, 6).
281301
CONTEXT: COPY copy_tbl, line 2: "7check failed6"
282302
SELECT * FROM COPY_TBL;
283303
x | y | z

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp