8

The manual for Postgres 12 says:

The following operations are always parallel restricted.

  • Scans of common table expressions (CTEs).
  • Scans of temporary tables.
  • ...

Further down on the same manual page:

[...] Similarly, functions must be marked PARALLEL RESTRICTED if they access temporary tables, client connection state, cursors, prepared statements, or miscellaneous backend-local state which the system cannot synchronize across workers. For example, setseed and random are parallel restricted for this last reason.

No mention of CTEs. Now I am unsure whether I can use PARALLEL SAFE for functions containing a CTE. It would make sense to me for those to be only PARALLEL RESTRICTED.

Context: I have to determine the best label for existing user-defined functions. The setting is new since Postgres 9.6, and can have a massive impact on performance as operations involving functions that are not PARALLEL SAFE will not be performed by parallel workers, PARALLEL RESTRICTED only by the leader. (And PARALLEL USAFE disables parallelism altogether.)

I posted a related question on pgsql-general.

Improve this question
5
  • I'd say it's safe (no pun intended) to mark them parallel safe as this is about possible side effects. Of course your CTE contains DML, that the function is not parallel safe
    – user1822
    Oct 16, 2019 at 17:53
  • @a_horse_with_no_name: my line of thinking was this: CTEs materialize results. And those special "temp" tables are local to one process, which might prevent parallelism (or mess with it).
    – Erwin Brandstetter
    Oct 16, 2019 at 18:02
  • Hmm, but wouldn't each "instance" of the CTE have its own private "temp table" where it's materialized?
    – user1822
    Oct 16, 2019 at 18:26
  • @a_horse_with_no_name: Totally conceivable. In the sense that CTE scans themselves cannot be parallelized (hence the first quote), but the prohibition is not propagated out to nesting levels of the query (hence no mention in the 2nd quote). I am just not sure. And I need to be sure.
    – Erwin Brandstetter
    Oct 16, 2019 at 19:06
  • 1
    you probably get better answers on the Postgres mailing list.
    – user1822
    Oct 16, 2019 at 19:09

1 Answer 1

Reset to default
4

If a function contains a query that uses a CTE, and that function is used by a parallel worker process, the query and the CTE will only be executed in the private process context of the parallel worker. There is no shared state involved in creating or scanning that CTE.

So it is safe to mark the function PARALLEL SAFE.

The quotation from the documentation says that a CTE defined in a query that should be parallelized can only be scanned by the leader process, not by the parallel workers, precisely for the reason that the CTE is not shared between processes. This has no impact on CTEs defined in queries running in functions called by parallel workers, because such nested queries won't be parallelized anyway:

Even when it is in general possible for parallel query plans to be generated, the planner will not generate them for a given query if any of the following are true:

[...]

  • The query is running inside of another query that is already parallel. For example, if a function called by a parallel query issues an SQL query itself, that query will never use a parallel plan.
Improve this answer
2

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.