From d1ef5631e620f9a5b6480a32bb70124c857af4f1 Mon Sep 17 00:00:00 2001 From: Etsuro Fujita Date: Fri, 28 Jul 2023 15:45:02 +0900 Subject: Disallow replacing joins with scans in problematic cases. Commit e7cb7ee14, which introduced the infrastructure for FDWs and custom scan providers to replace joins with scans, failed to add support handling of pseudoconstant quals assigned to replaced joins in createplan.c, leading to an incorrect plan without a gating Result node when postgres_fdw replaced a join with such a qual. To fix, we could add the support by 1) modifying the ForeignPath and CustomPath structs to store the list of RestrictInfo nodes to apply to the join, as in JoinPaths, if they represent foreign and custom scans replacing a join with a scan, and by 2) modifying create_scan_plan() in createplan.c to use that list in that case, instead of the baserestrictinfo list, to get pseudoconstant quals assigned to the join; but #1 would cause an ABI break. So fix by modifying the infrastructure to just disallow replacing joins with such quals. Back-patch to all supported branches. Reported by Nishant Sharma. Patch by me, reviewed by Nishant Sharma and Richard Guo. Discussion: https://postgr.es/m/CADrsxdbcN1vejBaf8a%2BQhrZY5PXL-04mCd4GDu6qm6FigDZd6Q%40mail.gmail.com --- contrib/postgres_fdw/expected/postgres_fdw.out | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'contrib/postgres_fdw/expected/postgres_fdw.out') diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 17c95da25e4..08f1b356f29 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2293,6 +2293,32 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM 1 (10 rows) +-- join with pseudoconstant quals, not pushed down. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1 AND CURRENT_USER = SESSION_USER) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; + QUERY PLAN +------------------------------------------------------------------------------- + Limit + Output: t1.c1, t2.c1, t1.c3 + -> Sort + Output: t1.c1, t2.c1, t1.c3 + Sort Key: t1.c3, t1.c1 + -> Result + Output: t1.c1, t2.c1, t1.c3 + One-Time Filter: (CURRENT_USER = SESSION_USER) + -> Hash Join + Output: t1.c1, t1.c3, t2.c1 + Hash Cond: (t2.c1 = t1.c1) + -> Foreign Scan on public.ft2 t2 + Output: t2.c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" + -> Hash + Output: t1.c1, t1.c3 + -> Foreign Scan on public.ft1 t1 + Output: t1.c1, t1.c3 + Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1" +(19 rows) + -- non-Var items in targetlist of the nullable rel of a join preventing -- push-down in some cases -- unable to push {ft1, ft2} -- cgit v1.2.3