Test case for parameterized remote path in postgres_fdw

Started by Etsuro Fujitaover 2 years ago4 messageshackers
Jump to latest
#1Etsuro Fujita
fujita.etsuro@lab.ntt.co.jp

Hi,

While working on the join pushdown issue, I noticed this bit in commit
e4106b252:

--- parameterized remote path
+-- parameterized remote path for foreign table
 EXPLAIN (VERBOSE, COSTS false)
-  SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
+  SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
 SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
+

The first statement was modified to test the intended behavior, but
the second one was not. The second one as-is performs a foreign join:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: a.c1, a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2,
b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
Relations: (public.ft2 a) INNER JOIN (public.ft2 b)
Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
r1.c7, r1.c8, r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7,
r2.c8 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 =
r2."C 1")) AND ((r1."C 1" = 47))))
(4 rows)

So we should have modified the second one as well? Attached is a
small patch for that.

Best regards,
Etsuro Fujita

Attachments:

postgres-fdw-parameterized-path-test-case.patchapplication/octet-stream; name=postgres-fdw-parameterized-path-test-case.patchDownload+5-4
#2Richard Guo
guofenglinux@gmail.com
In reply to: Etsuro Fujita (#1)
Re: Test case for parameterized remote path in postgres_fdw

On Tue, Aug 15, 2023 at 7:50 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

So we should have modified the second one as well? Attached is a
small patch for that.

Agreed, nice catch! +1 to the patch.

Thanks
Richard

#3Etsuro Fujita
fujita.etsuro@lab.ntt.co.jp
In reply to: Richard Guo (#2)
Re: Test case for parameterized remote path in postgres_fdw

Hi Richard,

On Wed, Aug 16, 2023 at 9:41 AM Richard Guo <guofenglinux@gmail.com> wrote:

On Tue, Aug 15, 2023 at 7:50 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

So we should have modified the second one as well? Attached is a
small patch for that.

Agreed, nice catch! +1 to the patch.

Thanks for looking!

Best regards,
Etsuro Fujita

#4Etsuro Fujita
fujita.etsuro@lab.ntt.co.jp
In reply to: Etsuro Fujita (#3)
Re: Test case for parameterized remote path in postgres_fdw

On Wed, Aug 16, 2023 at 6:45 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

On Wed, Aug 16, 2023 at 9:41 AM Richard Guo <guofenglinux@gmail.com> wrote:

On Tue, Aug 15, 2023 at 7:50 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:

So we should have modified the second one as well? Attached is a
small patch for that.

Agreed, nice catch! +1 to the patch.

Thanks for looking!

Pushed.

Best regards,
Etsuro Fujita