Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jEh7k-0006qN-A3 for pgsql-hackers@arkaria.postgresql.org; Wed, 18 Mar 2020 22:25:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jEh7j-0003aX-14 for pgsql-hackers@arkaria.postgresql.org; Wed, 18 Mar 2020 22:25:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jEh7i-0003aN-DN for pgsql-hackers@lists.postgresql.org; Wed, 18 Mar 2020 22:25:14 +0000 Received: from mail-oi1-x242.google.com ([2607:f8b0:4864:20::242]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jEh7f-0004bl-34 for pgsql-hackers@lists.postgresql.org; Wed, 18 Mar 2020 22:25:13 +0000 Received: by mail-oi1-x242.google.com with SMTP id d63so568084oig.6 for ; Wed, 18 Mar 2020 15:25:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=kBJRan/niRIcQuZ9BRklNOGFkS8KSDj2wWPt2HaKCtk=; b=UmRL74loPAZvaW7nZpOeWYUG89NoBoYMShYLbVzNV9WBP4HFSgmaX/o6h3TwhunNTP 7w+v9ryO1QbpXuWbfXcHgHyBi9thoGAjCIyFyHihQDdidykqxNwhSIzbPTpt11VRjBfe ZoUL3nDilkdj3LFrZ3gZniu7FfuzTjIRRbgXuTtRTOSjLy44ssy9dTZgAtK031H5N5f6 YQvcm0atNevA2lQJeqBo9QjbX7pOBys/am+K3ajNolrNu9bsYIibAEnOa2hy9qRDeMLQ MnRrPEz9hY7GNyXQ1/qpGO6b9S1U2OHHTvAXaBKqKkr5wAs/O/An9/H9WwyQaKZuZLWb mdYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=kBJRan/niRIcQuZ9BRklNOGFkS8KSDj2wWPt2HaKCtk=; b=K+ta086iXBpikSZzIOKJZM+13csj2vbpj78UqDK5S55JnKfic1ealseuAA/PGaKuQH GUuM9Uj12zpyZH4SeN1LY5TEIUh9m8TJpiepOCI5oG9d4veJDbEG+9PKXBDt8VnnklW7 uJ9xA+49/QqTGhissbLdHaIF1WuU9J2d5D9VPiUlUN+D2Jrxgp/7g9GkhrNsziKLkClC 6HUqIIjy02QHcL75qRc8QzvxEQIW+Uk5JJ0xIkMQrQFjNwg170HH7OUEncvAg6fZU8AC eM2ZGODxw4nxup5+XPjDgoK7UMizQ4GhNuwTTCpV+rgZyPoSVisx1Ec7ybyI9Bfizh2i VW8Q== X-Gm-Message-State: ANhLgQ2hXzzUT4sLbD7SSNQSpMfUGMTu8te9vu9KQQE+bLi+NQ9HM6vA ud/1saqG3BcZk+KTJu0ExBpdSTz5ZiU= X-Google-Smtp-Source: ADFU+vu4vWgT/+9I942lr8eaakOna8b8DIKBLAsSe1Yk8Ozyzp0yPxTXU46Xi60bEIXp4LmsWcFmqw== X-Received: by 2002:aca:1c13:: with SMTP id c19mr154765oic.47.1584570309427; Wed, 18 Mar 2020 15:25:09 -0700 (PDT) Received: from [100.115.92.206] (cpe-70-119-96-135.tx.res.rr.com. [70.119.96.135]) by smtp.gmail.com with ESMTPSA id g39sm118551otb.26.2020.03.18.15.25.07 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 18 Mar 2020 15:25:08 -0700 (PDT) Subject: Re: [PATCH] Add schema and table names to partition error To: Amit Kapila Cc: PostgreSQL Hackers References: <0aa113a3-3c7f-db48-bcd8-f9290b2269ae@gmail.com> <0acc36c9-1aef-1057-0c29-de473c850605@gmail.com> <4037ab52-27e4-bbfb-d618-adbab81125e2@gmail.com> From: Chris Bandy Message-ID: <53769c1d-17da-d019-8273-33db36569f44@gmail.com> Date: Wed, 18 Mar 2020 17:25:05 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------7610F20B8BD8B09665E6DC43" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------7610F20B8BD8B09665E6DC43 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit On 3/18/20 6:56 AM, Amit Kapila wrote: > On Thu, Mar 12, 2020 at 7:46 PM Amit Kapila wrote: >> >> On Wed, Mar 11, 2020 at 8:51 PM Chris Bandy 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 --------------7610F20B8BD8B09665E6DC43 Content-Type: text/x-patch; charset=UTF-8; name="v6-0001-Add-tests-for-integrity-violation-error-fields.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="v6-0001-Add-tests-for-integrity-violation-error-fields.patch" From 6e5e47dc9a816c8d3e3453759da5ea0dcbeea31a Mon Sep 17 00:00:00 2001 From: Chris Bandy 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 --------------7610F20B8BD8B09665E6DC43 Content-Type: text/x-patch; charset=UTF-8; name="v6-0002-Add-object-names-to-partition-integrity-violation.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="v6-0002-Add-object-names-to-partition-integrity-violation.pa"; filename*1="tch" From 6e65ef7d5cb1f8b722b0fabad8b2e4b0e965563c Mon Sep 17 00:00:00 2001 From: Chris Bandy 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 --------------7610F20B8BD8B09665E6DC43--