From 77d4d88afbaa37773d15578d89881fa175ec38e8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 12 Dec 2018 16:08:30 -0500 Subject: Repair bogus EPQ plans generated for postgres_fdw foreign joins. postgres_fdw's postgresGetForeignPlan() assumes without checking that the outer_plan it's given for a join relation must have a NestLoop, MergeJoin, or HashJoin node at the top. That's been wrong at least since commit 4bbf6edfb (which could cause insertion of a Sort node on top) and it seems like a pretty unsafe thing to Just Assume even without that. Through blind good fortune, this doesn't seem to have any worse consequences today than strange EXPLAIN output, but it's clearly trouble waiting to happen. To fix, test the node type explicitly before touching Join-specific fields, and avoid jamming the new tlist into a node type that can't do projection. Export a new support function from createplan.c to avoid building low-level knowledge about the latter into FDWs. Back-patch to 9.6 where the faulty coding was added. Note that the associated regression test cases don't show any changes before v11, apparently because the tests back-patched with 4bbf6edfb don't actually exercise the problem case before then (there's no top-level Sort in those plans). Discussion: https://postgr.es/m/8946.1544644803@sss.pgh.pa.us --- contrib/postgres_fdw/expected/postgres_fdw.out | 136 +++++++++++++------------ 1 file changed, 72 insertions(+), 64 deletions(-) (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 dfa6201b3f2..666548ba696 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1701,25 +1701,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2) Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 - -> Sort - Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Sort Key: t1.c3 USING <, t1.c1 - -> Merge Join + -> Result + Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* + -> Sort Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Merge Cond: (t1.c1 = t2.c1) - -> Sort - Output: t1.c1, t1.c3, t1.* - Sort Key: t1.c1 - -> Foreign Scan on public.ft1 t1 + Sort Key: t1.c3, t1.c1 + -> Merge Join + Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* + Merge Cond: (t1.c1 = t2.c1) + -> Sort Output: t1.c1, t1.c3, t1.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE - -> Sort - Output: t2.c1, t2.* - Sort Key: t2.c1 - -> Foreign Scan on public.ft2 t2 + Sort Key: t1.c1 + -> Foreign Scan on public.ft1 t1 + Output: t1.c1, t1.c3, t1.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE + -> Sort Output: t2.c1, t2.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" -(26 rows) + Sort Key: t2.c1 + -> Foreign Scan on public.ft2 t2 + Output: t2.c1, t2.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" +(28 rows) SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1; c1 | c1 @@ -1748,25 +1750,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2) Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2 - -> Sort - Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Sort Key: t1.c3 USING <, t1.c1 - -> Merge Join + -> Result + Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* + -> Sort Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Merge Cond: (t1.c1 = t2.c1) - -> Sort - Output: t1.c1, t1.c3, t1.* - Sort Key: t1.c1 - -> Foreign Scan on public.ft1 t1 + Sort Key: t1.c3, t1.c1 + -> Merge Join + Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* + Merge Cond: (t1.c1 = t2.c1) + -> Sort Output: t1.c1, t1.c3, t1.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE - -> Sort - Output: t2.c1, t2.* - Sort Key: t2.c1 - -> Foreign Scan on public.ft2 t2 + Sort Key: t1.c1 + -> Foreign Scan on public.ft1 t1 + Output: t1.c1, t1.c3, t1.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE + -> Sort Output: t2.c1, t2.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE -(26 rows) + Sort Key: t2.c1 + -> Foreign Scan on public.ft2 t2 + Output: t2.c1, t2.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE +(28 rows) SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE; c1 | c1 @@ -1796,25 +1800,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2) Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 - -> Sort - Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Sort Key: t1.c3 USING <, t1.c1 - -> Merge Join + -> Result + Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* + -> Sort Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Merge Cond: (t1.c1 = t2.c1) - -> Sort - Output: t1.c1, t1.c3, t1.* - Sort Key: t1.c1 - -> Foreign Scan on public.ft1 t1 + Sort Key: t1.c3, t1.c1 + -> Merge Join + Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* + Merge Cond: (t1.c1 = t2.c1) + -> Sort Output: t1.c1, t1.c3, t1.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE - -> Sort - Output: t2.c1, t2.* - Sort Key: t2.c1 - -> Foreign Scan on public.ft2 t2 + Sort Key: t1.c1 + -> Foreign Scan on public.ft1 t1 + Output: t1.c1, t1.c3, t1.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE + -> Sort Output: t2.c1, t2.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" -(26 rows) + Sort Key: t2.c1 + -> Foreign Scan on public.ft2 t2 + Output: t2.c1, t2.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" +(28 rows) SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1; c1 | c1 @@ -1843,25 +1849,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2) Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2 - -> Sort - Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Sort Key: t1.c3 USING <, t1.c1 - -> Merge Join + -> Result + Output: t1.c1, t2.c1, t1.c3, t1.*, t2.* + -> Sort Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* - Merge Cond: (t1.c1 = t2.c1) - -> Sort - Output: t1.c1, t1.c3, t1.* - Sort Key: t1.c1 - -> Foreign Scan on public.ft1 t1 + Sort Key: t1.c3, t1.c1 + -> Merge Join + Output: t1.c1, t1.c3, t1.*, t2.c1, t2.* + Merge Cond: (t1.c1 = t2.c1) + -> Sort Output: t1.c1, t1.c3, t1.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE - -> Sort - Output: t2.c1, t2.* - Sort Key: t2.c1 - -> Foreign Scan on public.ft2 t2 + Sort Key: t1.c1 + -> Foreign Scan on public.ft1 t1 + Output: t1.c1, t1.c3, t1.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE + -> Sort Output: t2.c1, t2.* - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE -(26 rows) + Sort Key: t2.c1 + -> Foreign Scan on public.ft2 t2 + Output: t2.c1, t2.* + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE +(28 rows) SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE; c1 | c1 -- cgit v1.2.3