From: | Miles Elam <miles(dot)elam(at)productops(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Check constraint failure messages |
Date: | 2021-04-09 00:20:10 |
Message-ID: | CAALojA-=iKKp-YuHwY=RKT9ow=AdKKG1iYK674AVw5PdTcXTzg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)elam(at)productops(dot)com> wrote:
> On Tue, Apr 6, 2021 at 1:59 PM Ron <ronljohnsonjr(at)gmail(dot)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 | Date | Subject | |
---|---|---|---|
Next Message | Siddhartha2485 | 2021-04-09 01:43:58 | Re: ERROR : invalid transaction termination : PostgreSQL v12 |
Previous Message | Kevin Brannen | 2021-04-08 20:32:19 | RE: Check constraint failure messages |