Re: PoC: adding CustomJoin, separate from CustomScan

On 7/25/25 17:34, Paul A Jungwirth wrote:
> On Thu, Jul 24, 2025 at 12:09 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I think that the motivating use case for the existing support was
>> wanting to offload work to something that looks altogether unlike the
>> normal PostgreSQL executor, rather than (as you want to do) implement
>> a new type of join within the PostgreSQL executor. The latter might
>> merit different infrastructure, although I cringe a little bit at the
>> idea of having two ways of implementing custom joins when the total
>> number of projects using this infrastructure is probably less than
>> five.
>
> I'm interested in using this. It sounds like it would let me implement
> temporal outer/semi/anti joins very nicely. I didn't realize I could
> do that with CustomScan as well---I'll investigate that---but
> CustomJoin does sound like a better fit.
>

I managed to make it work with the CustomScan by manually building the
tuple based on information extracted from target lists while creating
the plan (so prior to setrefs and all that).

I didn't have time to experiment with that more, so I don't know what
limitations that implies. E.g. I'm not sure if this makes evaluating
expressions (like join clauses) harder, or something like that.

The CustomJoin sure was easier / more convenient to use, though.

> I'm not sure how to let users *ask* for a temporal join though. . . .
> How do other CustomScans "get there"? Perhaps I could use a noop
> function you include in the ON section that is declarative
> pseudo-syntax, e.g. naming the tsrange columns, and a planner hook
> that looks for that. I'm also not completely sure that temporal joins
> have the same algebraic properties (associative, commutative, etc) as
> non-temporal, or when mixed with non-temporal. Perhaps I can find a
> research paper about that. Can the planner freely re-order
> CustomScans? Anyway, I just wanted to say that there might be six
> projects. :-)
>

I don't think CustomScans help with parser/grammar at all. It's just a
planner/executor node, it has no way to interact with parser. Maybe some
sort of "custom operator" would work, not sure. Not sure what exactly
you need in the planner.

For planning, CustomScan joins are simply part of the regular join
search. We generate all the various paths for a joinrel, and then give
the set_join_pathlist_hook hook a chance to add some more. AFAIK it
doesn't affect the join order search, or anything like that. At least
not directly.

regards

--
Tomas Vondra