Skip to content

Commit 991f3e5

Browse files
tglsfdctglsfdc
committed
Provide database object names as separate fields in error messages.
This patch addresses the problem that applications currently have to extract object names from possibly-localized textual error messages, if they want to know for example which index caused a UNIQUE_VIOLATION failure. It adds new error message fields to the wire protocol, which can carry the name of a table, table column, data type, or constraint associated with the error. (Since the protocol spec has always instructed clients to ignore unrecognized field types, this should not create any compatibility problem.) Support for providing these new fields has been added to just a limited set of error reports (mainly, those in the "integrity constraint violation" SQLSTATE class), but we will doubtless add them to more calls in future. Pavel Stehule, reviewed and extensively revised by Peter Geoghegan, with additional hacking by Tom Lane.
1 parent 89d00cb commit 991f3e5

27 files changed

+604
-41
lines changed

‎doc/src/sgml/errcodes.sgml

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
According to the standard, the first two characters of an error code
2828
denote a class of errors, while the last three characters indicate
2929
a specific condition within that class. Thus, an application that
30-
does not recognize the specific error code can still be able to infer
30+
does not recognize the specific error code might still be able to infer
3131
what to do from the error class.
3232
</para>
3333

@@ -42,13 +42,25 @@
4242
</para>
4343

4444
<para>
45-
The symbol shown in the column <quote>Condition Name</quote> is also
45+
The symbol shown in the column <quote>Condition Name</quote> is
4646
the condition name to use in <application>PL/pgSQL</>. Condition
4747
names can be written in either upper or lower case. (Note that
4848
<application>PL/pgSQL</> does not recognize warning, as opposed to error,
4949
condition names; those are classes 00, 01, and 02.)
5050
</para>
5151

52+
<para>
53+
For some types of errors, the server reports the name of a database object
54+
(a table, table column, data type, or constraint) associated with the error;
55+
for example, the name of the unique constraint that caused a
56+
<symbol>unique_violation</> error. Such names are supplied in separate
57+
fields of the error report message so that applications need not try to
58+
extract them from the possibly-localized human-readable text of the message.
59+
As of <productname>PostgreSQL</> 9.3, complete coverage for this feature
60+
exists only for errors in SQLSTATE class 23 (integrity constraint
61+
violation), but this is likely to be expanded in future.
62+
</para>
63+
5264

5365
<table id="errcodes-table">
5466
<title><productname>PostgreSQL</productname> Error Codes</title>

‎doc/src/sgml/libpq.sgml

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2679,6 +2679,62 @@ char *PQresultErrorField(const PGresult *res, int fieldcode);
26792679
</listitem>
26802680
</varlistentry>
26812681

2682+
<varlistentry id="libpq-pg-diag-schema-name">
2683+
<term><symbol>PG_DIAG_SCHEMA_NAME</></term>
2684+
<listitem>
2685+
<para>
2686+
If the error was associated with a specific database object,
2687+
the name of the schema containing that object, if any.
2688+
</para>
2689+
</listitem>
2690+
</varlistentry>
2691+
2692+
<varlistentry id="libpq-pg-diag-table-name">
2693+
<term><symbol>PG_DIAG_TABLE_NAME</></term>
2694+
<listitem>
2695+
<para>
2696+
If the error was associated with a specific table, the name of
2697+
the table. (When this field is present, the schema name field
2698+
provides the name of the table's schema.)
2699+
</para>
2700+
</listitem>
2701+
</varlistentry>
2702+
2703+
<varlistentry id="libpq-pg-diag-column-name">
2704+
<term><symbol>PG_DIAG_COLUMN_NAME</></term>
2705+
<listitem>
2706+
<para>
2707+
If the error was associated with a specific table column, the
2708+
name of the column. (When this field is present, the schema
2709+
and table name fields identify the table.)
2710+
</para>
2711+
</listitem>
2712+
</varlistentry>
2713+
2714+
<varlistentry id="libpq-pg-diag-datatype-name">
2715+
<term><symbol>PG_DIAG_DATATYPE_NAME</></term>
2716+
<listitem>
2717+
<para>
2718+
If the error was associated with a specific datatype, the name
2719+
of the datatype. (When this field is present, the schema name
2720+
field provides the name of the datatype's schema.)
2721+
</para>
2722+
</listitem>
2723+
</varlistentry>
2724+
2725+
<varlistentry id="libpq-pg-diag-constraint-name">
2726+
<term><symbol>PG_DIAG_CONSTRAINT_NAME</></term>
2727+
<listitem>
2728+
<para>
2729+
If the error was associated with a specific constraint,
2730+
the name of the constraint. The table or domain that the
2731+
constraint belongs to is reported using the fields listed
2732+
above. (For this purpose, indexes are treated as constraints,
2733+
even if they weren't created with constraint syntax.)
2734+
</para>
2735+
</listitem>
2736+
</varlistentry>
2737+
26822738
<varlistentry id="libpq-pg-diag-source-file">
26832739
<term><symbol>PG_DIAG_SOURCE_FILE</></term>
26842740
<listitem>
@@ -2710,6 +2766,14 @@ char *PQresultErrorField(const PGresult *res, int fieldcode);
27102766
</variablelist>
27112767
</para>
27122768

2769+
<note>
2770+
<para>
2771+
The fields for schema name, table name, column name, datatype
2772+
name, and constraint name are supplied only for a limited number
2773+
of error types; see <xref linkend="errcodes-appendix">.
2774+
</para>
2775+
</note>
2776+
27132777
<para>
27142778
The client is responsible for formatting displayed information to meet
27152779
its needs; in particular it should break long lines as needed.

‎doc/src/sgml/protocol.sgml

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4757,6 +4757,72 @@ message.
47574757
</listitem>
47584758
</varlistentry>
47594759

4760+
<varlistentry>
4761+
<term>
4762+
<literal>s</>
4763+
</term>
4764+
<listitem>
4765+
<para>
4766+
Schema name: if the error was associated with a specific database
4767+
object, the name of the schema containing that object, if any.
4768+
</para>
4769+
</listitem>
4770+
</varlistentry>
4771+
4772+
<varlistentry>
4773+
<term>
4774+
<literal>t</>
4775+
</term>
4776+
<listitem>
4777+
<para>
4778+
Table name: if the error was associated with a specific table, the
4779+
name of the table. (When this field is present, the schema name field
4780+
provides the name of the table's schema.)
4781+
</para>
4782+
</listitem>
4783+
</varlistentry>
4784+
4785+
<varlistentry>
4786+
<term>
4787+
<literal>c</>
4788+
</term>
4789+
<listitem>
4790+
<para>
4791+
Column name: if the error was associated with a specific table column,
4792+
the name of the column. (When this field is present, the schema and
4793+
table name fields identify the table.)
4794+
</para>
4795+
</listitem>
4796+
</varlistentry>
4797+
4798+
<varlistentry>
4799+
<term>
4800+
<literal>d</>
4801+
</term>
4802+
<listitem>
4803+
<para>
4804+
Datatype name: if the error was associated with a specific datatype,
4805+
the name of the datatype. (When this field is present, the schema
4806+
name field provides the name of the datatype's schema.)
4807+
</para>
4808+
</listitem>
4809+
</varlistentry>
4810+
4811+
<varlistentry>
4812+
<term>
4813+
<literal>n</>
4814+
</term>
4815+
<listitem>
4816+
<para>
4817+
Constraint name: if the error was associated with a specific
4818+
constraint, the name of the constraint. The table or domain that the
4819+
constraint belongs to is reported using the fields listed above. (For
4820+
this purpose, indexes are treated as constraints, even if they weren't
4821+
created with constraint syntax.)
4822+
</para>
4823+
</listitem>
4824+
</varlistentry>
4825+
47604826
<varlistentry>
47614827
<term>
47624828
<literal>F</>
@@ -4794,6 +4860,14 @@ message.
47944860

47954861
</variablelist>
47964862

4863+
<note>
4864+
<para>
4865+
The fields for schema name, table name, column name, datatype name, and
4866+
constraint name are supplied only for a limited number of error types;
4867+
see <xref linkend="errcodes-appendix">.
4868+
</para>
4869+
</note>
4870+
47974871
<para>
47984872
The client is responsible for formatting displayed information to meet its
47994873
needs; in particular it should break long lines as needed. Newline characters

‎doc/src/sgml/sources.sgml

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,45 @@ ereport(ERROR,
273273
query processing.
274274
</para>
275275
</listitem>
276+
<listitem>
277+
<para>
278+
<function>errtable(Relation rel)</function> specifies a relation whose
279+
name and schema name should be included as auxiliary fields in the error
280+
report.
281+
</para>
282+
</listitem>
283+
<listitem>
284+
<para>
285+
<function>errtablecol(Relation rel, int attnum)</function> specifies
286+
a column whose name, table name, and schema name should be included as
287+
auxiliary fields in the error report.
288+
</para>
289+
</listitem>
290+
<listitem>
291+
<para>
292+
<function>errtableconstraint(Relation rel, const char *conname)</function>
293+
specifies a table constraint whose name, table name, and schema name
294+
should be included as auxiliary fields in the error report. Indexes
295+
should be considered to be constraints for this purpose, whether or
296+
not they have an associated <structname>pg_constraint</> entry. Be
297+
careful to pass the underlying heap relation, not the index itself, as
298+
<literal>rel</>.
299+
</para>
300+
</listitem>
301+
<listitem>
302+
<para>
303+
<function>errdatatype(Oid datatypeOid)</function> specifies a data
304+
type whose name and schema name should be included as auxiliary fields
305+
in the error report.
306+
</para>
307+
</listitem>
308+
<listitem>
309+
<para>
310+
<function>errdomainconstraint(Oid datatypeOid, const char *conname)</function>
311+
specifies a domain constraint whose name, domain name, and schema name
312+
should be included as auxiliary fields in the error report.
313+
</para>
314+
</listitem>
276315
<listitem>
277316
<para>
278317
<function>errcode_for_file_access()</> is a convenience function that
@@ -301,6 +340,23 @@ ereport(ERROR,
301340
</itemizedlist>
302341
</para>
303342

343+
<note>
344+
<para>
345+
At most one of the functions <function>errtable</>,
346+
<function>errtablecol</>, <function>errtableconstraint</>,
347+
<function>errdatatype</>, or <function>errdomainconstraint</> should
348+
be used in an <function>ereport</> call. These functions exist to
349+
allow applications to extract the name of a database object associated
350+
with the error condition without having to examine the
351+
potentially-localized error message text.
352+
These functions should be used in error reports for which it's likely
353+
that applications would wish to have automatic error handling. As of
354+
<productname>PostgreSQL</> 9.3, complete coverage exists only for
355+
errors in SQLSTATE class 23 (integrity constraint violation), but this
356+
is likely to be expanded in future.
357+
</para>
358+
</note>
359+
304360
<para>
305361
There is an older function <function>elog</> that is still heavily used.
306362
An <function>elog</> call:

‎src/backend/access/hash/hash.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,7 @@ hashbuild(PG_FUNCTION_ARGS)
8686
* one page.
8787
*/
8888
if (num_buckets >= (uint32) NBuffers)
89-
buildstate.spool = _h_spoolinit(index, num_buckets);
89+
buildstate.spool = _h_spoolinit(heap, index, num_buckets);
9090
else
9191
buildstate.spool = NULL;
9292

‎src/backend/access/hash/hashsort.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -44,7 +44,7 @@ struct HSpool
4444
* create and initialize a spool structure
4545
*/
4646
HSpool *
47-
_h_spoolinit(Relation index, uint32 num_buckets)
47+
_h_spoolinit(Relation heap, Relation index, uint32 num_buckets)
4848
{
4949
HSpool *hspool = (HSpool *) palloc0(sizeof(HSpool));
5050
uint32 hash_mask;
@@ -67,7 +67,8 @@ _h_spoolinit(Relation index, uint32 num_buckets)
6767
* speed index creation. This should be OK since a single backend can't
6868
* run multiple index creations in parallel.
6969
*/
70-
hspool->sortstate = tuplesort_begin_index_hash(index,
70+
hspool->sortstate = tuplesort_begin_index_hash(heap,
71+
index,
7172
hash_mask,
7273
maintenance_work_mem,
7374
false);

‎src/backend/access/nbtree/nbtinsert.c

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -393,7 +393,9 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
393393
RelationGetRelationName(rel)),
394394
errdetail("Key %s already exists.",
395395
BuildIndexValueDescription(rel,
396-
values, isnull))));
396+
values, isnull)),
397+
errtableconstraint(heapRel,
398+
RelationGetRelationName(rel))));
397399
}
398400
}
399401
else if (all_dead)
@@ -455,7 +457,9 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
455457
(errcode(ERRCODE_INTERNAL_ERROR),
456458
errmsg("failed to re-find tuple within index \"%s\"",
457459
RelationGetRelationName(rel)),
458-
errhint("This may be because of a non-immutable index expression.")));
460+
errhint("This may be because of a non-immutable index expression."),
461+
errtableconstraint(heapRel,
462+
RelationGetRelationName(rel))));
459463

460464
if (nbuf != InvalidBuffer)
461465
_bt_relbuf(rel, nbuf);
@@ -523,6 +527,8 @@ _bt_findinsertloc(Relation rel,
523527
* able to fit three items on every page, so restrict any one item to 1/3
524528
* the per-page available space. Note that at this point, itemsz doesn't
525529
* include the ItemId.
530+
*
531+
* NOTE: if you change this, see also the similar code in _bt_buildadd().
526532
*/
527533
if (itemsz > BTMaxItemSize(page))
528534
ereport(ERROR,
@@ -533,7 +539,9 @@ _bt_findinsertloc(Relation rel,
533539
RelationGetRelationName(rel)),
534540
errhint("Values larger than 1/3 of a buffer page cannot be indexed.\n"
535541
"Consider a function index of an MD5 hash of the value, "
536-
"or use full text indexing.")));
542+
"or use full text indexing."),
543+
errtableconstraint(heapRel,
544+
RelationGetRelationName(rel))));
537545

538546
/*----------
539547
* If we will need to split the page to put the item on this page,

‎src/backend/access/nbtree/nbtree.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -109,14 +109,14 @@ btbuild(PG_FUNCTION_ARGS)
109109
elog(ERROR, "index \"%s\" already contains data",
110110
RelationGetRelationName(index));
111111

112-
buildstate.spool = _bt_spoolinit(index, indexInfo->ii_Unique, false);
112+
buildstate.spool = _bt_spoolinit(heap, index, indexInfo->ii_Unique, false);
113113

114114
/*
115115
* If building a unique index, put dead tuples in a second spool to keep
116116
* them out of the uniqueness check.
117117
*/
118118
if (indexInfo->ii_Unique)
119-
buildstate.spool2 = _bt_spoolinit(index, false, true);
119+
buildstate.spool2 = _bt_spoolinit(heap, index, false, true);
120120

121121
/* do the heap scan */
122122
reltuples = IndexBuildHeapScan(heap, index, indexInfo, true,

0 commit comments

Comments
 (0)