Re: PoC: adding CustomJoin, separate from CustomScan

On 7/24/25 15:57, Robert Haas wrote:
> On Thu, Jul 24, 2025 at 9:04 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>> With this patch, my custom join can simply do
>>
>> econtext->ecxt_outertuple = outer;
>> econtext->ecxt_innertuple = inner;
>>
>> return ExecProject(node->js.ps.ps_ProjInfo);
>>
>> and it works.
>
> This doesn't seem like the right approach to me because, AFAICS, it's
> only going to work if you're replacing a single join. But I think it
> would be very desirable for a CustomScan to be able to replace
> multiple joins with a single scan node and handle all the details
> internally. I'm fairly certain that was part of the original design
> intent here.
>

I'm not sure I understand this multiway-join argument. Yes, CustomJoin
can only handle regular joins of two relations. But I haven't suggested
to forbid using CustomScan to do arbitrary multiway-joins, though. You
can still do that, but it means you'll probably have to do much more
manual work. It doesn't seem great to force "regular" joins to do all
that manual work, though.

I was thinking about this a bit more, and I think the CustomScan join
essentially has to construct it's own info how to build the tuple, most
likely in PlanCustomPath, because once setrefs.c does it's thing it's
way too late for that I think. And the only way to store that is
custom_private, so no fancy custom structs. I'll give it a try.

Seems much more convenient to allow doing what regulars joins do, and
only force the extension to do the manual stuff if it wants to do
something like multiway-joins ...

> And I think that must already work for foreign joins, because I
> believe postgres_fdw is perfectly capable of pushing a multi-way join
> down to a remote node -- and if it's doing that, it's somehow solving
> the problem you encountered here. So maybe we can study how that works
> and figure out how to apply it to this case.
>

AFAICS postgres_fdw completely sidesteps this issue, because it deparses
the join into a SQL statement in postgresGetForeignPlan(), and it's the
remote server responsible for building the tuple. The postgres_fdw has
nothing to do to make that happen. It just gets the tuple, puts it into
the ecxt_scantuple slot, and that's it. It doesn't need to worry about
inner/outer tuple, etc.

That's not what the CustomScan can rely on, I think. At least my use
case can't do that - I have to build the tuple, somehow.

regards

--
Tomas Vondra