Re: Consider parallel for lateral subqueries with limit

On Tue, Jan 4, 2022 at 5:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Greg Nancarrow <gregn4422(at)gmail(dot)com> writes:
> > The patch LGTM.
> > I have set the status to "Ready for Committer".
>
> I don't really see why this patch is even a little bit safe.
> The argument for it seems to be that a lateral subquery will
> necessarily be executed in such a way that each complete iteration
> of the subquery, plus joining to its outer rel, happens within a
> single worker ... but where is the guarantee of that? Once
> you've marked the rel as parallel-safe, the planner is free to
> consider all sorts of parallel join structures. I'm afraid this
> would be easily broken as soon as you look at cases with three or
> more rels. Or maybe even just two. The reason for the existing
> restriction boils down to this structure being unsafe:
>
> Gather
> NestLoop
> Scan ...
> Limit
> Scan ...
>
> and I don't see how the existence of a lateral reference
> makes it any safer.

Thanks for taking a look. I'm not following how the structure you
posited is inherently unsafe when it's a lateral reference. That
limit/scan (if lateral) has to be being executed per tuple in the
outer scan, right? And if it's a unique execution per tuple, then
consistency across tuples (that are in different workers) can't be a
concern.

Is there a scenario I'm missing where lateral can currently be
executed in that way in that structure (or a different one)?

Thanks,
James Coleman