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

> On Jun 29, 2017, at 9:14 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> 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.

That's what I was looking for. I think I had trouble finding it since I was
using plpgsql (notice no slash) and "parallel worker" and so forth to try
to find it. "PL/pgSQL" and "parallel query" are not terms I use, and did
not notice them buried a few sentences down in this paragraph.

Thanks for the citation.

mark