Re: postgres_fdw: wrong results with self join + enable_nestloop off

Thanks Etsuro for your response!

One small typo correction in my answer to "What is the technical issue?"
"it is *not* considered a pseudo constant" --> "it is considered a pseudo
constant"

Regards,
Nishant.

On Fri, Apr 14, 2023 at 6:21 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
wrote:

> Hi Nishant,
>
> On Fri, Apr 14, 2023 at 8:39 PM Nishant Sharma
> <nishant(dot)sharma(at)enterprisedb(dot)com> wrote:
> > I debugged this issue and was able to find a fix for the same. Kindly
> please refer to the attached fix. With the fix I am able to resolve the
> issue.
>
> Thanks for the report and patch!
>
> > What is the technical issue?
> > The problem here is the use of extract_actual_clauses. Because of which
> the plan creation misses adding the second condition of AND i.e "now() <
> '23-Feb-2020'::timestamp" in the plan. Because it is not considered a
> pseudo constant and extract_actual_clause is passed with false as the
> second parameter and it gets skipped from the list. As a result that
> condition is never taken into consideration as either one-time filter
> (before or after) or part of SQL remote execution
> >
> > Why do I think the fix is correct?
> > The fix is simple, where we have created a new function similar to
> extract_actual_clause which just extracts all the conditions from the list
> with no checks and returns the list to the caller. As a result all
> conditions would be taken into consideration in the query plan.
>
> I think that the root cause for this issue would be in the
> create_scan_plan handling of pseudoconstant quals when creating a
> foreign-join (or custom-join) plan. Anyway, I will look at your patch
> closely, first.
>
> Best regards,
> Etsuro Fujita
>