From 101bb413634f4be82a6d934660ceda99e4f4cc53 Mon Sep 17 00:00:00 2001 From: Chris Bandy 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