I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the query will totally disable parallelism.

But I am having a hard time understand WHEN to use it. Let me give an example. Say I have a function which only creates tables and does not update any data or insert any rows. What should the parallel safety of this be marked as? I would think to mark it as parallel safe, but then I cannot understand why the manual would list the following as parallel restricted:

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

Since both points do not necessarily involve any data update/insert (race condition)?

The case for CTE is particularly strange. I am understanding that if I add a parallel safe function to a CTE call, it will not be executed as parallel safe any longer? And, outside of function parallel safety, I am understanding that all CTEs are executed as parallel restricted? Is there a difference between materialized and not materialized CTEs here?


The reason is that both CTEs and temporary tables are private to the backend process that created them, and parallel worker processes are different processes that do not have access to the private resources of the leader process.

To make CTE scans parallel safe, CTEs would have to be materialized in shared memory.

I would be careful marking a function that creates a table as parallel safe, since that creates locks in the database. Can you guarantee that there can never be any conflicts so that your worker processes lock each other?

  • so if I set my database to execute my CTE in parallel, it will never happen? what about if I use NOT MATERIALIZED keyword? I do not understand your comment at the end about creating locks and about what I have to guarantee so that processes don't lock each other. Could you please clarify? – Zeruno Mar 20 '20 at 7:10
  • If two of your parallel workers try to create the same table, one will lock the other. – Laurenz Albe Mar 20 '20 at 7:38
  • About materialized/not materialized CTEs: I guess it was simpler to exclude CTEs from parallelization. First, the rule was introduced before CTEs could be inlined, so maybe this was simply left for the future, but maybe it was considered unlikely that doing the same work several times over would offer a benefit. You'd have to look at thr discussion in the mailing list archives. – Laurenz Albe Mar 20 '20 at 7:46
  • When we say NOT MATERIALIZED inlines the CTE to the outer query, does that mean rewriting the CTE as a subquery? In this case, I suppose parallelism of NOT MATERIALIZED is indeed possible. – Zeruno Mar 20 '20 at 8:14
  • Rewrite the query to use a subquery rather than a CTE. – Laurenz Albe Mar 20 '20 at 9:54

Say I have a function which only creates tables and does not update any data or insert any rows

Creating a table must insert rows into the system catalogs. This function is PARALLEL UNSAFE.

  • Why? If I have two functions creating tables (with different names) where is the conflict/race condition? Why would I mark it as parallel unsafe? – Zeruno Mar 20 '20 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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