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
foo_wrapper |
---|
("{""k"": ""v""}",t) |
select foo_wrapper('x');--ok,caught
foo_wrapper |
---|
({},f) |
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.
No, you need to upgrade to PostgreSQL 17 for this feature.