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

From Nishant Sharma
Subject Re: postgres_fdw: wrong results with self join + enable_nestloop off
Date
Msg-id CADrsxdbo1f-cS6K=kqEAT7bkLPsGM0jhKUTJXzadDj0RQ7t3bw@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
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.fujita@gmail.com> wrote:
Hi Nishant,

On Fri, Apr 14, 2023 at 8:39 PM Nishant Sharma
<nishant.sharma@enterprisedb.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

pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: Initial Schema Sync for Logical Replication
Next
From: Michael Paquier
Date:
Subject: Re: allow_in_place_tablespaces vs. pg_basebackup