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

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.

> And if exception handling is a problem in the loop, are exceptions
> somehow not a problem in other parallel queries?
>

I don't see exceptions as a blocking factor to choose any parallel
query inside PL. However, if you have something in mind, feel free to
share with some example?

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