Thread: Check constraint failure messages

Check constraint failure messages

From
Miles Elam
Date:
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

Re: Check constraint failure messages

From
Ron
Date:
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 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?

Is it a deferred constraint?

--
Angular momentum makes the world go 'round.

Re: Check constraint failure messages

From
Miles Elam
Date:
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 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?

Is it a deferred constraint?
 
Plain ole domain CHECK constraint.

CREATE DOMAIN po.email AS varchar
  CHECK (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

Re: Check constraint failure messages

From
Ron
Date:
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 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?

Is it a deferred constraint?
 
Plain ole domain CHECK constraint.

CREATE DOMAIN po.email AS varchar
  CHECK (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.

Re: Check constraint failure messages

From
Miles Elam
Date:
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.

RE: Check constraint failure messages

From
Kevin Brannen
Date:

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

.

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

Re: Check constraint failure messages

From
Miles Elam
Date:
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 varchar
  CHECK (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.

Re: Check constraint failure messages

From
zim
Date:
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 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?

Is it a deferred constraint?
 
Plain ole domain CHECK constraint.

CREATE DOMAIN po.email AS varchar
  CHECK (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.

Re: Check constraint failure messages

From
"David G. Johnston"
Date:
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.