3

Looking at the function f_jsonb_select_keys from this answer:
How to select sub-object with given keys from JSONB?

https://www.postgresql.org/docs/current/functions-aggregate.html

Aggregate functions that support Partial Mode are eligible to participate in various optimizations, such as parallel aggregation.

In here, jsonb_object_agg partial node is NO
https://www.postgresql.org/docs/15/parallel-safety.html

In general, if a function is labeled as being safe when it is restricted or unsafe, or if it is labeled as being restricted when it is in fact unsafe, it may throw errors or produce wrong answers when used in a parallel query. C-language functions could in theory exhibit totally undefined behavior if mislabeled, since there is no way for the system to protect itself against arbitrary C code, but in most likely cases the result will be no worse than for any other function. If in doubt, it is probably best to label functions as UNSAFE.

CREATE OR REPLACE FUNCTION f_jsonb_select_keys(_js jsonb, _keys text[])
  RETURNS jsonb
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT jsonb_object_agg(t.key, t.value)
FROM   jsonb_each(_js) t
WHERE  t.key = ANY (_keys);
$func$;
  • jsonb_each is parallel safe, but from the above excerpt from manual, jsonb_object_agg is parallel unsafe?
  • Overall, is the function f_jsonb_select_keys parallel safe?
0

1 Answer 1

3

"Partial mode" is not required for a function to be PARALLEL SAFE. All involved functions are parallel safe, so my custom function can safely be marked PARALLEL SAFE, as well.

test=> SELECT proname, proparallel
test-> FROM   pg_proc
test-> WHERE  proname IN ('jsonb_object_agg', 'jsonb_each', 'json_object_agg', 'json_each');
     proname      | proparallel 
------------------+-------------
 json_each        | s
 json_object_agg  | s
 jsonb_each       | s
 jsonb_object_agg | s

The manual:

proparallel char

proparallel tells whether the function can be safely run in parallel mode. It is s for functions which are safe to run in parallel mode without restriction.

Also, think about it: if f_jsonb_select_keys() is used in a query and the planner uses multiple worker processes, the nested jsonb_object_agg() only ever aggregates the output of its "own" jsonb_each(), there is no overlap with other processes. Nothing that could mess with parallelism.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.