Re: BUG #15324: Non-deterministic behaviour from parallelised sub-query

On Thu, Aug 16, 2018 at 9:25 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Wed, Aug 15, 2018 at 4:40 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> Greetings,
>>
>
> Yeah, let me summarize the problems which require patches:
> (a) Consider the presence of a LIMIT/OFFSET in a sub-select as making
> it parallel-unsafe.
>

As mentioned up-thread, I have considered adding a check in
max_parallel_hazard_walker, but it turns out that it will make the
whole query parallel-unsafe even if one of the sub-selects has
Limit/Offset. I think the better idea is to detect that during
set_rel_consider_parallel. Attached patch
prohibit_parallel_limit_subselect_v2 implements the fix for same.

I have also tried to consider waving off the case when Order By is
present in the sub-select, but for that, I think we need to ensure
that Order By is on the same column as the target list. It is not
clear to me whether adding additional code checks to detect that is
valuable because it can consume planner time for some cases. Also, I
think it is not advisable to have such checks for the back-branches as
pointed by Tom as well. So, I didn't do anything about it.

> (b) Consider the presence of any window function calculation as
> parallel-restricted operation.
>

For this, we need to mark all the window functions like row_number,
rank, dense_rank, etc as parallel-restricted. Additionally, we also
need to detect the presence of aggregate functions that act as window
functions (when an OVER clause follows the call). Attached patch
treat_window_func_calc_parallel_restricted_v1 implements the fix.

> Initially, I will prepare two separate patches for them and then we
> will see if we want to combine them into one before committing.
>

One can apply patches in the order
prohibit_parallel_limit_subselect_v2 and
treat_window_func_calc_parallel_restricted_v1.

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