Check constraint failure messages

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.

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

On 4/6/21 3:50 PM, Miles Elam wrote:

On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr@gmail.com
<mailto: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.

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
.
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.

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:

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 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.