I am using Postgres 15.

Is there any way to handle exception handling and parallel safety in postgres?

I have a function foo() that is immutable and parallel safe but there are cases where it might create exceptions.

I want to create a wrapper around that function that handles exceptions (by returning a FALSE value). Something in the following lines:

create function foo(text)
returns jsonb 
immutable parallel safe
return case $1 
       when 'a' then '{"k":"v"}'
       else $1::jsonb
       end;

create function foo_wrapper(val text)
 returns TABLE(results jsonb, _result boolean)
 immutable parallel safe as 
$$
BEGIN RETURN QUERY
  SELECT*,TRUE FROM foo(val);
EXCEPTION WHEN OTHERS THEN RETURN QUERY
  SELECT '{}'::jsonb,FALSE;
END
$$language plpgsql;

select foo_wrapper('a');--ok

I am using Postgres 15.

Is there any way to handle exception handling and parallel safety in postgres?

I have a function foo() that is immutable and parallel safe but there are cases where it might create exceptions.

I want to create a wrapper around that function that handles exceptions (by returning a FALSE value). Something in the following lines:

create function foo(text)
returns jsonb 
immutable parallel safe
return case $1 
       when 'a' then '{"k":"v"}'
       else $1::jsonb
       end;

create function foo_wrapper(val text)
 returns TABLE(results jsonb, _result boolean)
 immutable parallel safe as 
$$
BEGIN RETURN QUERY
  SELECT*,TRUE FROM foo(val);
EXCEPTION WHEN OTHERS THEN RETURN QUERY
  SELECT '{}'::jsonb,FALSE;
END
$$language plpgsql;

select foo_wrapper('a');--ok

select foo_wrapper('x');--ok,caught

However, it is not possible to mark the wrapper as parallel safe as I am getting errors of this kind:
_{demo at db<>fiddle}

create table t as 
select generate_series(1,3e5)n;

explain analyze verbose
select foo_wrapper('x')
from t;

ERROR:  cannot start subtransactions during a parallel operation
CONTEXT:  PL/pgSQL function foo_wrapper(text) line 2 during statement block entry

and this is due to the fact that exceptions in postgres seem to start subtransactions which are not allowed in parallel queries.

• postgresql
• plpgsql

1 Answer 1

No, you need to upgrade to PostgreSQL 17 for this feature.