Test case for parameterized remote path in postgres_fdw
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
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 77df7eb8e4..144c114d0f 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -744,10 +744,10 @@ EXPLAIN (VERBOSE, COSTS OFF)
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1::integer))
(8 rows)
-SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
- c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+-------+------------------------------+--------------------------+----+------------+-----
- 47 | 7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo | 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
+SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+ C 1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 47 | 7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo | 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
(1 row)
-- check both safe and unsafe join conditions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index cfb1b57e33..a303bfb322 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -344,7 +344,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be
-- parameterized remote path for foreign table
EXPLAIN (VERBOSE, COSTS OFF)
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;
+SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
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
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
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