Re: CHECK that involves a function call behaves differently during bulk load?

On Thu, Mar 13, 2025 at 2:05 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thursday, March 13, 2025, Kirk Parker <khp(at)equatoria(dot)us> wrote:
>>
>>
>> CREATE FUNCTION public.valid_upc_ean(target character varying)
>> RETURNS boolean
>> LANGUAGE plpgsql
>> AS $$
>> DECLARE
>> len integer;
>> holder varchar;
>>
>> begin
>> if target is null then
>> return true;
>> end if;
>> len = length(trim(target));
>> if len = 12 then
>> holder := target::upc;
>> return true;
>> elsif len = 13 then
>> holder := target::ean13;
>> return true;
>> elsif len = 0 then
>> return true;
>> else
>> return false;
>> end if;
>> exception when others then
>> return false;
>> end;
>> $$;
>>
>> It's used like this:
>>
>> CREATE TABLE public.inv_variant
>> (
>> id integer NOT NULL,
>> prod_id integer NOT NULL,
>> sku character varying(126) NOT NULL,
>> upc character varying(14),
>> ...
>> CONSTRAINT var_upc_check CHECK (public.valid_upc_ean(upc))
>> );
>>
>> As mentioned about, the function fires and works perfectly once the data
>> is loaded, but the bulk load gives this error:
>>
>> ERROR: new row for relation "inv_variant" violates check constraint
>> "var_upc_check"
>> DETAIL: Failing row contains (2264, 2170, BOX-1, 012345678912, ...
>>
>> Every row with NULL in this column inserts successfully, every one with
>> data fails in this way. The data itself is valid -- I can copy the text
>> starting with the '(' from the DETAIL: Failing row... message, paste it
>> after "insert into inv_variant", and it executes perfectly--this in the
>> very same psql where that exact set of insert data is failing on the
>> redirected dump-file input.
>>
>> I am quite at a loss about how to even go about troubleshooting this.
>>
>
> Not discarding useful error messages as you do in the exception block is a
> good start.
>
>
>>
>> Since valid_upc_ean() is a pure function with no side effects or
>> references to anything other than the table in question,
>>
>>
> And two non-core data types that lack schema qualifications.
>
> David J.
>
>
Schema-qualifying the casts to non-core types did the trick, thanks!

But it does raise two questions:

(1) why does it work with individual inserts sent via psql, but not with
the redirected input from the dump?
(2) everything is in the public schema so why wouldn't it find the type
anyway?

Finally, is this maybe something to take up on the documentation list?
There are quite a few example code snippets in the PL/pgSQL chapter, and
not a single instance of a schema-qualifying name among them.