Thread: Can functions containing a CTE be PARALLEL SAFE?

Can functions containing a CTE be PARALLEL SAFE?

From
Erwin Brandstetter
Date:
[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.

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

  [1]: https://www.postgresql.org/docs/12/parallel-safety.html


 That means that these CTEs can only be scanned in the
leader process.

Re: Can functions containing a CTE be PARALLEL SAFE?

From
Tom Lane
Date:
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



Re: Can functions containing a CTE be PARALLEL SAFE?

From
Erwin Brandstetter
Date:
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