CODEWITHSUNDEEP
postgresqlplpgsql
user2465039
user2465039

Reputation: 965

Parallel safety and exception handling in Postgres

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.

Upvotes: 2

Views: 33

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

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

Upvotes: 3

Related Questions