On 3/18/20 6:56 AM, Amit Kapila wrote:
> On Thu, Mar 12, 2020 at 7:46 PM Amit Kapila <amit.kapil...@gmail.com> wrote:
>>
>> On Wed, Mar 11, 2020 at 8:51 PM Chris Bandy <bandy.ch...@gmail.com> wrote:
>>>
>>> On 3/11/20 6:29 AM, Amit Kapila wrote:
>>>>
>>>> I have tried with git am as well, but it failed. I am not sure what
>>>> is the reason. Can you please once check at your end?
>>>
>>> Yes, sorry. This set (and v3 and v4) should work with -p0. Any following
>>> patches from me will use the normal -p1.
>>>
>>
>> Okay.
>>
>
> I again tried the latest patch v5 both with -p1 and -p0, but it gives
> an error while applying the patch. Can you send a patch that we can
> apply with patch -p1 or git-am?
>
> [1] -
> https://www.postgresql.org/message-id/0731def8-978e-0285-04ee-582762729b38%40gmail.com
>
Sorry for these troubles. Attached are patches created using `git
format-patch -n -v6` on master at 487e9861d0.
Thanks,
Chris
>From 6e5e47dc9a816c8d3e3453759da5ea0dcbeea31a 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 v6 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 6e65ef7d5cb1f8b722b0fabad8b2e4b0e965563c 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 v6 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 729025470d..8e35c5bd1a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5343,12 +5343,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 ef74ad85ff..fb6ce49056 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -346,7 +346,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
