Обсуждение: Check constraint failure messages
I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error message
ERROR: value for domain po.email violates check constraint "email_check" SQL state: 23514
When inserting 1000+ entries in a batch, finding the exact entry with the problem is noticeably harder than with other error types. For example when a column should be a uuid but you pass in 'Mary had a little lamb', the error message tells you what the invalid value is as well as the column name you're trying to put it into.
Are there any quick hacks floating around out there to solve or at least mitigate this?
- Miles
On 4/6/21 2:40 PM, Miles Elam wrote:
Is it a deferred constraint?
I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error messageERROR: value for domain po.email violates check constraint "email_check" SQL state: 23514When inserting 1000+ entries in a batch, finding the exact entry with the problem is noticeably harder than with other error types. For example when a column should be a uuid but you pass in 'Mary had a little lamb', the error message tells you what the invalid value is as well as the column name you're trying to put it into.Are there any quick hacks floating around out there to solve or at least mitigate this?
Is it a deferred constraint?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr@gmail.com> wrote:
On 4/6/21 2:40 PM, Miles Elam wrote:I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error messageERROR: value for domain po.email violates check constraint "email_check" SQL state: 23514When inserting 1000+ entries in a batch, finding the exact entry with the problem is noticeably harder than with other error types. For example when a column should be a uuid but you pass in 'Mary had a little lamb', the error message tells you what the invalid value is as well as the column name you're trying to put it into.Are there any quick hacks floating around out there to solve or at least mitigate this?
Is it a deferred constraint?
Plain ole domain CHECK constraint.
CREATE DOMAIN po.email AS varcharCHECK (VALUE IS NULL OR (po.length_in(VALUE, 1, 254) AND NOT po.email_expanded(VALUE) IS NULL));
where "po" is another schema, po.length_in(...) is an IMMUTABLE range check, and po.email_expanded(...) is a function returning a record. Same behavior happens if I remove the functions and define the check constraint in place. The only info returned in a bulk insert is the name of the violated check constraint, aka email_check.
An example table using it is defined as follows
CREATE TABLE IF NOT EXISTS profile (id uuid PRIMARY KEY,email po.email NOT NULL,
manager_email po.email NOT NULL
);
Nothing fancy.
INSERT INTO profile (id, email, manager_email) VALUES(gen_random_uuid(), 'user1@example.com', 'manager1@example.com'),(gen_random_uuid(), 'user2@example.com', 'manager2@example.com'),
(gen_random_uuid(), 'user3&example.com', 'manager3@example.com'),
(gen_random_uuid(), 'user4@example.com', 'manager4.example.com'),
(gen_random_uuid(), 'user5@example.com', 'manager5@example.com');
Inserting this batch will tell me that there was an error and that it was "email_check" that failed, but no indication that the 3rd user's email address or the 4th user's manager email was the problem, forcing a bisect operation among 1,000+ entries to find the first error, then bisect from there to find the second error if any, and repeat until no more constraint errors.
- Miles
On 4/6/21 3:50 PM, Miles Elam wrote:
The blunt force answer is to not use bulk inserts. Try COPY; it's good at saying which record throws an error.
On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr@gmail.com> wrote:On 4/6/21 2:40 PM, Miles Elam wrote:I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error messageERROR: value for domain po.email violates check constraint "email_check" SQL state: 23514When inserting 1000+ entries in a batch, finding the exact entry with the problem is noticeably harder than with other error types. For example when a column should be a uuid but you pass in 'Mary had a little lamb', the error message tells you what the invalid value is as well as the column name you're trying to put it into.Are there any quick hacks floating around out there to solve or at least mitigate this?
Is it a deferred constraint?Plain ole domain CHECK constraint.CREATE DOMAIN po.email AS varcharCHECK (VALUE IS NULL OR (po.length_in(VALUE, 1, 254) AND NOT po.email_expanded(VALUE) IS NULL));where "po" is another schema, po.length_in(...) is an IMMUTABLE range check, and po.email_expanded(...) is a function returning a record. Same behavior happens if I remove the functions and define the check constraint in place. The only info returned in a bulk insert is the name of the violated check constraint, aka email_check.
The blunt force answer is to not use bulk inserts. Try COPY; it's good at saying which record throws an error.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On Tue, Apr 6, 2021 at 1:59 PM Ron <ronljohnsonjr@gmail.com> wrote:
The blunt force answer is to not use bulk inserts. Try COPY; it's good at saying which record throws an error.
Sadly, this is a cloud-managed database without direct access to 5432 from outside the VPC and bastian instances are frowned upon by our security folks. Guess I'm stuck with bisecting. Thanks for the confirmation.
From: Miles Elam <miles.elam@productops.com>
Sent: Tuesday, April 6, 2021 4:19 PM
Sadly, this is a cloud-managed database without direct access to 5432 from outside the VPC and bastian instances are frowned upon by our security folks. Guess I'm stuck with bisecting. Thanks for the confirmation.
====================
It'd slow you down a little, but you could install a before trigger to do the check and if it fails you'll have all the info you need and can call RAISE to communicate a record identifier, bad value, whatever.
If performance is really a big deal, set up the trigger only after you get a failure so you only have to run it on the 'bad batches'.
Or else write a precheck program that looks at the data first and looks only for this and therefore can report the exact record before it feeds the good data to your real loader.
Or there's probably other ways I'm not thinking of. The question is what options does your process allow, I suppose.
HTH,
Kevin
.
Following up in case someone else runs into this problem. I changed the function the CHECK statement called to raise a warning. Not perfect, but noticeably better. I don't get the column that failed but I do get what bad input gummed things up.
CREATE OR REPLACE FUNCTION po.confirm(p_val anyelement, p_validated boolean)
RETURNS boolean LANGUAGE plpgsql STRICT IMMUTABLE PARALLEL SAFE AS $$
BEGIN
IF NOT p_validated THEN
RAISE WARNING 'Invalid value: %', p_val;
END IF;
RETURN p_validated;
END;
$$;
COMMENT ON FUNCTION po.confirm(anyelement,boolean) IS
'Raises a warning when a condition is false; useful for outputting CHECK constraint error values.';CREATE DOMAIN po.email AS varcharCHECK (po.confirm(VALUE, VALUE IS NULL OR NOT po.email_expanded(VALUE) IS NULL));
Code is not seamless or DRY, but manageable.
- Miles
On Tue, Apr 6, 2021 at 2:18 PM Miles Elam <miles.elam@productops.com> wrote:
On Tue, Apr 6, 2021 at 1:59 PM Ron <ronljohnsonjr@gmail.com> wrote:
The blunt force answer is to not use bulk inserts. Try COPY; it's good at saying which record throws an error.Sadly, this is a cloud-managed database without direct access to 5432 from outside the VPC and bastian instances are frowned upon by our security folks. Guess I'm stuck with bisecting. Thanks for the confirmation.
An error message that includes the table name and column name would also be helpful for non-bulk inserts: a single insert where multiple columns of a table have the same domain type. The problem gets worse when there are inserts into multiple tables that have the same domain types (multiple sql inserts in a statement).
Am 06.04.21 um 22:58 schrieb Ron:
On 4/6/21 3:50 PM, Miles Elam wrote:On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr@gmail.com> wrote:On 4/6/21 2:40 PM, Miles Elam wrote:I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error messageERROR: value for domain po.email violates check constraint "email_check" SQL state: 23514When inserting 1000+ entries in a batch, finding the exact entry with the problem is noticeably harder than with other error types. For example when a column should be a uuid but you pass in 'Mary had a little lamb', the error message tells you what the invalid value is as well as the column name you're trying to put it into.Are there any quick hacks floating around out there to solve or at least mitigate this?
Is it a deferred constraint?Plain ole domain CHECK constraint.CREATE DOMAIN po.email AS varcharCHECK (VALUE IS NULL OR (po.length_in(VALUE, 1, 254) AND NOT po.email_expanded(VALUE) IS NULL));where "po" is another schema, po.length_in(...) is an IMMUTABLE range check, and po.email_expanded(...) is a function returning a record. Same behavior happens if I remove the functions and define the check constraint in place. The only info returned in a bulk insert is the name of the violated check constraint, aka email_check.
The blunt force answer is to not use bulk inserts. Try COPY; it's good at saying which record throws an error.--
Angular momentum makes the world go 'round.
On Sun, Oct 3, 2021, 14:39 zim <zimtigerclaw@outlook.com> wrote:
An error message that includes the table name and column name would also be helpful for non-bulk inserts:
This is not limited to domain types and comes up fairly often. In general there is agreement that the current behavior is not great but implementing something better seems to be more challenging than the perceived benefit warrants.
David J.