Hello all
I am working on new diagnostics fields in errors - CONSTRAINT_NAME,
SCHEMA_NAME, TABLE_NAME and COLUMN_NAME.
These fields is shown when verbosity mode is active. Actually this
works for table constraints, not null constraint and for RI
constraints.
postgres=# delete from xxx;
ERROR: 23503: update or delete on table "xxx" violates foreign key
constraint "yyy_b_fkey" on table "yyy"
DETAIL: Key (a)=(10) is still referenced from table "yyy".
LOCATION: ri_ReportViolation, ri_triggers.c:3593
CONSTRAINT: yyy_b_fkey
SCHEMA: public
TABLE: xxx
COLUMN: a
These fields should be available from GET DIAGNOSTICS statement too -
It could be next step after support for stacked diagnostics.
I looked on column name identification for column constraints - and
there is not any possible identification - so I need a new column to
pg_constraint table - "attrid", that should to identify column for
column's constraint.
This patch is just concept. Final patch will be significantly longer -
we need to check any "ereport" call, as this most important
constraints are table and ri constraints.
Regards
Pavel Stehule
*** ./src/backend/executor/execMain.c.orig 2011-06-05 16:09:09.000000000 +0200
--- ./src/backend/executor/execMain.c 2011-06-05 19:46:41.469227198 +0200
***************
*** 1565,1571 ****
ereport(ERROR,
(errcode(ERRCODE_NOT_NULL_VIOLATION),
errmsg("null value in column \"%s\" violates not-null constraint",
! NameStr(rel->rd_att->attrs[attrChk - 1]->attname))));
}
}
--- 1565,1575 ----
ereport(ERROR,
(errcode(ERRCODE_NOT_NULL_VIOLATION),
errmsg("null value in column \"%s\" violates not-null constraint",
! NameStr(rel->rd_att->attrs[attrChk - 1]->attname)),
! errconstraintname("not_null_constraint"),
! errschemaname(get_namespace_name(RelationGetNamespace(rel))),
! errtablename(RelationGetRelationName(rel)),
! errcolumnname(NameStr(rel->rd_att->attrs[attrChk - 1]->attname))));
}
}
***************
*** 1577,1583 ****
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("new row for relation \"%s\" violates check constraint \"%s\"",
! RelationGetRelationName(rel), failed)));
}
}
--- 1581,1590 ----
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("new row for relation \"%s\" violates check constraint \"%s\"",
! RelationGetRelationName(rel), failed),
! errconstraintname(failed),
! errschemaname(get_namespace_name(RelationGetNamespace(rel))),
! errtablename(RelationGetRelationName(rel))));
}
}
*** ./src/backend/utils/adt/ri_triggers.c.orig 2011-06-05 16:09:10.000000000 +0200
--- ./src/backend/utils/adt/ri_triggers.c 2011-06-05 19:57:28.489835680 +0200
***************
*** 3537,3543 ****
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
RelationGetRelationName(fk_rel), constrname),
errdetail("No rows were found in \"%s\".",
! RelationGetRelationName(pk_rel))));
}
/* Get printable versions of the keys involved */
--- 3537,3546 ----
errmsg("insert or update on table \"%s\" violates foreign key constraint \"%s\"",
RelationGetRelationName(fk_rel), constrname),
errdetail("No rows were found in \"%s\".",
! RelationGetRelationName(pk_rel)),
! errconstraintname(constrname),
! errschemaname(get_namespace_name(RelationGetNamespace(fk_rel))),
! errtablename(RelationGetRelationName(fk_rel))));
}
/* Get printable versions of the keys involved */
***************
*** 3570,3576 ****
RelationGetRelationName(fk_rel), constrname),
errdetail("Key (%s)=(%s) is not present in table \"%s\".",
key_names.data, key_values.data,
! RelationGetRelationName(pk_rel))));
else
ereport(ERROR,
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
--- 3573,3583 ----
RelationGetRelationName(fk_rel), constrname),
errdetail("Key (%s)=(%s) is not present in table \"%s\".",
key_names.data, key_values.data,
! RelationGetRelationName(pk_rel)),
! errconstraintname(constrname),
! errschemaname(get_namespace_name(RelationGetNamespace(fk_rel))),
! errtablename(RelationGetRelationName(fk_rel)),
! errcolumnname(key_names.data)));
else
ereport(ERROR,
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
***************
*** 3579,3585 ****
constrname, RelationGetRelationName(fk_rel)),
errdetail("Key (%s)=(%s) is still referenced from table \"%s\".",
key_names.data, key_values.data,
! RelationGetRelationName(fk_rel))));
}
/* ----------
--- 3586,3596 ----
constrname, RelationGetRelationName(fk_rel)),
errdetail("Key (%s)=(%s) is still referenced from table \"%s\".",
key_names.data, key_values.data,
! RelationGetRelationName(fk_rel)),
! errconstraintname(constrname),
! errschemaname(get_namespace_name(RelationGetNamespace(pk_rel))),
! errtablename(RelationGetRelationName(pk_rel)),
! errcolumnname(key_names.data)));
}
/* ----------
*** ./src/backend/utils/error/elog.c.orig 2011-06-05 16:09:10.000000000 +0200
--- ./src/backend/utils/error/elog.c 2011-06-05 18:38:15.780832408 +0200
***************
*** 989,994 ****
--- 989,1089 ----
}
/*
+ * errconstraintname - add name of constraint related to current error
+ */
+ int
+ errconstraintname(const char *constraint_name)
+ {
+ ErrorData *edata = &errordata[errordata_stack_depth];
+
+ /* we don't bother incrementing recursion_depth */
+ CHECK_STACK_DEPTH();
+
+ if (edata->constraint_name)
+ {
+ pfree(edata->constraint_name);
+ edata->constraint_name = NULL;
+ }
+
+ if (constraint_name)
+ edata->constraint_name = MemoryContextStrdup(ErrorContext, constraint_name);
+
+ return 0; /* return value does not matter */
+ }
+
+
+
+ /*
+ * errschemaname - add name of schema related to current error
+ */
+ int
+ errschemaname(const char *schema_name)
+ {
+ ErrorData *edata = &errordata[errordata_stack_depth];
+
+ /* we don't bother incrementing recursion_depth */
+ CHECK_STACK_DEPTH();
+
+ if (edata->schema_name)
+ {
+ pfree(edata->schema_name);
+ edata->schema_name = NULL;
+ }
+
+ if (schema_name)
+ edata->schema_name = MemoryContextStrdup(ErrorContext, schema_name);
+
+ return 0; /* return value does not matter */
+ }
+
+ /*
+ * errtablename - add name of table related to current error
+ */
+ int
+ errtablename(const char *table_name)
+ {
+ ErrorData *edata = &errordata[errordata_stack_depth];
+
+ /* we don't bother incrementing recursion_depth */
+ CHECK_STACK_DEPTH();
+
+ if (edata->table_name)
+ {
+ pfree(edata->table_name);
+ edata->table_name = NULL;
+ }
+
+ if (table_name)
+ edata->table_name = MemoryContextStrdup(ErrorContext, table_name);
+
+ return 0; /* return value does not matter */
+ }
+
+ /*
+ * errcolumnname - add name of column related to current error
+ */
+ int
+ errcolumnname(const char *column_name)
+ {
+ ErrorData *edata = &errordata[errordata_stack_depth];
+
+ /* we don't bother incrementing recursion_depth */
+ CHECK_STACK_DEPTH();
+
+ if (edata->column_name)
+ {
+ pfree(edata->column_name);
+ edata->column_name = NULL;
+ }
+
+ if (column_name)
+ edata->column_name = MemoryContextStrdup(ErrorContext, column_name);
+
+ return 0; /* return value does not matter */
+ }
+
+
+ /*
* internalerrposition --- add internal cursor position to the current error
*/
int
***************
*** 1277,1282 ****
--- 1372,1385 ----
newedata->hint = pstrdup(newedata->hint);
if (newedata->context)
newedata->context = pstrdup(newedata->context);
+ if (newedata->constraint_name)
+ newedata->constraint_name = pstrdup(newedata->constraint_name);
+ if (newedata->schema_name)
+ newedata->schema_name = pstrdup(newedata->schema_name);
+ if (newedata->table_name)
+ newedata->table_name = pstrdup(newedata->table_name);
+ if (newedata->column_name)
+ newedata->column_name = pstrdup(newedata->column_name);
if (newedata->internalquery)
newedata->internalquery = pstrdup(newedata->internalquery);
***************
*** 1304,1309 ****
--- 1407,1421 ----
pfree(edata->context);
if (edata->internalquery)
pfree(edata->internalquery);
+ if (edata->constraint_name)
+ pfree(edata->constraint_name);
+ if (edata->schema_name)
+ pfree(edata->schema_name);
+ if (edata->table_name)
+ pfree(edata->table_name);
+ if (edata->column_name)
+ pfree(edata->column_name);
+
pfree(edata);
}
***************
*** 1374,1379 ****
--- 1486,1499 ----
newedata->hint = pstrdup(newedata->hint);
if (newedata->context)
newedata->context = pstrdup(newedata->context);
+ if (newedata->constraint_name)
+ newedata->constraint_name = pstrdup(newedata->constraint_name);
+ if (newedata->schema_name)
+ newedata->schema_name = pstrdup(newedata->schema_name);
+ if (newedata->table_name)
+ newedata->table_name = pstrdup(newedata->table_name);
+ if (newedata->column_name)
+ newedata->column_name = pstrdup(newedata->column_name);
if (newedata->internalquery)
newedata->internalquery = pstrdup(newedata->internalquery);
***************
*** 2197,2202 ****
--- 2317,2337 ----
if (application_name)
appendCSVLiteral(&buf, application_name);
+ /* constraint_name */
+ appendCSVLiteral(&buf, edata->constraint_name);
+ appendStringInfoChar(&buf, ',');
+
+ /* schema name */
+ appendCSVLiteral(&buf, edata->schema_name);
+ appendStringInfoChar(&buf, ',');
+
+ /* table name */
+ appendCSVLiteral(&buf, edata->table_name);
+ appendStringInfoChar(&buf, ',');
+
+ /* column name */
+ appendCSVLiteral(&buf, edata->column_name);
+
appendStringInfoChar(&buf, '\n');
/* If in the syslogger process, try to write messages direct to file */
***************
*** 2284,2289 ****
--- 2419,2452 ----
append_with_tabs(&buf, edata->hint);
appendStringInfoChar(&buf, '\n');
}
+ if (edata->constraint_name)
+ {
+ log_line_prefix(&buf, edata);
+ appendStringInfoString(&buf, _("CONSTRAINT: "));
+ append_with_tabs(&buf, edata->constraint_name);
+ appendStringInfoChar(&buf, '\n');
+ }
+ if (edata->schema_name)
+ {
+ log_line_prefix(&buf, edata);
+ appendStringInfoString(&buf, _("SCHEMA: "));
+ append_with_tabs(&buf, edata->schema_name);
+ appendStringInfoChar(&buf, '\n');
+ }
+ if (edata->table_name)
+ {
+ log_line_prefix(&buf, edata);
+ appendStringInfoString(&buf, _("TABLE: "));
+ append_with_tabs(&buf, edata->table_name);
+ appendStringInfoChar(&buf, '\n');
+ }
+ if (edata->column_name)
+ {
+ log_line_prefix(&buf, edata);
+ appendStringInfoString(&buf, _("COLUMN: "));
+ append_with_tabs(&buf, edata->column_name);
+ appendStringInfoChar(&buf, '\n');
+ }
if (edata->internalquery)
{
log_line_prefix(&buf, edata);
***************
*** 2552,2557 ****
--- 2715,2744 ----
err_sendstring(&msgbuf, edata->context);
}
+ if (edata->constraint_name)
+ {
+ pq_sendbyte(&msgbuf, PG_DIAG_CONSTRAINT_NAME);
+ err_sendstring(&msgbuf, edata->constraint_name);
+ }
+
+ if (edata->schema_name)
+ {
+ pq_sendbyte(&msgbuf, PG_DIAG_SCHEMA_NAME);
+ err_sendstring(&msgbuf, edata->schema_name);
+ }
+
+ if (edata->table_name)
+ {
+ pq_sendbyte(&msgbuf, PG_DIAG_TABLE_NAME);
+ err_sendstring(&msgbuf, edata->table_name);
+ }
+
+ if (edata->column_name)
+ {
+ pq_sendbyte(&msgbuf, PG_DIAG_COLUMN_NAME);
+ err_sendstring(&msgbuf, edata->column_name);
+ }
+
if (edata->cursorpos > 0)
{
snprintf(tbuf, sizeof(tbuf), "%d", edata->cursorpos);
*** ./src/include/postgres_ext.h.orig 2011-06-05 16:09:10.000000000 +0200
--- ./src/include/postgres_ext.h 2011-06-05 18:31:35.428844302 +0200
***************
*** 55,59 ****
#define PG_DIAG_SOURCE_FILE 'F'
#define PG_DIAG_SOURCE_LINE 'L'
#define PG_DIAG_SOURCE_FUNCTION 'R'
!
#endif
--- 55,62 ----
#define PG_DIAG_SOURCE_FILE 'F'
#define PG_DIAG_SOURCE_LINE 'L'
#define PG_DIAG_SOURCE_FUNCTION 'R'
! #define PG_DIAG_SCHEMA_NAME 's'
! #define PG_DIAG_TABLE_NAME 't'
! #define PG_DIAG_COLUMN_NAME 'c'
! #define PG_DIAG_CONSTRAINT_NAME 'n'
#endif
*** ./src/include/utils/elog.h.orig 2011-06-05 16:09:10.000000000 +0200
--- ./src/include/utils/elog.h 2011-06-05 18:30:39.938499613 +0200
***************
*** 177,182 ****
--- 177,187 ----
extern int errfunction(const char *funcname);
extern int errposition(int cursorpos);
+ extern int errconstraintname(const char *constraint_name);
+ extern int errschemaname(const char *schema_name);
+ extern int errtablename(const char *table_name);
+ extern int errcolumnname(const char *column_name);
+
extern int internalerrposition(int cursorpos);
extern int internalerrquery(const char *query);
***************
*** 315,320 ****
--- 320,329 ----
char *detail_log; /* detail error message for server log only */
char *hint; /* hint message */
char *context; /* context message */
+ char *constraint_name; /* name of constraint related to error */
+ char *schema_name; /* name of schema that contains table related to error */
+ char *table_name; /* name of table related to error */
+ char *column_name; /* name of column related to error */
int cursorpos; /* cursor index into query string */
int internalpos; /* cursor index into internalquery */
char *internalquery; /* text of internally-generated query */
*** ./src/interfaces/libpq/fe-protocol3.c.orig 2011-06-05 16:09:10.000000000 +0200
--- ./src/interfaces/libpq/fe-protocol3.c 2011-06-05 18:32:38.101139497 +0200
***************
*** 856,861 ****
--- 856,873 ----
valf, vall);
appendPQExpBufferChar(&workBuf, '\n');
}
+ val = PQresultErrorField(res, PG_DIAG_CONSTRAINT_NAME);
+ if (val)
+ appendPQExpBuffer(&workBuf, libpq_gettext("CONSTRAINT: %s\n"), val);
+ val = PQresultErrorField(res, PG_DIAG_SCHEMA_NAME);
+ if (val)
+ appendPQExpBuffer(&workBuf, libpq_gettext("SCHEMA: %s\n"), val);
+ val = PQresultErrorField(res, PG_DIAG_TABLE_NAME);
+ if (val)
+ appendPQExpBuffer(&workBuf, libpq_gettext("TABLE: %s\n"), val);
+ val = PQresultErrorField(res, PG_DIAG_COLUMN_NAME);
+ if (val)
+ appendPQExpBuffer(&workBuf, libpq_gettext("COLUMN: %s\n"), val);
}
/*
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
