Re: Request more documentation for incompatibility of parallelism and plpgsql exec_run_select

On Fri, Jun 30, 2017 at 9:25 AM, Mark Dilger <hornschnorter(at)gmail(dot)com> wrote:
> Hackers,
>
> In src/pl/plpgsql/src/pl_exec.c: exec_run_select intentionally does not
> allow a parallel plan if a portal will be returned. This has the practical
> consequence that a common coding practice (at least for me) of doing
> something like:
>
> create function myfunc(arg1 text, arg2 text) returns setof myfunctype as $$
> declare
> sql text;
> result myfunctype;
> begin
> -- unsafe interpolation, but this is just a code example
> sql := 'select foo from bar where a = ' || arg1 || ' and b = ' || arg2;
> for result in execute sql loop
> return next result;
> end loop;
> return;
> end;
> $$ language plpgsql volatile;
>
> can't run the generated 'sql' in parallel. I think this is understandable, but
> the documentation of this limitation in the sgml docs is thin. Perhaps
> someone who understands this limitation better than I do can document it?
>

This is explained in section 15.2 [1], refer below para:
"The query might be suspended during execution. In any situation in
which the system thinks that partial or incremental execution might
occur, no parallel plan is generated. For example, a cursor created
using DECLARE CURSOR will never use a parallel plan. Similarly, a
PL/pgSQL loop of the form FOR x IN query LOOP .. END LOOP will never
use a parallel plan, because the parallel query system is unable to
verify that the code in the loop is safe to execute while parallel
query is active."

Check if that clarifies your doubts, otherwise, we might need to write
something more so that it can be easier for users to understand.

[1] - https://www.postgresql.org/docs/devel/static/when-can-parallel-query-be-used.html

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com