Thread: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

From

David Gauthier

Date:

17 July 2021, 01:26:17

This stored procedure ...

create or replace function validate_proj_csv (proj_csv varchar)
returns int
language plpgsql
as
$$

-- This function used in a check constraint in the public.projects table to ensure that
-- all projects in column sibling_project_csv are valid projects.

DECLARE
  proj_arr varchar[];
  see_prj int;
  
BEGIN

  proj_arr := regexp_split_to_array(proj_csv,',');
  
  for x in 1 .. array_upper(proj_arr,1)
  loop
    select 1 into see_prj from public.projects where project = proj_arr[x];
    if (see_prj is null) then
      raise notice 'Project "%" in project csv "%" is not a valid project.', proj_arr[x],proj_csv;
      return 0;
    end if;

  end loop;
    
  return 1;

END;
$$ ;

... works fine...

dvdb=# select validate_proj_csv('sabin,strix2,ipu1.0');
 validate_proj_csv 
-------------------
                 1
(1 row)

dvdb=# select validate_proj_csv('sabin,strix2,ipu1.00');
NOTICE:  Project "ipu1.00" in project csv "sabin,strix2,ipu1.00" is not a valid project.
 validate_proj_csv 
-------------------
                 0
(1 row)

But when I try to use it in a check constraint....

dvdb=# alter table projects add constraint validate_sibling_project_csv check (validate_proj_csv(sibling_project_csv) = 0);
ERROR:  upper bound of FOR loop cannot be null
CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14 at FOR with integer loop variable

What's going on ?

How to get this to work ?

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

From

Adrian Klaver

Date:

17 July 2021, 01:30:51

On 7/16/21 3:26 PM, David Gauthier wrote:
> This stored procedure ...

> dvdb=# alter table projects add constraint validate_sibling_project_csv 
> check (validate_proj_csv(sibling_project_csv) = 0);
> ERROR:  upper bound of FOR loop cannot be null
> CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14 
> at FOR with integer loop variable
> 
> What's going on ?

I'm going to say you have a NULL value in sibling_project_csv in the table.

> How to get this to work ?
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

From

"David G. Johnston"

Date:

17 July 2021, 04:01:33

On Fri, Jul 16, 2021 at 3:26 PM David Gauthier <davegauthierpg@gmail.com> wrote:

This stored procedure ...

create or replace function validate_proj_csv (proj_csv varchar)

It is a function - I don't think you can used stored procedures in check constraints...

dvdb=# alter table projects add constraint validate_sibling_project_csv check (validate_proj_csv(sibling_project_csv) = 0);

ERROR: upper bound of FOR loop cannot be null
CONTEXT: PL/pgSQL function validate_proj_csv(character varying) line 14 at FOR with integer loop variable

I'm actually surprised this alter command worked at all since you are violating a requirement for check constraints - namely that the expression be immutable. Your function, regardless of its declaration (which is default volatile), is not immutable.

How to get this to work ?

You really need to re-write this as a trigger function.

David J.

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

From

Alban Hertroys

Date:

17 July 2021, 13:36:21

> On 17 Jul 2021, at 0:26, David Gauthier <davegauthierpg@gmail.com> wrote:

(…)

> dvdb=# select validate_proj_csv('sabin,strix2,ipu1.0');
>  validate_proj_csv
> -------------------
>                  1
> (1 row)
>
> dvdb=# select validate_proj_csv('sabin,strix2,ipu1.00');
> NOTICE:  Project "ipu1.00" in project csv "sabin,strix2,ipu1.00" is not a valid project.
>  validate_proj_csv
> -------------------
>                  0
> (1 row)
>
>
> But when I try to use it in a check constraint....
>
> dvdb=# alter table projects add constraint validate_sibling_project_csv check (validate_proj_csv(sibling_project_csv)
=0); 
> ERROR:  upper bound of FOR loop cannot be null
> CONTEXT:  PL/pgSQL function validate_proj_csv(character varying) line 14 at FOR with integer loop variable
>
> What's going on ?
> How to get this to work ?

As people advised you previously, you would probably be better off normalising your table.

For example, add a table for the links:

create table project_sibling (
    project    text not null
        references public.projects(project)
            on update cascade
            on delete cascade
,    sibling text not null
        references public.projects(project)
            on update cascade
            on delete cascade
,    primary key (project, sibling)
);

-- Populate it from public.projects initially
insert into project_sibling(project, sibling)
select p.project, s.sibling
  from public.projects p
 cross join lateral regex_split_to_table(project_csv, ',') s(sibling)
;

I had to make a few guesses there, as I don’t have your original table structure, but that’s the gist of it.

If that project_csv column gets populated by some external application, you could keep the link-table updated with
insert/update/deletetriggers. 

Alternatively, a writable view replacing public.projects may be a possibility.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

From

Adrian Klaver

Date:

17 July 2021, 20:33:41

On 7/17/21 10:13 AM, David Gauthier wrote:
Please reply to list also.
Ccing list.

> That was it.  I added...
> 
>    if (proj_csv is null) then
>      return(1);
>    end if;
> 
> ... and it works fine.
> 
> Back in earlier versions of PG (I'm talking v8), existing column values 
> weren't checked.  I remember you had to manually run a query using your 
> stored procedure to see if there would be any existing violations before 
> creating the constraint.  The way it is now is an improvement, much better.

Glad it worked. Just be aware that using a function as a check 
constraint is skirting the rules for CHECK as explained here:

https://www.postgresql.org/docs/current/sql-createtable.html

There have been multiple posts to --general where that has come back to 
bite someone. Generally because the function reaches out to some other 
object in the database, which may or may not be there later. To me your 
particular function looks fairly benign, still you are standing close to 
the edge:)

> 
> Thanks Adrian.
> 
> On Fri, Jul 16, 2021 at 6:30 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/16/21 3:26 PM, David Gauthier wrote:
>      > This stored procedure ...
> 
>      > dvdb=# alter table projects add constraint
>     validate_sibling_project_csv
>      > check (validate_proj_csv(sibling_project_csv) = 0);
>      > ERROR:  upper bound of FOR loop cannot be null
>      > CONTEXT:  PL/pgSQL function validate_proj_csv(character varying)
>     line 14
>      > at FOR with integer loop variable
>      >
>      > What's going on ?
> 
>     I'm going to say you have a NULL value in sibling_project_csv in the
>     table.
> 
>      > How to get this to work ?
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com