Re: postgres_fdw: wrong results with self join + enable_nestloop off - Mailing list pgsql-hackers

From Richard Guo
Subject Re: postgres_fdw: wrong results with self join + enable_nestloop off
Date
Msg-id CAMbWs4_-zGxqsOPwUYtTnK4kzB3YMAmxRy_Q4U3E9or51R+aYQ@mail.gmail.com
Whole thread Raw
In response to Re: postgres_fdw: wrong results with self join + enable_nestloop off  (Etsuro Fujita <etsuro.fujita@gmail.com>)
Responses Re: postgres_fdw: wrong results with self join + enable_nestloop off
List pgsql-hackers

On Wed, Jun 14, 2023 at 2:49 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Mon, Jun 5, 2023 at 10:19 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> To avoid this issue, I am wondering if we should modify
> add_paths_to_joinrel() in back branches so that it just disallows the
> FDW to consider pushing down joins when the restrictlist has
> pseudoconstant clauses.  Attached is a patch for that.

I think that custom scans have the same issue, so I modified the patch
further so that it also disallows custom-scan providers to consider
join pushdown in add_paths_to_joinrel() if necessary.  Attached is a
new version of the patch.

Good point.  The v2 patch looks good to me for back branches.

I'm wondering what the plan is for HEAD.  Should we also disallow
foreign/custom join pushdown in the case that there is any
pseudoconstant restriction clause, or instead still allow join pushdown
in that case?  If it is the latter, I think we can do something like my
patch upthread does.  But that patch needs to be revised to consider
custom scans, maybe by storing the restriction clauses also in
CustomPath?

Thanks
Richard

pgsql-hackers by date:

Previous
From: James Coleman
Date:
Subject: Re: Stampede of the JIT compilers
Next
From: Richard Guo
Date:
Subject: Re: Incremental sort for access method with ordered scan support (amcanorderbyop)