On 3/4/20 2:54 AM, Chris Bandy wrote:
> I've also convinced myself that the number of integrity errors in the
> entire codebase is manageable to test. If others think it is worthwhile,
> I can spend some time over the next week to expand this test approach to
> cover _all_ SQLSTATE class 23 errors.
Done. Please find attached two patches that (1) test all but one reports
of integrity violations and (2) attach object names to the handful that
lacked them.
I decided to include error messages in the tests so that the next person
to change the message would be mindful of the attached fields and vice
versa. I thought these might be impacted by locale, but `make check
LANG=de_DE.utf8` passes for me. Is that command the right way to verify
that?
With these patches, behavior matches the documentation which states:
"[object] names are supplied in separate fields of the error report
message so that applications need not try to extract them from the
possibly-localized human-readable text of the message. As of PostgreSQL
9.3, complete coverage for this feature exists only for errors in
SQLSTATE class 23..."
Thanks,
Chris
>From 101bb413634f4be82a6d934660ceda99e4f4cc53 Mon Sep 17 00:00:00 2001
From: Chris Bandy <bandy.ch...@gmail.com>
Date: Fri, 6 Mar 2020 20:48:55 -0600
Subject: [PATCH 1/2] Add tests for integrity violation error fields
The documentation states that all errors of SQLSTATE class 23 should
include the name of an object associated with the error.
---
src/test/regress/expected/integrity_errors.out | 408 +++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/integrity_errors.sql | 193 ++++++++++++
3 files changed, 602 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/integrity_errors.out
create mode 100644 src/test/regress/sql/integrity_errors.sql
diff --git a/src/test/regress/expected/integrity_errors.out b/src/test/regress/expected/integrity_errors.out
new file mode 100644
index 0000000000..e75e6b722f
--- /dev/null
+++ b/src/test/regress/expected/integrity_errors.out
@@ -0,0 +1,408 @@
+--
+-- Tests for integrity violation error fields
+--
+-- Errors in SQLSTATE class 23 (integrity constraint violation) should
+-- include the name of a database object as a separate field.
+--
+-- The fields of interest are shown at the same verbosity level as
+-- volatile details such as source code line numbers. To produce stable
+-- regression output, the following function returns a portion of the
+-- full error reported.
+CREATE FUNCTION integrity_error_record(
+ dml text,
+ OUT err_sqlstate text,
+ OUT err_message text,
+ OUT err_detail text,
+ OUT err_datatype text,
+ OUT err_schema text,
+ OUT err_table text,
+ OUT err_column text,
+ OUT err_constraint text)
+AS $$
+BEGIN
+ EXECUTE $1;
+EXCEPTION
+ WHEN integrity_constraint_violation THEN GET STACKED DIAGNOSTICS
+ err_sqlstate := RETURNED_SQLSTATE,
+ err_message := MESSAGE_TEXT,
+ err_detail := PG_EXCEPTION_DETAIL,
+ err_datatype := PG_DATATYPE_NAME,
+ err_schema := SCHEMA_NAME,
+ err_table := TABLE_NAME,
+ err_column := COLUMN_NAME,
+ err_constraint := CONSTRAINT_NAME;
+END;
+$$ LANGUAGE plpgsql;
+\pset expanded on
+\pset tuples_only on
+-- table not null
+CREATE TABLE ivnt1 (n int NOT NULL);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivnt1 VALUES (NULL);
+$$);
+err_sqlstate | 23502
+err_message | null value in column "n" of relation "ivnt1" violates not-null constraint
+err_detail | Failing row contains (null).
+err_datatype |
+err_schema | public
+err_table | ivnt1
+err_column | n
+err_constraint |
+
+-- alter table not null
+CREATE TABLE ivnt2 (n int);
+INSERT INTO ivnt2 VALUES (NULL);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivnt2 ALTER n SET NOT NULL;
+$$);
+err_sqlstate | 23502
+err_message | column "n" of relation "ivnt2" contains null values
+err_detail |
+err_datatype |
+err_schema | public
+err_table | ivnt2
+err_column | n
+err_constraint |
+
+DROP TABLE ivnt1, ivnt2;
+-- table unique
+CREATE TABLE ivpkt1 (x int, y int, PRIMARY KEY (x, y));
+INSERT INTO ivpkt1 VALUES (1, 2);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpkt1 VALUES (1, 2);
+$$);
+err_sqlstate | 23505
+err_message | duplicate key value violates unique constraint "ivpkt1_pkey"
+err_detail | Key (x, y)=(1, 2) already exists.
+err_datatype |
+err_schema | public
+err_table | ivpkt1
+err_column |
+err_constraint | ivpkt1_pkey
+
+-- alter table unique
+CREATE TABLE ivpkt2 (x int, y int);
+INSERT INTO ivpkt2 VALUES (1, 2), (1, 2);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpkt2 ADD PRIMARY KEY (x, y);
+$$);
+err_sqlstate | 23505
+err_message | could not create unique index "ivpkt2_pkey"
+err_detail | Key (x, y)=(1, 2) is duplicated.
+err_datatype |
+err_schema | public
+err_table | ivpkt2
+err_column |
+err_constraint | ivpkt2_pkey
+
+-- table foreign key reference
+CREATE TABLE ivfkt1 (x int, y int, FOREIGN KEY (x, y) REFERENCES ivpkt1);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivfkt1 VALUES (10, 10);
+$$);
+err_sqlstate | 23503
+err_message | insert or update on table "ivfkt1" violates foreign key constraint "ivfkt1_x_y_fkey"
+err_detail | Key (x, y)=(10, 10) is not present in table "ivpkt1".
+err_datatype |
+err_schema | public
+err_table | ivfkt1
+err_column |
+err_constraint | ivfkt1_x_y_fkey
+
+INSERT INTO ivfkt1 VALUES (1, 2);
+SELECT * FROM integrity_error_record($$
+ DELETE FROM ivpkt1;
+$$);
+err_sqlstate | 23503
+err_message | update or delete on table "ivpkt1" violates foreign key constraint "ivfkt1_x_y_fkey" on table "ivfkt1"
+err_detail | Key (x, y)=(1, 2) is still referenced from table "ivfkt1".
+err_datatype |
+err_schema | public
+err_table | ivfkt1
+err_column |
+err_constraint | ivfkt1_x_y_fkey
+
+-- foreign key reference match full
+CREATE TABLE ivfkt2 (x int, y int, FOREIGN KEY (x, y) REFERENCES ivpkt1 MATCH FULL);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivfkt2 VALUES (1, NULL);
+$$);
+err_sqlstate | 23503
+err_message | insert or update on table "ivfkt2" violates foreign key constraint "ivfkt2_x_y_fkey"
+err_detail | MATCH FULL does not allow mixing of null and nonnull key values.
+err_datatype |
+err_schema | public
+err_table | ivfkt2
+err_column |
+err_constraint | ivfkt2_x_y_fkey
+
+CREATE TABLE ivfkt3 (x int, y int);
+INSERT INTO ivfkt3 VALUES (1, NULL);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivfkt3 ADD FOREIGN KEY (x, y) REFERENCES ivpkt1 MATCH FULL;
+$$);
+err_sqlstate | 23503
+err_message | insert or update on table "ivfkt3" violates foreign key constraint "ivfkt3_x_y_fkey"
+err_detail | MATCH FULL does not allow mixing of null and nonnull key values.
+err_datatype |
+err_schema | public
+err_table | ivfkt3
+err_column |
+err_constraint | ivfkt3_x_y_fkey
+
+DROP TABLE ivfkt1, ivfkt2, ivfkt3, ivpkt1, ivpkt2;
+-- table exclusion
+CREATE TABLE ivet1 (n int, EXCLUDE (n WITH =));
+INSERT INTO ivet1 VALUES (1);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivet1 VALUES (1);
+$$);
+err_sqlstate | 23P01
+err_message | conflicting key value violates exclusion constraint "ivet1_n_excl"
+err_detail | Key (n)=(1) conflicts with existing key (n)=(1).
+err_datatype |
+err_schema | public
+err_table | ivet1
+err_column |
+err_constraint | ivet1_n_excl
+
+-- alter table exclusion
+CREATE TABLE ivet2 (n int);
+INSERT INTO ivet2 VALUES (1), (1);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivet2 ADD EXCLUDE (n WITH =);
+$$);
+err_sqlstate | 23P01
+err_message | could not create exclusion constraint "ivet2_n_excl"
+err_detail | Key (n)=(1) conflicts with key (n)=(1).
+err_datatype |
+err_schema | public
+err_table | ivet2
+err_column |
+err_constraint | ivet2_n_excl
+
+DROP TABLE ivet1, ivet2;
+-- domain
+CREATE DOMAIN ivd1 int NOT NULL CHECK (VALUE < 5);
+CREATE TABLE ivdt1 (n ivd1);
+SELECT * FROM integrity_error_record($$
+ SELECT NULL::ivd1;
+$$);
+err_sqlstate | 23502
+err_message | domain ivd1 does not allow null values
+err_detail |
+err_datatype | ivd1
+err_schema | public
+err_table |
+err_column |
+err_constraint |
+
+SELECT * FROM integrity_error_record($$
+ SELECT 10::ivd1;
+$$);
+err_sqlstate | 23514
+err_message | value for domain ivd1 violates check constraint "ivd1_check"
+err_detail |
+err_datatype | ivd1
+err_schema | public
+err_table |
+err_column |
+err_constraint | ivd1_check
+
+SELECT * FROM integrity_error_record($$
+ SELECT json_populate_record(NULL::ivdt1, '{"n":null}');
+$$);
+err_sqlstate | 23502
+err_message | domain ivd1 does not allow null values
+err_detail |
+err_datatype | ivd1
+err_schema | public
+err_table |
+err_column |
+err_constraint |
+
+SELECT * FROM integrity_error_record($$
+ SELECT json_populate_record(NULL::ivdt1, '{"n":10}');
+$$);
+err_sqlstate | 23514
+err_message | value for domain ivd1 violates check constraint "ivd1_check"
+err_detail |
+err_datatype | ivd1
+err_schema | public
+err_table |
+err_column |
+err_constraint | ivd1_check
+
+-- alter domain
+CREATE DOMAIN ivd2 int;
+CREATE TABLE ivdt2 (n ivd2);
+INSERT INTO ivdt2 VALUES (NULL), (10);
+SELECT * FROM integrity_error_record($$
+ ALTER DOMAIN ivd2 SET NOT NULL;
+$$);
+err_sqlstate | 23502
+err_message | column "n" of table "ivdt2" contains null values
+err_detail |
+err_datatype |
+err_schema | public
+err_table | ivdt2
+err_column | n
+err_constraint |
+
+SELECT * FROM integrity_error_record($$
+ ALTER DOMAIN ivd2 ADD CHECK (VALUE < 5);
+$$);
+err_sqlstate | 23514
+err_message | column "n" of table "ivdt2" contains values that violate the new constraint
+err_detail |
+err_datatype |
+err_schema | public
+err_table | ivdt2
+err_column | n
+err_constraint |
+
+DROP TABLE ivdt1, ivdt2;
+DROP DOMAIN ivd1, ivd2;
+-- table check
+CREATE TABLE ivct1 (n int, CHECK (n < 5));
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivct1 VALUES (10);
+$$);
+err_sqlstate | 23514
+err_message | new row for relation "ivct1" violates check constraint "ivct1_n_check"
+err_detail | Failing row contains (10).
+err_datatype |
+err_schema | public
+err_table | ivct1
+err_column |
+err_constraint | ivct1_n_check
+
+-- alter table check
+CREATE TABLE ivct2 (n int);
+INSERT INTO ivct2 VALUES (10);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivct2 ADD CHECK (n < 5);
+$$);
+err_sqlstate | 23514
+err_message | check constraint "ivct2_n_check" of relation "ivct2" is violated by some row
+err_detail |
+err_datatype |
+err_schema | public
+err_table | ivct2
+err_column |
+err_constraint | ivct2_n_check
+
+-- alter table validate check
+ALTER TABLE ivct2 ADD CONSTRAINT ivct2_check CHECK (n < 5) NOT VALID;
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivct2 VALIDATE CONSTRAINT ivct2_check;
+$$);
+err_sqlstate | 23514
+err_message | check constraint "ivct2_check" of relation "ivct2" is violated by some row
+err_detail |
+err_datatype |
+err_schema | public
+err_table | ivct2
+err_column |
+err_constraint | ivct2_check
+
+DROP TABLE ivct1, ivct2;
+-- no partitions
+CREATE TABLE ivpt1 (x int, y int, PRIMARY KEY (x, y)) PARTITION BY RANGE (y);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpt1 VALUES (10, 10);
+$$);
+err_sqlstate | 23514
+err_message | no partition of relation "ivpt1" found for row
+err_detail | Partition key of the failing row contains (y) = (10).
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+-- partition constraint
+CREATE TABLE ivpt1_p1 PARTITION OF ivpt1 FOR VALUES FROM (1) TO (5);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpt1 VALUES (10, 10);
+$$);
+err_sqlstate | 23514
+err_message | no partition of relation "ivpt1" found for row
+err_detail | Partition key of the failing row contains (y) = (10).
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpt1_p1 VALUES (10, 10);
+$$);
+err_sqlstate | 23514
+err_message | new row for relation "ivpt1_p1" violates partition constraint
+err_detail | Failing row contains (10, 10).
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+-- alter partition constraint
+CREATE TABLE ivpt1_p2 (LIKE ivpt1);
+INSERT INTO ivpt1_p2 VALUES (10, 10);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpt1 ATTACH PARTITION ivpt1_p2 FOR VALUES FROM (6) TO (10);
+$$);
+err_sqlstate | 23514
+err_message | partition constraint of relation "ivpt1_p2" is violated by some row
+err_detail |
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+-- conflict with default partition
+CREATE TABLE ivpt1_default PARTITION OF ivpt1 DEFAULT;
+INSERT INTO ivpt1 VALUES (10, 10);
+SELECT * FROM integrity_error_record($$
+ CREATE TABLE ivpt1_p3 PARTITION OF ivpt1 FOR VALUES FROM (10) TO (20);
+$$);
+err_sqlstate | 23514
+err_message | updated partition constraint for default partition "ivpt1_default" would be violated by some row
+err_detail |
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+CREATE TABLE ivpt1_p3 (LIKE ivpt1);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpt1 ATTACH PARTITION ivpt1_p3 FOR VALUES FROM (10) TO (20);
+$$);
+err_sqlstate | 23514
+err_message | updated partition constraint for default partition "ivpt1_default" would be violated by some row
+err_detail |
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+-- partition foreign key reference
+CREATE TABLE ivpt2 (x int, y int, FOREIGN KEY (x, y) REFERENCES ivpt1);
+INSERT INTO ivpt2 VALUES (10, 10);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpt1 DETACH PARTITION ivpt1_default;
+$$);
+err_sqlstate | 23503
+err_message | removing partition "ivpt1_default" violates foreign key constraint "ivpt2_x_y_fkey2"
+err_detail | Key (x, y)=(10, 10) is still referenced from table "ivpt2".
+err_datatype |
+err_schema |
+err_table |
+err_column |
+err_constraint |
+
+DROP TABLE ivpt1, ivpt1_p2, ivpt1_p2, ivpt1_p3, ivpt2;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index d2b17dd3ea..4fc2b0b467 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -55,7 +55,7 @@ test: create_index create_index_spgist create_view index_including index_includi
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors
+test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors integrity_errors
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/sql/integrity_errors.sql b/src/test/regress/sql/integrity_errors.sql
new file mode 100644
index 0000000000..1616d216ff
--- /dev/null
+++ b/src/test/regress/sql/integrity_errors.sql
@@ -0,0 +1,193 @@
+--
+-- Tests for integrity violation error fields
+--
+-- Errors in SQLSTATE class 23 (integrity constraint violation) should
+-- include the name of a database object as a separate field.
+--
+-- The fields of interest are shown at the same verbosity level as
+-- volatile details such as source code line numbers. To produce stable
+-- regression output, the following function returns a portion of the
+-- full error reported.
+CREATE FUNCTION integrity_error_record(
+ dml text,
+ OUT err_sqlstate text,
+ OUT err_message text,
+ OUT err_detail text,
+ OUT err_datatype text,
+ OUT err_schema text,
+ OUT err_table text,
+ OUT err_column text,
+ OUT err_constraint text)
+AS $$
+BEGIN
+ EXECUTE $1;
+EXCEPTION
+ WHEN integrity_constraint_violation THEN GET STACKED DIAGNOSTICS
+ err_sqlstate := RETURNED_SQLSTATE,
+ err_message := MESSAGE_TEXT,
+ err_detail := PG_EXCEPTION_DETAIL,
+ err_datatype := PG_DATATYPE_NAME,
+ err_schema := SCHEMA_NAME,
+ err_table := TABLE_NAME,
+ err_column := COLUMN_NAME,
+ err_constraint := CONSTRAINT_NAME;
+END;
+$$ LANGUAGE plpgsql;
+
+\pset expanded on
+\pset tuples_only on
+
+-- table not null
+CREATE TABLE ivnt1 (n int NOT NULL);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivnt1 VALUES (NULL);
+$$);
+
+-- alter table not null
+CREATE TABLE ivnt2 (n int);
+INSERT INTO ivnt2 VALUES (NULL);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivnt2 ALTER n SET NOT NULL;
+$$);
+DROP TABLE ivnt1, ivnt2;
+
+-- table unique
+CREATE TABLE ivpkt1 (x int, y int, PRIMARY KEY (x, y));
+INSERT INTO ivpkt1 VALUES (1, 2);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpkt1 VALUES (1, 2);
+$$);
+
+-- alter table unique
+CREATE TABLE ivpkt2 (x int, y int);
+INSERT INTO ivpkt2 VALUES (1, 2), (1, 2);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpkt2 ADD PRIMARY KEY (x, y);
+$$);
+
+-- table foreign key reference
+CREATE TABLE ivfkt1 (x int, y int, FOREIGN KEY (x, y) REFERENCES ivpkt1);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivfkt1 VALUES (10, 10);
+$$);
+INSERT INTO ivfkt1 VALUES (1, 2);
+SELECT * FROM integrity_error_record($$
+ DELETE FROM ivpkt1;
+$$);
+
+-- foreign key reference match full
+CREATE TABLE ivfkt2 (x int, y int, FOREIGN KEY (x, y) REFERENCES ivpkt1 MATCH FULL);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivfkt2 VALUES (1, NULL);
+$$);
+CREATE TABLE ivfkt3 (x int, y int);
+INSERT INTO ivfkt3 VALUES (1, NULL);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivfkt3 ADD FOREIGN KEY (x, y) REFERENCES ivpkt1 MATCH FULL;
+$$);
+DROP TABLE ivfkt1, ivfkt2, ivfkt3, ivpkt1, ivpkt2;
+
+-- table exclusion
+CREATE TABLE ivet1 (n int, EXCLUDE (n WITH =));
+INSERT INTO ivet1 VALUES (1);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivet1 VALUES (1);
+$$);
+
+-- alter table exclusion
+CREATE TABLE ivet2 (n int);
+INSERT INTO ivet2 VALUES (1), (1);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivet2 ADD EXCLUDE (n WITH =);
+$$);
+DROP TABLE ivet1, ivet2;
+
+-- domain
+CREATE DOMAIN ivd1 int NOT NULL CHECK (VALUE < 5);
+CREATE TABLE ivdt1 (n ivd1);
+SELECT * FROM integrity_error_record($$
+ SELECT NULL::ivd1;
+$$);
+SELECT * FROM integrity_error_record($$
+ SELECT 10::ivd1;
+$$);
+SELECT * FROM integrity_error_record($$
+ SELECT json_populate_record(NULL::ivdt1, '{"n":null}');
+$$);
+SELECT * FROM integrity_error_record($$
+ SELECT json_populate_record(NULL::ivdt1, '{"n":10}');
+$$);
+
+-- alter domain
+CREATE DOMAIN ivd2 int;
+CREATE TABLE ivdt2 (n ivd2);
+INSERT INTO ivdt2 VALUES (NULL), (10);
+SELECT * FROM integrity_error_record($$
+ ALTER DOMAIN ivd2 SET NOT NULL;
+$$);
+SELECT * FROM integrity_error_record($$
+ ALTER DOMAIN ivd2 ADD CHECK (VALUE < 5);
+$$);
+DROP TABLE ivdt1, ivdt2;
+DROP DOMAIN ivd1, ivd2;
+
+-- table check
+CREATE TABLE ivct1 (n int, CHECK (n < 5));
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivct1 VALUES (10);
+$$);
+
+-- alter table check
+CREATE TABLE ivct2 (n int);
+INSERT INTO ivct2 VALUES (10);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivct2 ADD CHECK (n < 5);
+$$);
+
+-- alter table validate check
+ALTER TABLE ivct2 ADD CONSTRAINT ivct2_check CHECK (n < 5) NOT VALID;
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivct2 VALIDATE CONSTRAINT ivct2_check;
+$$);
+DROP TABLE ivct1, ivct2;
+
+-- no partitions
+CREATE TABLE ivpt1 (x int, y int, PRIMARY KEY (x, y)) PARTITION BY RANGE (y);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpt1 VALUES (10, 10);
+$$);
+
+-- partition constraint
+CREATE TABLE ivpt1_p1 PARTITION OF ivpt1 FOR VALUES FROM (1) TO (5);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpt1 VALUES (10, 10);
+$$);
+SELECT * FROM integrity_error_record($$
+ INSERT INTO ivpt1_p1 VALUES (10, 10);
+$$);
+
+-- alter partition constraint
+CREATE TABLE ivpt1_p2 (LIKE ivpt1);
+INSERT INTO ivpt1_p2 VALUES (10, 10);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpt1 ATTACH PARTITION ivpt1_p2 FOR VALUES FROM (6) TO (10);
+$$);
+
+-- conflict with default partition
+CREATE TABLE ivpt1_default PARTITION OF ivpt1 DEFAULT;
+INSERT INTO ivpt1 VALUES (10, 10);
+SELECT * FROM integrity_error_record($$
+ CREATE TABLE ivpt1_p3 PARTITION OF ivpt1 FOR VALUES FROM (10) TO (20);
+$$);
+CREATE TABLE ivpt1_p3 (LIKE ivpt1);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpt1 ATTACH PARTITION ivpt1_p3 FOR VALUES FROM (10) TO (20);
+$$);
+
+-- partition foreign key reference
+CREATE TABLE ivpt2 (x int, y int, FOREIGN KEY (x, y) REFERENCES ivpt1);
+INSERT INTO ivpt2 VALUES (10, 10);
+SELECT * FROM integrity_error_record($$
+ ALTER TABLE ivpt1 DETACH PARTITION ivpt1_default;
+$$);
+DROP TABLE ivpt1, ivpt1_p2, ivpt1_p2, ivpt1_p3, ivpt2;
--
2.11.0
>From 6f2ccc824c7ef82408a3c35ff73b9e0eb4c23486 Mon Sep 17 00:00:00 2001
From: Chris Bandy <bandy.ch...@gmail.com>
Date: Fri, 6 Mar 2020 21:02:52 -0600
Subject: [PATCH 2/2] Add object names to partition integrity violations
All errors of SQLSTATE class 23 should include the name of an object
associated with the error.
---
src/backend/commands/tablecmds.c | 6 ++++--
src/backend/executor/execMain.c | 3 ++-
src/backend/executor/execPartition.c | 3 ++-
src/backend/partitioning/partbounds.c | 3 ++-
src/backend/utils/adt/ri_triggers.c | 3 ++-
src/test/regress/expected/integrity_errors.out | 30 +++++++++++++-------------
6 files changed, 27 insertions(+), 21 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7a13b97164..054db26099 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5342,12 +5342,14 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("updated partition constraint for default partition \"%s\" would be violated by some row",
- RelationGetRelationName(oldrel))));
+ RelationGetRelationName(oldrel)),
+ errtable(oldrel)));
else
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("partition constraint of relation \"%s\" is violated by some row",
- RelationGetRelationName(oldrel))));
+ RelationGetRelationName(oldrel)),
+ errtable(oldrel)));
}
/* Write the tuple out to the new relation */
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 28130fbc2b..4fdffad6f3 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1878,7 +1878,8 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("new row for relation \"%s\" violates partition constraint",
RelationGetRelationName(resultRelInfo->ri_RelationDesc)),
- val_desc ? errdetail("Failing row contains %s.", val_desc) : 0));
+ val_desc ? errdetail("Failing row contains %s.", val_desc) : 0,
+ errtable(resultRelInfo->ri_RelationDesc)));
}
/*
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index c13b1d3501..a5542b92c7 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -345,7 +345,8 @@ ExecFindPartition(ModifyTableState *mtstate,
RelationGetRelationName(rel)),
val_desc ?
errdetail("Partition key of the failing row contains %s.",
- val_desc) : 0));
+ val_desc) : 0,
+ errtable(rel)));
}
if (partdesc->is_leaf[partidx])
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 35953f23fa..4c47f54a57 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -1366,7 +1366,8 @@ check_default_partition_contents(Relation parent, Relation default_rel,
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("updated partition constraint for default partition \"%s\" would be violated by some row",
- RelationGetRelationName(default_rel))));
+ RelationGetRelationName(default_rel)),
+ errtable(default_rel)));
ResetExprContext(econtext);
CHECK_FOR_INTERRUPTS();
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 4ab7cda110..bb49e80d16 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -2452,7 +2452,8 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
NameStr(riinfo->conname)),
errdetail("Key (%s)=(%s) is still referenced from table \"%s\".",
key_names.data, key_values.data,
- RelationGetRelationName(fk_rel))));
+ RelationGetRelationName(fk_rel)),
+ errtableconstraint(fk_rel, NameStr(riinfo->conname))));
else if (onfk)
ereport(ERROR,
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
diff --git a/src/test/regress/expected/integrity_errors.out b/src/test/regress/expected/integrity_errors.out
index e75e6b722f..14796a99c9 100644
--- a/src/test/regress/expected/integrity_errors.out
+++ b/src/test/regress/expected/integrity_errors.out
@@ -316,8 +316,8 @@ err_sqlstate | 23514
err_message | no partition of relation "ivpt1" found for row
err_detail | Partition key of the failing row contains (y) = (10).
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt1
err_column |
err_constraint |
@@ -330,8 +330,8 @@ err_sqlstate | 23514
err_message | no partition of relation "ivpt1" found for row
err_detail | Partition key of the failing row contains (y) = (10).
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt1
err_column |
err_constraint |
@@ -342,8 +342,8 @@ err_sqlstate | 23514
err_message | new row for relation "ivpt1_p1" violates partition constraint
err_detail | Failing row contains (10, 10).
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt1_p1
err_column |
err_constraint |
@@ -357,8 +357,8 @@ err_sqlstate | 23514
err_message | partition constraint of relation "ivpt1_p2" is violated by some row
err_detail |
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt1_p2
err_column |
err_constraint |
@@ -372,8 +372,8 @@ err_sqlstate | 23514
err_message | updated partition constraint for default partition "ivpt1_default" would be violated by some row
err_detail |
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt1_default
err_column |
err_constraint |
@@ -385,8 +385,8 @@ err_sqlstate | 23514
err_message | updated partition constraint for default partition "ivpt1_default" would be violated by some row
err_detail |
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt1_default
err_column |
err_constraint |
@@ -400,9 +400,9 @@ err_sqlstate | 23503
err_message | removing partition "ivpt1_default" violates foreign key constraint "ivpt2_x_y_fkey2"
err_detail | Key (x, y)=(10, 10) is still referenced from table "ivpt2".
err_datatype |
-err_schema |
-err_table |
+err_schema | public
+err_table | ivpt2
err_column |
-err_constraint |
+err_constraint | ivpt2_x_y_fkey2
DROP TABLE ivpt1, ivpt1_p2, ivpt1_p2, ivpt1_p3, ivpt2;
--
2.11.0
