Re: Consider parallel for lateral subqueries with limit

On Tue, Jan 4, 2022 at 5:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Greg Nancarrow <gregn4422@gmail.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