Re: Making Vars outer-join aware - Mailing list pgsql-hackers

From Richard Guo
Subject Re: Making Vars outer-join aware
Date
Msg-id CAMbWs49KZO2VvMqo+1MQfaX4cun7vhF=5RgVdpA0gjJcj77HFQ@mail.gmail.com
Whole thread Raw
In response to Making Vars outer-join aware  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Making Vars outer-join aware
List pgsql-hackers

On Sat, Jul 2, 2022 at 12:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Anyway, even though this is far from done, I'm pretty pleased with
the results so far, so I thought I'd put it out for review by
anyone who cares to take a look.  I'll add it to the September CF
in hopes that it might be more or less finished by then, and so
that the cfbot will check it out.

Thanks for the work! I have a question about qual clause placement.

For the query in the example

    SELECT * FROM t1 LEFT JOIN t2 ON (t1.x = t2.y) WHERE foo(t2.z)

(foo() is not strict.) We want to avoid pushing foo(t2.z) down to the t2
scan level. Previously we do that with check_outerjoin_delay() by
scanning all the outer joins below and check if the qual references any
nullable rels of the OJ, and if so include the OJ's rels into the qual.
So as a result we'd get that foo(t2.z) is referencing t1 and t2, and
we'd put the qual into the join lists of t1 and t2.

Now there is the 'varnullingrels' marker in the t2.z, which is the LEFT
JOIN below (with RTI 3). So we consider the qual is referencing RTE 2
(which is t2) and RTE 3 (which is the OJ). Do we still need to include
RTE 1, i.e. t1 into the qual's required relids? How should we do that?

Thanks
Richard

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: [BUG] Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns
Next
From: Pavel Stehule
Date:
Subject: Re: Schema variables - new implementation for Postgres 15