Horton

Thread

Commits

  1. postgres_fdw: Fix test for parameterized foreign scan.

  2. postgres_fdw: Push down joins to remote servers.

  1. Test case for parameterized remote path in postgres_fdw

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2023-08-15T11:49:59Z

    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

  2. Re: Test case for parameterized remote path in postgres_fdw

    Richard Guo <guofenglinux@gmail.com> — 2023-08-16T00:41:09Z

    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

  3. Re: Test case for parameterized remote path in postgres_fdw

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2023-08-16T09:45:06Z

    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

  4. Re: Test case for parameterized remote path in postgres_fdw

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2023-08-30T08:50:57Z

    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