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

> On Jul 5, 2017, at 5:30 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Jul 5, 2017 at 9:44 AM, Mark Dilger <hornschnorter(at)gmail(dot)com> wrote:
>>
>>> On Jul 3, 2017, at 10:25 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>>
>>> On Mon, Jul 3, 2017 at 8:57 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>> On 30 June 2017 at 05:14, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>>>
>>>>> 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."
>>>>
>>>> Can you explain "unable to verify that the code in the loop is safe to
>>>> execute while parallel query is active". Surely we aren't pushing code
>>>> in the loop into the actual query, so the safety of command in the FOR
>>>> loop has nothing to do with the parallel safety of the query.
>>>>
>>>> Please give an example of something that would be unsafe? Is that
>>>> documented anywhere, README etc?
>>>>
>>>> FOR x IN query LOOP .. END LOOP
>>>> seems like a case that would be just fine, since we're going to loop
>>>> thru every row or break early.
>>>>
>>>
>>> It is not fine because we don't support partial execution support. In
>>> above case, if the loop breaks, we can't break parallel query
>>> execution. Now, I don't think it will impossible to support the same,
>>> but as of now, parallel subsystem doesn't have such a support.
>>
>> I can understand this, but wonder if I could use something like
>>
>> FOR I TOTALLY PROMISE TO USE ALL ROWS rec IN EXECUTE sql LOOP
>> ...
>> END LOOP;
>>
>> if I hacked the grammar up a bit. Would the problem go away, or would
>> I still have problems when exceptions beyond my control get thrown inside
>> the loop?
>>
>
> I don't think it is just a matter of hacking grammar, internally we
> are using cursor fetch to fetch the rows and there we are passing some
> fixed number of rows to fetch which again is a killer to invoke the
> parallel query.

Is the risk that a RETURN will be executed within the loop block the only
risk that is causing parallel query to be disabled in these plpgsql loops?
If so, it seems that a plpgsql language syntax which created a loop that
disabled RETURN from within the loop body would solve the problem. I
do not propose any such language extension. It was just a thought
experiment.

Is there a conditional somewhere in the source that says, in effect, "if this
is a plpgsql loop, then disable parallel query"? If so, and if I removed that
check such that parallel query would run in plpgsql loops, would I only
get breakage when I returned out of a loop? Or would there be other
situations where that would break?

Thanks,

mark