Can functions containing a CTE be PARALLEL SAFE?
[The manual for Postgres 12 says][1]https://www.postgresql.org/docs/12/parallel-safety.html:
The following operations are always parallel restricted.
- Scans of common table expressions (CTEs).
- Scans of temporary tables.
- ...
Further down on the same [manual page:][2]https://www.postgresql.org/docs/12/parallel-safety.html#PARALLEL-LABELING
[...] 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.
I searched the list archives and found a statement from Thomas Munro
[here][3]/messages/by-id/CAEepm=03s4Yih+c0pCTfKi0O8zgq-P4VfCVEk5VVXcR6aXHm4A@mail.gmail.com:
That means that these CTEs can only be scanned in the leader process.
Now I am unsure whether I can use `PARALLEL SAFE` for functions containing
a CTE (while fulfilling all other criteria)?
Would the new inlining of CTEs in Postgres 12 have any role in this?
I posted a [similar question on dba.SE][4]https://dba.stackexchange.com/q/251274/3684.
Regards
Erwin Brandstetter
[1]: https://www.postgresql.org/docs/12/parallel-safety.html
[2]: https://www.postgresql.org/docs/12/parallel-safety.html#PARALLEL-LABELING
https://www.postgresql.org/docs/12/parallel-safety.html#PARALLEL-LABELING
[3]: /messages/by-id/CAEepm=03s4Yih+c0pCTfKi0O8zgq-P4VfCVEk5VVXcR6aXHm4A@mail.gmail.com
/messages/by-id/CAEepm=03s4Yih+c0pCTfKi0O8zgq-P4VfCVEk5VVXcR6aXHm4A@mail.gmail.com
[4]: https://dba.stackexchange.com/q/251274/3684
That means that these CTEs can only be scanned in the
leader process.
Erwin Brandstetter <brsaweda@gmail.com> writes:
The following operations are always parallel restricted.
- Scans of common table expressions (CTEs).
Now I am unsure whether I can use `PARALLEL SAFE` for functions containing
a CTE (while fulfilling all other criteria)?
AFAIR, the reason for treating CTEs as parallel restricted is simply to
guarantee single evaluation of the CTE. Within a function, that would
only matter per-function-execution, so I can't see why a function
containing such a query couldn't be pushed down to workers for execution.
regards, tom lane
Makes sense, thanks for the confirmation.
Maybe clarify in the manual?
Regards
Erwin
On Thu, Oct 17, 2019 at 11:20 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Erwin Brandstetter <brsaweda@gmail.com> writes:
The following operations are always parallel restricted.
- Scans of common table expressions (CTEs).Now I am unsure whether I can use `PARALLEL SAFE` for functions
containing
a CTE (while fulfilling all other criteria)?
AFAIR, the reason for treating CTEs as parallel restricted is simply to
guarantee single evaluation of the CTE. Within a function, that would
only matter per-function-execution, so I can't see why a function
containing such a query couldn't be pushed down to workers for execution.regards, tom lane