Test case for parameterized remote path in postgres_fdw

From: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Test case for parameterized remote path in postgres_fdw
Date: 2023-08-15 11:49:59
Message-ID: CAPmGK15nR0NXLSCKQAcqbZbTzrzd5MozowWnTnGfPkayndF43Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
postgres-fdw-parameterized-path-test-case.patch application/octet-stream 2.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2023-08-15 11:53:05 Re: [PoC] Improve dead tuple storage for lazy vacuum
Previous Message Andy Fan 2023-08-15 11:26:45 Re: Avoid a potential unstable test case: xmlmap.sql