Re: PoC: adding CustomJoin, separate from CustomScan

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'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. :-)

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com