Обсуждение: Can functions containing a CTE be PARALLEL SAFE?
[The manual for Postgres 12 says][1]:
> 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]
> [...] 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.
[1]: https://www.postgresql.org/docs/12/parallel-safety.html
That means that these CTEs can only be scanned in the
leader process.
> 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]
> [...] 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]:
> 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].
Regards
Erwin Brandstetter
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:
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