Alexander Pyhalov писал(а) 2021-10-04 10:42:
Ashutosh Bapat писал 2021-06-15 16:15:
Hi Alexander,
Hi.
The current version of the patch is based on asymetric partition-wise
join.
Currently it is applied after
v19-0001-Asymmetric-partitionwise-join.patch from
on
https://www.postgresql.org/message-id/792d60f4-37bc-e6ad-68ca-c2af5cbb2...@postgrespro.ru
.
So far I don't know how to visualize actual function expression used
in
function RTE, as in postgresExplainForeignScan() es->rtable comes
from
queryDesc->plannedstmt->rtable, and rte->functions is already 0.
The actual function expression will be part of the Remote SQL of
ForeignScan node so no need to visualize it separately.
We still need to create tuple description for functions in
get_tupdesc_for_join_scan_tuples(),
so I had to remove setting newrte->functions to NIL in
add_rte_to_flat_rtable().
With rte->functions in place, there's no issues for explain.
The patch will have problems when there are multiple foreign tables
all on different servers or use different FDWs. In such a case the
function scan's RelOptInfo will get the fpinfo based on the first
foreign table the function scan is paired with during join planning.
But that may not be the best foreign table to join. We should be able
to plan all the possible joins. Current infra to add one fpinfo per
RelOptInfo won't help there. We need something better.
I suppose attached version of the patch is more mature.
The patch targets only postgres FDW, how do you see this working with
other FDWs?
Not now. We introduce necessary APIs for other FDWs, but implementing
TryShippableJoinPaths()
doesn't seem straightforward.
If we come up with the right approach we could use it for 1. pushing
down queries with IN () clause 2. joining a small local table with a
large foreign table by sending the local table rows down to the
foreign server.
Hi,
This is a long-overdue follow-up to the original patch.
Note that this patch contains only the changes required for
function scan pushdown, examples and code related to asymmetric
join are dropped.
In this version, we have fixed some of the issues:
1) Functions returning records are now deparsed correctly
2) I have benchmarked this version alongside the current master
using HammerDB and it shows a consistent ~6000 number of orders
per minute (NOPM) compared to ~100 NOPM on local setup with
1 virtual user and 10 warehouses
The benchmarking was concluded in the following way:
1) schema was created and filled up by buildschema on the first instance
2) foreign schema was imported on the second instance
3) functions created by buildschema (all in namespace 'public') were
exported from the first instance and created on the second one
4) HammerDB was connected to the second instance, and the benchmark
was run there
Note that the HammerDB service functions that were imported to the
second
instance are not the ones being pushed down, only PostgreSQL built-ins
are.
The issue with setting newrte->functions to NIL still persists.
I've attempted to work around it by adding the rte->functions
list to fdw_private field. I stored a copy of rte->functions for each
RTE in the order they are listed in simple_rte_array and accessed it
where the rte->functions were used in the original patch. While this
being pretty straightforward, I found out the hard way that the RTEs
are flattened and the original sequential order known at
postgresGetForeignPlan() and postgresPlanDirectModify() is altered
randomly. It prevents me from looking up the right functions list for
the RTE by its rti in postgresExplainForeignScan() and its var->varno in
get_tupdesc_for_join_scan_tuples(). I am aware that the rte->functions
will now be copied even on instances that don't utilize a FDW, but I
don't see a way to solve it. Any suggestions are welcome.
Best regards,
Gleb Kashkin,
Postgres Professional
From a59b04e33147a075fdd4c6cef155440689850b83 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Mon, 17 May 2021 19:19:31 +0300
Subject: [PATCH] Push join with function scan to remote server
The patch allows pushing joins with function RTEs to PostgreSQL
data sources in general and postgres_fdw specifically.
Co-authored-by: Gleb Kashkin <g.kash...@postgrespro.ru>
---
contrib/postgres_fdw/deparse.c | 187 ++++++--
.../postgres_fdw/expected/postgres_fdw.out | 368 +++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 423 +++++++++++++++---
contrib/postgres_fdw/postgres_fdw.h | 6 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 148 ++++++
src/backend/optimizer/path/joinpath.c | 11 +
src/backend/optimizer/plan/setrefs.c | 1 -
src/include/foreign/fdwapi.h | 1 +
src/test/regress/expected/create_view.out | 6 +-
9 files changed, 1046 insertions(+), 105 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 4680d51733..17228dec47 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -153,6 +153,7 @@ static void deparseConst(Const *node, deparse_expr_cxt *context, int showtype);
static void deparseParam(Param *node, deparse_expr_cxt *context);
static void deparseSubscriptingRef(SubscriptingRef *node, deparse_expr_cxt *context);
static void deparseFuncExpr(FuncExpr *node, deparse_expr_cxt *context);
+static void deparseFuncColnames(StringInfo buf, int varno, RangeTblEntry *rte, bool qualify_col);
static void deparseOpExpr(OpExpr *node, deparse_expr_cxt *context);
static bool isPlainForeignVar(Expr *node, deparse_expr_cxt *context);
static void deparseOperatorName(StringInfo buf, Form_pg_operator opform);
@@ -1977,13 +1978,54 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
{
RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
- /*
- * Core code already has some lock on each rel being planned, so we
- * can use NoLock here.
- */
- Relation rel = table_open(rte->relid, NoLock);
+ Assert(rte->rtekind == RTE_RELATION || rte->rtekind == RTE_FUNCTION);
+ if (rte->rtekind == RTE_RELATION)
+ {
+ /*
+ * Core code already has some lock on each rel being planned, so
+ * we can use NoLock here.
+ */
+ Relation rel = table_open(rte->relid, NoLock);
- deparseRelation(buf, rel);
+ deparseRelation(buf, rel);
+
+ table_close(rel, NoLock);
+ }
+ else if (rte->rtekind == RTE_FUNCTION)
+ {
+ RangeTblFunction *rtfunc;
+ deparse_expr_cxt context;
+ ListCell *lc;
+ bool first = true;
+ int n;
+
+ n = list_length(rte->functions);
+ Assert(n >= 1);
+
+ if (n > 1)
+ appendStringInfoString(buf, "ROWS FROM (");
+
+ foreach(lc, rte->functions)
+ {
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ else
+ first = false;
+
+ rtfunc = (RangeTblFunction *) lfirst(lc);
+
+ context.root = root;
+ context.foreignrel = foreignrel;
+ context.scanrel = foreignrel;
+ context.buf = buf;
+ context.params_list = params_list;
+
+ deparseExpr((Expr *) rtfunc->funcexpr, &context);
+ }
+
+ if (n > 1)
+ appendStringInfoString(buf, ")");
+ }
/*
* Add a unique alias to avoid any conflict in relation names due to
@@ -1991,9 +2033,43 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
* join.
*/
if (use_alias)
+ {
appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, foreignrel->relid);
+ if (rte->rtekind == RTE_FUNCTION)
+ {
+ appendStringInfo(buf, " (");
+ deparseFuncColnames(buf, 0, rte, false);
+ appendStringInfo(buf, ") ");
+ }
+ }
+ }
+}
- table_close(rel, NoLock);
+/*
+ * Deparse function columns alias list
+ */
+static void
+deparseFuncColnames(StringInfo buf, int varno, RangeTblEntry *rte, bool qualify_col)
+{
+ bool first = true;
+ ListCell *lc;
+
+ Assert(rte);
+ Assert(rte->rtekind == RTE_FUNCTION);
+ Assert(rte->eref);
+
+ foreach(lc, rte->eref->colnames)
+ {
+ char *colname = strVal(lfirst(lc));
+
+ if (colname[0] == '\0')
+ continue;
+ if (!first)
+ appendStringInfoString(buf, ",");
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, varno);
+ appendStringInfoString(buf, quote_identifier(colname));
+ first = false;
}
}
@@ -2717,23 +2793,6 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
/* Required only to be passed down to deparseTargetList(). */
List *retrieved_attrs;
- /*
- * The lock on the relation will be held by upper callers, so it's
- * fine to open it with no lock here.
- */
- rel = table_open(rte->relid, NoLock);
-
- /*
- * The local name of the foreign table can not be recognized by the
- * foreign server and the table it references on foreign server might
- * have different column ordering or different columns than those
- * declared locally. Hence we have to deparse whole-row reference as
- * ROW(columns referenced locally). Construct this by deparsing a
- * "whole row" attribute.
- */
- attrs_used = bms_add_member(NULL,
- 0 - FirstLowInvalidHeapAttributeNumber);
-
/*
* In case the whole-row reference is under an outer join then it has
* to go NULL whenever the rest of the row goes NULL. Deparsing a join
@@ -2748,16 +2807,43 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
}
appendStringInfoString(buf, "ROW(");
- deparseTargetList(buf, rte, varno, rel, false, attrs_used, qualify_col,
- &retrieved_attrs);
+ if (rte->rtekind == RTE_RELATION)
+ {
+ /*
+ * The local name of the foreign table can not be recognized by
+ * the foreign server and the table it references on foreign
+ * server might have different column ordering or different
+ * columns than those declared locally. Hence we have to deparse
+ * whole-row reference as ROW(columns referenced locally).
+ * Construct this by deparsing a "whole row" attribute.
+ */
+ attrs_used = bms_add_member(NULL,
+ 0 - FirstLowInvalidHeapAttributeNumber);
+
+ /*
+ * The lock on the relation will be held by upper callers, so it's
+ * fine to open it with no lock here.
+ */
+ rel = table_open(rte->relid, NoLock);
+ deparseTargetList(buf, rte, varno, rel, false, attrs_used, qualify_col,
+ &retrieved_attrs);
+ table_close(rel, NoLock);
+ bms_free(attrs_used);
+ }
+ else if (rte->rtekind == RTE_FUNCTION)
+ {
+ /*
+ * Function call is translated as-is, function returns the same
+ * columns in the same order as on local server
+ */
+ deparseFuncColnames(buf, varno, rte, qualify_col);
+ }
appendStringInfoChar(buf, ')');
/* Complete the CASE WHEN statement started above. */
if (qualify_col)
appendStringInfoString(buf, " END");
- table_close(rel, NoLock);
- bms_free(attrs_used);
}
else
{
@@ -2772,29 +2858,40 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
* If it's a column of a foreign table, and it has the column_name FDW
* option, use that value.
*/
- options = GetForeignColumnOptions(rte->relid, varattno);
- foreach(lc, options)
+ if (rte->rtekind == RTE_RELATION)
{
- DefElem *def = (DefElem *) lfirst(lc);
-
- if (strcmp(def->defname, "column_name") == 0)
+ options = GetForeignColumnOptions(rte->relid, varattno);
+ foreach(lc, options)
{
- colname = defGetString(def);
- break;
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "column_name") == 0)
+ {
+ colname = defGetString(def);
+ break;
+ }
}
- }
- /*
- * If it's a column of a regular table or it doesn't have column_name
- * FDW option, use attribute name.
- */
- if (colname == NULL)
- colname = get_attname(rte->relid, varattno, false);
+ /*
+ * If it's a column of a regular table or it doesn't have
+ * column_name FDW option, use attribute name.
+ */
+ if (colname == NULL)
+ colname = get_attname(rte->relid, varattno, false);
- if (qualify_col)
- ADD_REL_QUALIFIER(buf, varno);
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, varno);
- appendStringInfoString(buf, quote_identifier(colname));
+ appendStringInfoString(buf, quote_identifier(colname));
+ }
+ else if (rte->rtekind == RTE_FUNCTION)
+ {
+ colname = get_rte_attribute_name(rte, varattno);
+
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, varno);
+ appendStringInfoString(buf, quote_identifier(colname));
+ }
}
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f2bcd6aa98..686ce1ac61 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12345,3 +12345,371 @@ SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END
-- Clean up
\set VERBOSITY default
RESET debug_discard_caches;
+-- ===================================================================
+-- test function scan pushdown
+-- ===================================================================
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+ SERVER loopback OPTIONS (table_name 'base_tbl');
+ALTER FOREIGN TABLE remote_tbl OPTIONS (use_remote_estimate 'true');
+CREATE TABLE base_tbl1 (c int, d text);
+CREATE FOREIGN TABLE remote_tbl1 (c int, d text)
+ SERVER loopback OPTIONS (table_name 'base_tbl1');
+ALTER FOREIGN TABLE remote_tbl1 OPTIONS (use_remote_estimate 'true');
+INSERT INTO remote_tbl SELECT g, g*2 from generate_series(1,1000) g;
+INSERT INTO remote_tbl1 SELECT g, 'text'|| g from generate_series(1,500) g;
+ANALYZE base_tbl;
+ANALYZE base_tbl1;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: r.a, r.b, n.n
+ Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() n)
+ Remote SQL: SELECT r1.a, r1.b, r2.n FROM (public.base_tbl r1 INNER JOIN unnest('{2,3,4}'::integer[]) r2 (n) ON (((r1.a = r2.n))))
+(4 rows)
+
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n
+ORDER BY r.a;
+ a | b | n
+---+---+---
+ 2 | 4 | 2
+ 3 | 6 | 3
+ 4 | 8 | 4
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: n.n, r.a, r.b
+ Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() n)
+ Remote SQL: SELECT r1.n, r2.a, r2.b FROM (public.base_tbl r2 INNER JOIN unnest('{2,3,4}'::integer[]) r1 (n) ON (((r2.a = r1.n))))
+(4 rows)
+
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n
+ORDER BY r.a;
+ n | a | b
+---+---+---
+ 2 | 2 | 4
+ 3 | 3 | 6
+ 4 | 4 | 8
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: r.a, r.b, r1.c, r1.d, n.n
+ Relations: ((public.remote_tbl r) INNER JOIN (public.remote_tbl1 r1)) INNER JOIN (pg_catalog.unnest() n)
+ Remote SQL: SELECT r1.a, r1.b, r2.c, r2.d, r3.n FROM ((public.base_tbl r1 INNER JOIN public.base_tbl1 r2 ON (((r1.a = r2.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n) ON (((r1.a = r3.n))))
+(4 rows)
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a
+ORDER BY r.a;
+ a | b | c | d | n
+---+---+---+-------+---
+ 3 | 6 | 3 | text3 | 3
+ 4 | 8 | 4 | text4 | 4
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*,n from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: r.a, r.b, n.n
+ Relations: ((public.remote_tbl r) INNER JOIN (public.remote_tbl1 r1)) INNER JOIN (pg_catalog.unnest() n)
+ Remote SQL: SELECT r1.a, r1.b, r3.n FROM ((public.base_tbl r1 INNER JOIN public.base_tbl1 r2 ON (((r1.a = r2.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n) ON (((r1.a = r3.n)) AND ((r3.n > 3))))
+(4 rows)
+
+SELECT * from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+ a | b | c | d | n
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+CREATE OR REPLACE FUNCTION get_constant_texts()
+RETURNS TABLE (text_value text) AS $$
+BEGIN
+ RETURN QUERY VALUES
+ ('text1'),
+ ('text4');
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+ALTER EXTENSION postgres_fdw ADD FUNCTION get_constant_texts();
+ALTER SERVER loopback OPTIONS (extensions 'postgres_fdw');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*, t.n from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, txt)
+WHERE r.c = t.n AND r.d = t.txt;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: r.c, r.d, t.n
+ Relations: (public.remote_tbl1 r) INNER JOIN (ROWS FROM(pg_catalog.unnest(), public.get_constant_texts()) t)
+ Remote SQL: SELECT r1.c, r1.d, r2.n FROM (public.base_tbl1 r1 INNER JOIN ROWS FROM (unnest('{3,4}'::integer[]), public.get_constant_texts()) r2 (n,txt) ON (((r1.c = r2.n)) AND ((r2.txt = r1.d))))
+(4 rows)
+
+SELECT r.*, t.txt from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, txt)
+WHERE r.c = t.n AND r.d = t.txt;
+ c | d | txt
+---+-------+-------
+ 4 | text4 | text4
+(1 row)
+
+-- Complex types
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: r.a, r.b, t.bx
+ Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() t)
+ Remote SQL: SELECT r1.a, r1.b, r2.bx FROM (public.base_tbl r1 INNER JOIN unnest('{(2,3),(-2,-3)}'::box[]) r2 (bx) ON (((r1.a = area(r2.bx)))))
+(4 rows)
+
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx)
+ORDER BY r.a;
+ a | b | bx
+----+----+---------------
+ 24 | 48 | (2,3),(-2,-3)
+(1 row)
+
+-- DML
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+ Output: r.a, r.b
+ -> Foreign Update
+ Remote SQL: UPDATE public.base_tbl r1 SET b = 5 FROM unnest('{(2,3),(-2,-3)}'::box[]) r2 (bx) WHERE ((r1.a = area(r2.bx))) RETURNING r1.a, r1.b
+(4 rows)
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+ a | b
+----+---
+ 24 | 5
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+ Output: r.a, r.b
+ Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+ -> Foreign Scan
+ Output: CASE WHEN (random() >= '0'::double precision) THEN 5 ELSE 0 END, r.ctid, r.*, t.*
+ Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() t)
+ Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.a, r1.b) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.bx) END FROM (public.base_tbl r1 INNER JOIN unnest('{(2,3),(-2,-3)}'::box[]) r2 (bx) ON (((r1.a = area(r2.bx))))) FOR UPDATE OF r1
+ -> Hash Join
+ Output: r.ctid, r.*, t.*
+ Hash Cond: ((r.a)::double precision = area(t.bx))
+ -> Foreign Scan on public.remote_tbl r
+ Output: r.ctid, r.*, r.a
+ Remote SQL: SELECT a, b, ctid FROM public.base_tbl FOR UPDATE
+ -> Hash
+ Output: t.*, t.bx
+ -> Function Scan on pg_catalog.unnest t
+ Output: t.*, t.bx
+ Function Call: unnest('{(2,3),(-2,-3)}'::box[])
+(18 rows)
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+ a | b
+----+---
+ 24 | 5
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+ Output: r.a, r.b
+ -> Foreign Update
+ Remote SQL: UPDATE public.base_tbl r1 SET b = 5 FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx) WHERE ((r1.a >= r2.l)) AND ((r1.a <= area(r2.bx))) RETURNING r1.a, r1.b
+(4 rows)
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+ a | b
+----+---
+ 10 | 5
+ 11 | 5
+ 12 | 5
+ 13 | 5
+ 14 | 5
+ 15 | 5
+ 16 | 5
+ 17 | 5
+ 18 | 5
+ 19 | 5
+ 20 | 5
+ 21 | 5
+ 22 | 5
+ 23 | 5
+ 25 | 5
+ 26 | 5
+ 27 | 5
+ 28 | 5
+ 24 | 5
+(19 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+ Output: r.a, r.b
+ Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+ -> Foreign Scan
+ Output: CASE WHEN (random() >= '0'::double precision) THEN 5 ELSE 0 END, r.ctid, r.*, t.*
+ Relations: (public.remote_tbl r) INNER JOIN (ROWS FROM(pg_catalog.unnest(), pg_catalog.unnest()) t)
+ Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.a, r1.b) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.l,r2.bx) END FROM (public.base_tbl r1 INNER JOIN ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx) ON (((r1.a >= r2.l)) AND ((r1.a <= area(r2.bx))))) FOR UPDATE OF r1
+ -> Nested Loop
+ Output: r.ctid, r.*, t.*
+ Join Filter: ((r.a >= t.l) AND ((r.a)::double precision <= area(t.bx)))
+ -> Foreign Scan on public.remote_tbl r
+ Output: r.ctid, r.*, r.a
+ Remote SQL: SELECT a, b, ctid FROM public.base_tbl FOR UPDATE
+ -> Function Scan on t
+ Output: t.*, t.l, t.bx
+ Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+(16 rows)
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+ a | b
+----+---
+ 10 | 5
+ 11 | 5
+ 12 | 5
+ 13 | 5
+ 14 | 5
+ 15 | 5
+ 16 | 5
+ 17 | 5
+ 18 | 5
+ 19 | 5
+ 20 | 5
+ 21 | 5
+ 22 | 5
+ 23 | 5
+ 25 | 5
+ 26 | 5
+ 27 | 5
+ 28 | 5
+ 24 | 5
+(19 rows)
+
+-- Test that local functions are not pushed down
+CREATE OR REPLACE FUNCTION f(INTEGER)
+RETURNS SETOF INTEGER
+LANGUAGE sql AS 'select generate_series(1,$1);' IMMUTABLE;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, f(10) n
+WHERE r.a = n;
+ QUERY PLAN
+------------------------------------------------------
+ Hash Join
+ Output: r.a, r.b, (generate_series(1, 10))
+ Hash Cond: (r.a = (generate_series(1, 10)))
+ -> Foreign Scan on public.remote_tbl r
+ Output: r.a, r.b
+ Remote SQL: SELECT a, b FROM public.base_tbl
+ -> Hash
+ Output: (generate_series(1, 10))
+ -> ProjectSet
+ Output: generate_series(1, 10)
+ -> Result
+(11 rows)
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a AND n > 3;
+ a | b | c | d | n
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+-- Test that a function that returns a record is not pushed down
+CREATE OR REPLACE FUNCTION f_ret_record() RETURNS record AS $$ SELECT (1,2)::record $$ language SQL IMMUTABLE;
+ALTER EXTENSION postgres_fdw ADD function f_ret_record();
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int)
+WHERE s.a = rt.a;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Nested Loop
+ Output: s.*
+ -> Function Scan on public.f_ret_record s
+ Output: s.*, s.a
+ Function Call: f_ret_record()
+ -> Foreign Scan on public.remote_tbl rt
+ Output: rt.a, rt.b
+ Remote SQL: SELECT a FROM public.base_tbl WHERE ((a = $1::integer))
+(8 rows)
+
+SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int)
+WHERE s.a = rt.a;
+ s
+-------
+ (1,2)
+(1 row)
+
+DROP FUNCTION f(INTEGER);
+ALTER EXTENSION postgres_fdw DROP FUNCTION get_constant_texts();
+ALTER EXTENSION postgres_fdw DROP FUNCTION f_ret_record();
+DROP FUNCTION get_constant_texts();
+DROP FUNCTION f_ret_record();
+DROP TABLE base_tbl, base_tbl1;
+DROP FOREIGN TABLE remote_tbl, remote_tbl1;
+-- Test that function WITH ORDINALITY is not pushed down
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int) SERVER loopback OPTIONS (table_name 'base_tbl');
+INSERT INTO remote_tbl VALUES (1, 2), (2, 3), (3, 4), (5, 6);
+ANALYZE remote_tbl;
+SET enable_mergejoin TO false;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY
+WHERE a=unnest;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Hash Join
+ Output: remote_tbl.a, remote_tbl.b, unnest.unnest, unnest.ordinality
+ Hash Cond: (remote_tbl.a = unnest.unnest)
+ -> Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b
+ Remote SQL: SELECT a, b FROM public.base_tbl
+ -> Hash
+ Output: unnest.unnest, unnest.ordinality
+ -> Function Scan on pg_catalog.unnest
+ Output: unnest.unnest, unnest.ordinality
+ Function Call: unnest('{1,2}'::integer[])
+(11 rows)
+
+SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY
+WHERE a=unnest;
+ a | b | unnest | ordinality
+---+---+--------+------------
+ 1 | 2 | 1 | 1
+ 2 | 3 | 2 | 2
+(2 rows)
+
+DROP TABLE base_tbl;
+DROP FOREIGN TABLE remote_tbl;
+RESET enable_mergejoin;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 53733d642d..5d0a339ca0 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -36,6 +36,7 @@
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
+#include "optimizer/clauses.h"
#include "parser/parsetree.h"
#include "postgres_fdw.h"
#include "storage/latch.h"
@@ -406,6 +407,14 @@ static void postgresGetForeignJoinPaths(PlannerInfo *root,
RelOptInfo *innerrel,
JoinType jointype,
JoinPathExtraData *extra);
+
+static void postgresTryShippableJoinPaths(PlannerInfo *root,
+ RelOptInfo *joinrel,
+ RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ JoinType jointype,
+ JoinPathExtraData *extra);
+
static bool postgresRecheckForeignScan(ForeignScanState *node,
TupleTableSlot *slot);
static void postgresGetForeignUpperPaths(PlannerInfo *root,
@@ -476,7 +485,7 @@ static void store_returning_result(PgFdwModifyState *fmstate,
static void finish_foreign_modify(PgFdwModifyState *fmstate);
static void deallocate_query(PgFdwModifyState *fmstate);
static List *build_remote_returning(Index rtindex, Relation rel,
- List *returningList);
+ List *returningList, Var *tid);
static void rebuild_fdw_scan_tlist(ForeignScan *fscan, List *tlist);
static void execute_dml_stmt(ForeignScanState *node);
static TupleTableSlot *get_returning_data(ForeignScanState *node);
@@ -542,6 +551,11 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo,
const PgFdwRelationInfo *fpinfo_i);
static int get_batch_size_option(Relation rel);
+static bool is_nonrel_relinfo_ok(PlannerInfo *root, RelOptInfo *foreignrel);
+static void init_fpinfo(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid,
+ PgFdwRelationInfo *existing_fpinfo);
/*
* Foreign-data wrapper handler function: return a struct with pointers
@@ -597,6 +611,7 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
/* Support functions for join push-down */
routine->GetForeignJoinPaths = postgresGetForeignJoinPaths;
+ routine->TryShippableJoinPaths = postgresTryShippableJoinPaths;
/* Support functions for upper relation push-down */
routine->GetForeignUpperPaths = postgresGetForeignUpperPaths;
@@ -621,10 +636,32 @@ static void
postgresGetForeignRelSize(PlannerInfo *root,
RelOptInfo *baserel,
Oid foreigntableid)
+{
+ init_fpinfo(root, baserel, foreigntableid, NULL);
+}
+
+/*
+ * init_fpinfo
+ *
+ * Either initialize fpinfo based on foreign table or generate one, based on
+ * existing fpinfo.
+ * Also estimate # of rows and width of the result of the scan.
+ *
+ * We should consider the effect of all baserestrictinfo clauses here, but
+ * not any join clauses.
+ */
+static void
+init_fpinfo(PlannerInfo *root,
+ RelOptInfo *baserel,
+ Oid foreigntableid,
+ PgFdwRelationInfo *existing_fpinfo)
{
PgFdwRelationInfo *fpinfo;
ListCell *lc;
+ Assert(existing_fpinfo || foreigntableid != InvalidOid);
+ Assert(existing_fpinfo == NULL || foreigntableid == InvalidOid);
+
/*
* We use PgFdwRelationInfo to pass various information to subsequent
* functions.
@@ -635,40 +672,64 @@ postgresGetForeignRelSize(PlannerInfo *root,
/* Base foreign tables need to be pushed down always. */
fpinfo->pushdown_safe = true;
- /* Look up foreign-table catalog info. */
- fpinfo->table = GetForeignTable(foreigntableid);
- fpinfo->server = GetForeignServer(fpinfo->table->serverid);
-
- /*
- * Extract user-settable option values. Note that per-table settings of
- * use_remote_estimate, fetch_size and async_capable override per-server
- * settings of them, respectively.
- */
- fpinfo->use_remote_estimate = false;
- fpinfo->fdw_startup_cost = DEFAULT_FDW_STARTUP_COST;
- fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
- fpinfo->shippable_extensions = NIL;
- fpinfo->fetch_size = 100;
- fpinfo->async_capable = false;
-
- apply_server_options(fpinfo);
- apply_table_options(fpinfo);
+ if (existing_fpinfo)
+ {
+ /* We don't have any table, related to query */
+ fpinfo->table = NULL;
+ fpinfo->server = existing_fpinfo->server;
+ }
+ else
+ {
+ /* Look up foreign-table catalog info. */
+ fpinfo->table = GetForeignTable(foreigntableid);
+ fpinfo->server = GetForeignServer(fpinfo->table->serverid);
+ }
- /*
- * If the table or the server is configured to use remote estimates,
- * identify which user to do remote access as during planning. This
- * should match what ExecCheckPermissions() does. If we fail due to lack
- * of permissions, the query would have failed at runtime anyway.
- */
- if (fpinfo->use_remote_estimate)
+ if (existing_fpinfo)
{
- Oid userid;
+ merge_fdw_options(fpinfo, existing_fpinfo, NULL);
+ fpinfo->user = existing_fpinfo->user;
- userid = OidIsValid(baserel->userid) ? baserel->userid : GetUserId();
- fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid);
+ /*
+ * Don't try to execute anything on remote server for
+ * non-relation-based query
+ */
+ fpinfo->use_remote_estimate = false;
}
else
- fpinfo->user = NULL;
+ {
+ /*
+ * Extract user-settable option values. Note that per-table settings
+ * of use_remote_estimate, fetch_size and async_capable override
+ * per-server settings of them, respectively.
+ */
+ fpinfo->use_remote_estimate = false;
+ fpinfo->fdw_startup_cost = DEFAULT_FDW_STARTUP_COST;
+ fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
+ fpinfo->shippable_extensions = NIL;
+ fpinfo->fetch_size = 100;
+ fpinfo->async_capable = false;
+ fpinfo->is_generated = false;
+
+ apply_server_options(fpinfo);
+ apply_table_options(fpinfo);
+
+ /*
+ * If the table or the server is configured to use remote estimates,
+ * identify which user to do remote access as during planning. This
+ * should match what ExecCheckPermissions() does. If we fail due to lack
+ * of permissions, the query would have failed at runtime anyway.
+ */
+ if (fpinfo->use_remote_estimate)
+ {
+ Oid userid;
+
+ userid = OidIsValid(baserel->userid) ? baserel->userid : GetUserId();
+ fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid);
+ }
+ else
+ fpinfo->user = NULL;
+ }
/*
* Identify which baserestrictinfo clauses can be sent to the remote
@@ -783,6 +844,9 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->hidden_subquery_rels = NULL;
/* Set the relation index. */
fpinfo->relation_index = baserel->relid;
+ if (existing_fpinfo)
+ /* Mark fpinfo generated */
+ fpinfo->is_generated = true;
}
/*
@@ -1476,13 +1540,72 @@ get_tupdesc_for_join_scan_tuples(ForeignScanState *node)
if (!IsA(var, Var) || var->varattno != 0)
continue;
rte = list_nth(estate->es_range_table, var->varno - 1);
- if (rte->rtekind != RTE_RELATION)
- continue;
- reltype = get_rel_type_id(rte->relid);
- if (!OidIsValid(reltype))
- continue;
- att->atttypid = reltype;
- /* shouldn't need to change anything else */
+ if (rte->rtekind == RTE_RELATION)
+ {
+ reltype = get_rel_type_id(rte->relid);
+ if (!OidIsValid(reltype))
+ continue;
+ att->atttypid = reltype;
+ /* shouldn't need to change anything else */
+ }
+ else if (rte->rtekind == RTE_FUNCTION)
+ {
+ RangeTblFunction *rtfunc;
+ TupleDesc td;
+ Oid funcrettype;
+ int num_funcs,
+ attnum;
+ ListCell *lc,
+ *lctype,
+ *lcname;
+ bool functype_OK = true;
+ List *functypes = NIL;
+
+ if (rte->funcordinality)
+ continue;
+
+ num_funcs = list_length(rte->functions);
+ Assert(num_funcs >= 0);
+
+ foreach(lc, rte->functions)
+ {
+ rtfunc = (RangeTblFunction *) lfirst(lc);
+ get_expr_result_type(rtfunc->funcexpr, &funcrettype, NULL);
+ if (!OidIsValid(funcrettype) || funcrettype == RECORDOID)
+ {
+ functype_OK = false;
+ break;
+ }
+ functypes = lappend_oid(functypes, funcrettype);
+ }
+ if (!functype_OK)
+ continue;
+ td = CreateTemplateTupleDesc(num_funcs);
+
+ /*
+ * funcrettype != RECORD, so we have only one return attribute per
+ * function
+ */
+ Assert(list_length(rte->eref->colnames) == num_funcs);
+ attnum = 1;
+ forthree(lc, rte->functions, lctype, functypes, lcname, rte->eref->colnames)
+ {
+ char *colname;
+
+ rtfunc = (RangeTblFunction *) lfirst(lc);
+ funcrettype = lfirst_oid(lctype);
+ colname = strVal(lfirst(lcname));
+
+ TupleDescInitEntry(td, (AttrNumber) attnum, colname,
+ funcrettype, -1, 0);
+ TupleDescInitEntryCollation(td, (AttrNumber) attnum,
+ exprCollation(rtfunc->funcexpr));
+ attnum++;
+ }
+
+ assign_record_type_typmod(td);
+ att->atttypmod = td->tdtypmod;
+ }
}
return tupdesc;
}
@@ -1518,14 +1641,26 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
/*
* Identify which user to do the remote access as. This should match what
- * ExecCheckPermissions() does.
+ * ExecCheckPermissions() does. In case of a join or aggregate, scan RTEs
+ * until RTE_RELATION is found. We would get the same result from any.
*/
userid = OidIsValid(fsplan->checkAsUser) ? fsplan->checkAsUser : GetUserId();
if (fsplan->scan.scanrelid > 0)
+ {
rtindex = fsplan->scan.scanrelid;
+ rte = exec_rt_fetch(rtindex, estate);
+ }
else
- rtindex = bms_next_member(fsplan->fs_base_relids, -1);
- rte = exec_rt_fetch(rtindex, estate);
+ {
+ rtindex = -1;
+ while ((rtindex = bms_next_member(fsplan->fs_base_relids, rtindex)) >= 0)
+ {
+ rte = exec_rt_fetch(rtindex, estate);
+ if (rte && rte->rtekind == RTE_RELATION)
+ break;
+ }
+ Assert(rte && rte->rtekind == RTE_RELATION);
+ }
/* Get info about foreign table. */
table = GetForeignTable(rte->relid);
@@ -2566,8 +2701,30 @@ postgresPlanDirectModify(PlannerInfo *root,
* node below.
*/
if (fscan->scan.scanrelid == 0)
+ {
+ ListCell *lc;
+ Var *tid_var = NULL;
+
+ /*
+ * We should explicitly add tableoid to returning list if it's
+ * requested
+ */
+ foreach(lc, processed_tlist)
+ {
+ TargetEntry *tle = lfirst_node(TargetEntry, lc);
+ Var *var = (Var *) tle->expr;
+
+ if (IsA(var, Var) && (var->varattno == TableOidAttributeNumber) && (strcmp(tle->resname, "tableoid") == 0))
+ {
+ tid_var = var;
+ break;
+ }
+
+ }
+
returningList = build_remote_returning(resultRelation, rel,
- returningList);
+ returningList, tid_var);
+ }
}
/*
@@ -2889,21 +3046,66 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
rti += rtoffset;
Assert(bms_is_member(rti, plan->fs_base_relids));
rte = rt_fetch(rti, es->rtable);
- Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
- relname = get_rel_name(rte->relid);
- if (es->verbose)
+ if (rte->rtekind == RTE_RELATION)
{
- char *namespace;
-
- namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
- appendStringInfo(relations, "%s.%s",
- quote_identifier(namespace),
- quote_identifier(relname));
+ // Note: relname may be uninitialized.
+ relname = get_rel_name(rte->relid);
+ if (es->verbose)
+ {
+ char *namespace;
+
+ namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
+ appendStringInfo(relations, "%s.%s",
+ quote_identifier(namespace),
+ quote_identifier(relname));
+ }
+ else
+ appendStringInfoString(relations,
+ quote_identifier(relname));
+ }
+ else if (rte->rtekind == RTE_FUNCTION)
+ {
+ ListCell *lc;
+ int n;
+ bool first = true;
+
+
+ n = list_length(rte->functions);
+
+ if (n > 1)
+ appendStringInfo(relations, "ROWS FROM(");
+ foreach(lc, rte->functions)
+ {
+ RangeTblFunction *rtfunc = (RangeTblFunction *) lfirst(lc);
+
+ if (!first)
+ appendStringInfoString(relations, ", ");
+ else
+ first = false;
+
+ if (IsA(rtfunc->funcexpr, FuncExpr))
+ {
+ FuncExpr *funcexpr = (FuncExpr *) rtfunc->funcexpr;
+ Oid funcid = funcexpr->funcid;
+
+ relname = get_func_name(funcid);
+ if (es->verbose)
+ {
+ char *namespace;
+
+ namespace = get_namespace_name(get_func_namespace(funcid));
+ appendStringInfo(relations, "%s.%s()",
+ quote_identifier(namespace),
+ quote_identifier(relname));
+ }
+ else
+ appendStringInfo(relations, "%s()", quote_identifier(relname));
+ }
+ }
+ if (n > 1)
+ appendStringInfo(relations, ")");
}
- else
- appendStringInfoString(relations,
- quote_identifier(relname));
refname = (char *) list_nth(es->rtable_names, rti - 1);
if (refname == NULL)
refname = rte->eref->aliasname;
@@ -4427,7 +4629,7 @@ deallocate_query(PgFdwModifyState *fmstate)
* UPDATE/DELETE .. RETURNING on a join directly
*/
static List *
-build_remote_returning(Index rtindex, Relation rel, List *returningList)
+build_remote_returning(Index rtindex, Relation rel, List *returningList, Var *tid)
{
bool have_wholerow = false;
List *tlist = NIL;
@@ -4436,6 +4638,19 @@ build_remote_returning(Index rtindex, Relation rel, List *returningList)
Assert(returningList);
+ /*
+ * If tid is requested, add it to the returning list
+ */
+ if (tid)
+ {
+ tlist = lappend(tlist,
+ makeTargetEntry((Expr *) tid,
+ list_length(tlist) + 1,
+ NULL,
+ false));
+
+ }
+
vars = pull_var_clause((Node *) returningList, PVC_INCLUDE_PLACEHOLDERS);
/*
@@ -4727,11 +4942,13 @@ init_returning_filter(PgFdwDirectModifyState *dmstate,
if (attrno < 0)
{
/*
- * We don't retrieve system columns other than ctid and oid.
+ * We don't retrieve system columns other than ctid and oid,
+ * but locally-generated tableoid can appear in returning
+ * list.
*/
if (attrno == SelfItemPointerAttributeNumber)
dmstate->ctidAttno = i;
- else
+ else if (attrno != TableOidAttributeNumber)
Assert(false);
dmstate->hasSystemCols = true;
}
@@ -5745,6 +5962,63 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
return commands;
}
+/*
+ * Determine if foreignrel, not backed by foreign
+ * table, is fine to push down.
+ */
+static bool
+is_nonrel_relinfo_ok(PlannerInfo *root, RelOptInfo *foreignrel)
+{
+ RangeTblEntry *rte;
+ RangeTblFunction *rtfunc;
+ TupleDesc tupdesc;
+ Oid funcrettype;
+
+ rte = planner_rt_fetch(foreignrel->relid, root);
+
+ if (!rte)
+ return false;
+
+ Assert(foreignrel->fdw_private);
+
+ if (rte->rtekind == RTE_FUNCTION)
+ {
+ ListCell *lc;
+
+ /*
+ * WITH ORDINALITY pushdown is not implemented yet.
+ */
+ if (rte->funcordinality)
+ return false;
+
+ Assert(list_length(rte->functions) >= 1);
+ foreach(lc, rte->functions)
+ {
+ rtfunc = (RangeTblFunction *) lfirst(lc);
+
+ get_expr_result_type(rtfunc->funcexpr, &funcrettype, &tupdesc);
+
+ /*
+ * Remote server requires a well defined return type for a
+ * function pushdown.
+ */
+ if (!OidIsValid(funcrettype) || funcrettype == RECORDOID || funcrettype == VOIDOID)
+ return false;
+
+ if (contain_var_clause(rtfunc->funcexpr) ||
+ contain_mutable_functions(rtfunc->funcexpr) ||
+ contain_subplans(rtfunc->funcexpr))
+ return false;
+ if (!is_foreign_expr(root, foreignrel, (Expr *) rtfunc->funcexpr))
+ return false;
+ }
+
+ return true;
+ }
+
+ return false;
+}
+
/*
* Check if reltarget is safe enough to push down semi-join. Reltarget is not
* safe, if it contains references to inner rel relids, which do not belong to
@@ -6477,6 +6751,43 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
/* XXX Consider parameterized paths for the join relation */
}
+/*
+ * postgresTryShippableJoinPaths
+ *
+ * Try to add foreign join of foreign relation with shippable RTE.
+ */
+static void
+postgresTryShippableJoinPaths(PlannerInfo *root,
+ RelOptInfo *joinrel,
+ RelOptInfo *outerrel,
+ RelOptInfo *innerrel,
+ JoinType jointype,
+ JoinPathExtraData *extra)
+{
+ PgFdwRelationInfo *fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
+ PgFdwRelationInfo *fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
+
+ if (fpinfo_o == NULL)
+ /* Outer path is not foreign relation or foreign JOIN. */
+ return;
+
+ if (joinrel->fdwroutine != NULL || innerrel->reloptkind != RELOPT_BASEREL)
+ return;
+
+ if (fpinfo_i == NULL || fpinfo_i->is_generated)
+ init_fpinfo(root, innerrel, InvalidOid, fpinfo_o);
+
+ if (!is_nonrel_relinfo_ok(root, innerrel))
+ return;
+
+ joinrel->serverid = outerrel->serverid;
+ joinrel->userid = outerrel->userid;
+ joinrel->useridiscurrent = outerrel->useridiscurrent;
+ joinrel->fdwroutine = outerrel->fdwroutine;
+
+ postgresGetForeignJoinPaths(root, joinrel, outerrel, innerrel, jointype, extra);
+}
+
/*
* Assess whether the aggregation, grouping and having operations can be pushed
* down to the foreign server. As a side effect, save information we obtain in
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 9e501660d1..772144d611 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -52,6 +52,12 @@ typedef struct PgFdwRelationInfo
/* True means that the query_pathkeys is safe to push down */
bool qp_is_pushdown_safe;
+ /*
+ * True means that PgFdwRelationInfo is not extracted from catalogs, but
+ * generated
+ */
+ bool is_generated;
+
/* Cost and selectivity of local_conds. */
QualCost local_conds_cost;
Selectivity local_conds_sel;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 372fe6dad1..6c717b58b8 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4278,3 +4278,151 @@ SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END
-- Clean up
\set VERBOSITY default
RESET debug_discard_caches;
+
+-- ===================================================================
+-- test function scan pushdown
+-- ===================================================================
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+ SERVER loopback OPTIONS (table_name 'base_tbl');
+ALTER FOREIGN TABLE remote_tbl OPTIONS (use_remote_estimate 'true');
+CREATE TABLE base_tbl1 (c int, d text);
+CREATE FOREIGN TABLE remote_tbl1 (c int, d text)
+ SERVER loopback OPTIONS (table_name 'base_tbl1');
+ALTER FOREIGN TABLE remote_tbl1 OPTIONS (use_remote_estimate 'true');
+
+INSERT INTO remote_tbl SELECT g, g*2 from generate_series(1,1000) g;
+INSERT INTO remote_tbl1 SELECT g, 'text'|| g from generate_series(1,500) g;
+ANALYZE base_tbl;
+ANALYZE base_tbl1;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n;
+
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n;
+
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a;
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*,n from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+
+SELECT * from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+
+CREATE OR REPLACE FUNCTION get_constant_texts()
+RETURNS TABLE (text_value text) AS $$
+BEGIN
+ RETURN QUERY VALUES
+ ('text1'),
+ ('text4');
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+ALTER EXTENSION postgres_fdw ADD FUNCTION get_constant_texts();
+ALTER SERVER loopback OPTIONS (extensions 'postgres_fdw');
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*, t.n from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, txt)
+WHERE r.c = t.n AND r.d = t.txt;
+
+SELECT r.*, t.txt from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, txt)
+WHERE r.c = t.n AND r.d = t.txt;
+
+-- Complex types
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx);
+
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx)
+ORDER BY r.a;
+
+-- DML
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+-- Test that local functions are not pushed down
+CREATE OR REPLACE FUNCTION f(INTEGER)
+RETURNS SETOF INTEGER
+LANGUAGE sql AS 'select generate_series(1,$1);' IMMUTABLE;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, f(10) n
+WHERE r.a = n;
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a AND n > 3;
+
+-- Test that a function that returns a record is not pushed down
+CREATE OR REPLACE FUNCTION f_ret_record() RETURNS record AS $$ SELECT (1,2)::record $$ language SQL IMMUTABLE;
+ALTER EXTENSION postgres_fdw ADD function f_ret_record();
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int)
+WHERE s.a = rt.a;
+
+SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int)
+WHERE s.a = rt.a;
+
+DROP FUNCTION f(INTEGER);
+ALTER EXTENSION postgres_fdw DROP FUNCTION get_constant_texts();
+ALTER EXTENSION postgres_fdw DROP FUNCTION f_ret_record();
+DROP FUNCTION get_constant_texts();
+DROP FUNCTION f_ret_record();
+DROP TABLE base_tbl, base_tbl1;
+DROP FOREIGN TABLE remote_tbl, remote_tbl1;
+
+-- Test that function WITH ORDINALITY is not pushed down
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int) SERVER loopback OPTIONS (table_name 'base_tbl');
+INSERT INTO remote_tbl VALUES (1, 2), (2, 3), (3, 4), (5, 6);
+ANALYZE remote_tbl;
+SET enable_mergejoin TO false;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY
+WHERE a=unnest;
+
+SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY
+WHERE a=unnest;
+
+DROP TABLE base_tbl;
+DROP FOREIGN TABLE remote_tbl;
+RESET enable_mergejoin;
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 3971138480..57430a8165 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -333,6 +333,17 @@ add_paths_to_joinrel(PlannerInfo *root,
outerrel, innerrel,
jointype, &extra);
+ /*
+ * If push down of join is not possible we can try to join foreign
+ * relation with shippable RTE. In this case we have a chance to push down
+ * this join yet.
+ */
+ else if (outerrel->fdwroutine &&
+ outerrel->fdwroutine->TryShippableJoinPaths)
+ outerrel->fdwroutine->TryShippableJoinPaths(root, joinrel,
+ outerrel, innerrel,
+ jointype, &extra);
+
/*
* 6. Finally, give extensions a chance to manipulate the path list. They
* could add new paths (such as CustomPaths) by calling add_path(), or
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 91c7c4fe2f..088845c00f 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -552,7 +552,6 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, List *rteperminfos,
newrte->joinleftcols = NIL;
newrte->joinrightcols = NIL;
newrte->join_using_alias = NULL;
- newrte->functions = NIL;
newrte->tablefunc = NULL;
newrte->values_lists = NIL;
newrte->coltypes = NIL;
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index fcde3876b2..0ccd6776a8 100644
--- a/src/include/foreign/fdwapi.h
+++ b/src/include/foreign/fdwapi.h
@@ -221,6 +221,7 @@ typedef struct FdwRoutine
/* Functions for remote-join planning */
GetForeignJoinPaths_function GetForeignJoinPaths;
+ GetForeignJoinPaths_function TryShippableJoinPaths;
/* Functions for remote upper-relation (post scan/join) planning */
GetForeignUpperPaths_function GetForeignUpperPaths;
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f551624afb..3fda68bf7f 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1681,10 +1681,10 @@ select pg_get_viewdef('tt14v', true);
-- ... and you can even EXPLAIN it ...
explain (verbose, costs off) select * from tt14v;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+--------------------------------------------
Function Scan on testviewschm2.tt14f t
- Output: t.f1, t.f3, t.f4
+ Output: t.f1, t."?dropped?column?", t.f4
Function Call: tt14f()
(3 rows)
--
2.43.0
