I did another round of review for the latest patch and well as performed
the sanity test, and
haven't found any functional issues. Found couple of issue, see in-line
comments
for the same.
On Thu, Feb 18, 2016 at 3:15 PM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp>
wrote:
> On 2016/02/12 21:19, Etsuro Fujita wrote:
>
>> Comments on specific points follow.
>>>
>>> This seems to need minor rebasing in the wake of the join pushdown patch.
>>>
>>
> Will do.
>>
>
> Done.
>
> + /* Likewise for ForeignScan that has pushed down
>>> INSERT/UPDATE/DELETE */
>>> + if (IsA(plan, ForeignScan) &&
>>> + (((ForeignScan *) plan)->operation == CMD_INSERT ||
>>> + ((ForeignScan *) plan)->operation == CMD_UPDATE ||
>>> + ((ForeignScan *) plan)->operation == CMD_DELETE))
>>> + return;
>>>
>>> I don't really understand why this is a good idea. Since target lists
>>> are only displayed with EXPLAIN (VERBOSE), I don't really understand
>>> what is to be gained by suppressing them in any case at all, and
>>> therefore I don't understand why it's a good idea to do it here,
>>> either. If there is a good reason, maybe the comment should explain
>>> what it is.
>>>
>>
> I think that displaying target lists would be confusing for users.
>>
>
> There seems no objection from you (or anyone), I left that as proposed,
> and added more comments.
>
> + /* Check point 1 */
>>> + if (operation == CMD_INSERT)
>>> + return false;
>>> +
>>> + /* Check point 2 */
>>> + if (nodeTag(subplan) != T_ForeignScan)
>>> + return false;
>>> +
>>> + /* Check point 3 */
>>> + if (subplan->qual != NIL)
>>> + return false;
>>> +
>>> + /* Check point 4 */
>>> + if (operation == CMD_UPDATE)
>>>
>>> These comments are referring to something in the function header
>>> further up, but you could instead just delete the stuff from the
>>> header and mention the actual conditions here.
>>>
>>
> Will fix.
>>
>
> Done.
>
> The patch doesn't allow the postgres_fdw to push down an UPDATE/DELETE on
> a foreign join, so I added one more condition here not to handle such
> cases. (I'm planning to propose a patch to handle such cases, in the next
> CF.)
>
I think we should place the checking foreign join condition before the
target columns, as foreign join condition is less costly then the target
columns.
>
> - If the first condition is supposed accept only CMD_UPDATE or
>>> CMD_DELETE, say if (operation != CMD_UPDATE || operation !=
>>> CMD_DELETE) rather than doing it this way. Is-not-insert may in this
>>> context be functionally equivalent to is-update-or-delete, but it's
>>> better to write the comment and the code so that they exactly match
>>> rather than kinda-match.
>>>
>>> - For point 2, use IsA(subplan, ForiegnScan).
>>>
>>
> Will fix.
>>
>
> Done.
>
> + /*
>>> + * ignore subplan if the FDW pushes down the
>>> command to the remote
>>> + * server
>>> + */
>>>
>>> This comment states what the code does, instead of explaining why it
>>> does it. Please update it so that it explains why it does it.
>>>
>>
> Will update.
>>
>
> Done.
>
> + List *fdwPushdowns; /* per-target-table FDW
>>> pushdown flags */
>>>
>>> Isn't a list of booleans an awfully inefficient representation? How
>>> about a Bitmapset?
>>>
>>
> OK, will do.
>>
>
> Done.
>
> + /*
>>> + * Prepare remote-parameter expressions for evaluation.
>>> (Note: in
>>> + * practice, we expect that all these expressions will be just
>>> Params, so
>>> + * we could possibly do something more efficient than using
>>> the full
>>> + * expression-eval machinery for this. But probably there
>>> would be little
>>> + * benefit, and it'd require postgres_fdw to know more than is
>>> desirable
>>> + * about Param evaluation.)
>>> + */
>>> + dpstate->param_exprs = (List *)
>>> + ExecInitExpr((Expr *) fsplan->fdw_exprs,
>>> + (PlanState *) node);
>>>
>>> This is an exact copy of an existing piece of code and its associated
>>> comment. A good bit of the surrounding code is copied, too. You need
>>> to refactor to avoid duplication, like by putting some of the code in
>>> a new function that both postgresBeginForeignScan and
>>> postgresBeginForeignModify can call.
>>>
>>> execute_dml_stmt() has some of the same disease.
>>>
>>
> Will do.
>>
>
> Done.
>
> Other changes:
>
> * I fixed docs as discussed before with Rushabh Lathia and Thom Brown.
>
> * I keep Rushabh's code change that we call PlanDMLPushdown only when all
> the required APIs are available with FDW, but for CheckValidResultRel, I
> left the code as-is (no changes to that function), to match the docs saying
> that the FDW needs to provide the DML pushdown callback functions together
> with existing table-updating functions such as ExecForeignInsert,
> ExecForeignUpdate and ExecForeignDelete.
>
>
I think we should also update the CheckValidResultRel(), because even
though ExecForeignInsert,
ExecForeignUpdate and ExecForeignDelete not present, FDW still can perform
UPDATE/DELETE/INSERT using DML Pushdown APIs. Lets take committer's view on
this.
PFA update patch, which includes changes into postgresPlanDMLPushdown() to
check for join
condition before target columns and also fixed couple of whitespace issues.
Regards
Rushabh Lathia
www.EnterpriseDB.com
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index ef8eab6..c022ff4 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1316,6 +1316,69 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root,
}
/*
+ * deparse remote UPDATE statement
+ *
+ * The statement text is appended to buf, and we also create an integer List
+ * of the columns being retrieved by RETURNING (if any), which is returned
+ * to *retrieved_attrs.
+ */
+void
+deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
+ Index rtindex, Relation rel,
+ List *targetlist,
+ List *targetAttrs,
+ List *remote_conds,
+ List **params_list,
+ List *returningList,
+ List **retrieved_attrs)
+{
+ RelOptInfo *baserel = root->simple_rel_array[rtindex];
+ deparse_expr_cxt context;
+ int nestlevel;
+ bool first;
+ ListCell *lc;
+
+ /* Set up context struct for recursion */
+ context.root = root;
+ context.foreignrel = baserel;
+ context.buf = buf;
+ context.params_list = params_list;
+
+ appendStringInfoString(buf, "UPDATE ");
+ deparseRelation(buf, rel);
+ appendStringInfoString(buf, " SET ");
+
+ /* Make sure any constants in the exprs are printed portably */
+ nestlevel = set_transmission_modes();
+
+ first = true;
+ foreach(lc, targetAttrs)
+ {
+ int attnum = lfirst_int(lc);
+ TargetEntry *tle = get_tle_by_resno(targetlist, attnum);
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ deparseColumnRef(buf, rtindex, attnum, root, false);
+ appendStringInfoString(buf, " = ");
+ deparseExpr((Expr *) tle->expr, &context);
+ }
+
+ reset_transmission_modes(nestlevel);
+
+ if (remote_conds)
+ {
+ appendStringInfo(buf, " WHERE ");
+ appendConditions(remote_conds, &context);
+ }
+
+ deparseReturningList(buf, root, rtindex, rel, false,
+ returningList, retrieved_attrs);
+}
+
+/*
* deparse remote DELETE statement
*
* The statement text is appended to buf, and we also create an integer List
@@ -1338,6 +1401,43 @@ deparseDeleteSql(StringInfo buf, PlannerInfo *root,
}
/*
+ * deparse remote DELETE statement
+ *
+ * The statement text is appended to buf, and we also create an integer List
+ * of the columns being retrieved by RETURNING (if any), which is returned
+ * to *retrieved_attrs.
+ */
+void
+deparsePushedDownDeleteSql(StringInfo buf, PlannerInfo *root,
+ Index rtindex, Relation rel,
+ List *remote_conds,
+ List **params_list,
+ List *returningList,
+ List **retrieved_attrs)
+{
+ RelOptInfo *baserel = root->simple_rel_array[rtindex];
+ deparse_expr_cxt context;
+
+ /* Set up context struct for recursion */
+ context.root = root;
+ context.foreignrel = baserel;
+ context.buf = buf;
+ context.params_list = params_list;
+
+ appendStringInfoString(buf, "DELETE FROM ");
+ deparseRelation(buf, rel);
+
+ if (remote_conds)
+ {
+ appendStringInfo(buf, " WHERE ");
+ appendConditions(remote_conds, &context);
+ }
+
+ deparseReturningList(buf, root, rtindex, rel, false,
+ returningList, retrieved_attrs);
+}
+
+/*
* Add a RETURNING clause, if needed, to an INSERT/UPDATE/DELETE.
*/
static void
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 280c377..007e1a8 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2258,7 +2258,26 @@ INSERT INTO ft2 (c1,c2,c3)
(3 rows)
INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
+EXPLAIN (verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300), c3 = (c3 || '_update3'::text) WHERE ((("C 1" % 10) = 3))
+(3 rows)
+
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
+EXPLAIN (verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 400), c3 = (c3 || '_update7'::text) WHERE ((("C 1" % 10) = 7)) RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+(4 rows)
+
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
------+-----+--------------------+------------------------------+--------------------------+----+------------+-----
@@ -2368,7 +2387,7 @@ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
- FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
+ FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
@@ -2393,16 +2412,14 @@ UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
EXPLAIN (verbose, costs off)
- DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Delete on public.ft2
Output: c1, c4
- Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c4
- -> Foreign Scan on public.ft2
- Output: ctid
- Remote SQL: SELECT ctid FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 5)) FOR UPDATE
-(6 rows)
+ -> Foreign Delete on public.ft2
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 5)) RETURNING "C 1", c4
+(4 rows)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
c1 | c4
@@ -2513,7 +2530,7 @@ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
(103 rows)
EXPLAIN (verbose, costs off)
-DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
+DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
@@ -3378,16 +3395,14 @@ INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
(1 row)
EXPLAIN (verbose, costs off)
-UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
+UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
+ QUERY PLAN
+------------------------------------------------------------------------------------
Update on public.ft2
Output: (tableoid)::regclass
- Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1
- -> Foreign Scan on public.ft2
- Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid
- Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" = 9999)) FOR UPDATE
-(6 rows)
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c3 = 'bar'::text WHERE (("C 1" = 9999))
+(4 rows)
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
tableoid
@@ -3396,16 +3411,14 @@ UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
(1 row)
EXPLAIN (verbose, costs off)
-DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
- QUERY PLAN
-------------------------------------------------------------------------------------
+DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
+ QUERY PLAN
+--------------------------------------------------------------------
Delete on public.ft2
Output: (tableoid)::regclass
- Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
- -> Foreign Scan on public.ft2
- Output: ctid
- Remote SQL: SELECT ctid FROM "S 1"."T 1" WHERE (("C 1" = 9999)) FOR UPDATE
-(6 rows)
+ -> Foreign Delete on public.ft2
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE (("C 1" = 9999))
+(4 rows)
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
tableoid
@@ -3559,7 +3572,7 @@ CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6,
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
ERROR: new row for relation "T 1" violates check constraint "c2positive"
DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo).
-CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
+CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = (- c2) WHERE (("C 1" = 1))
-- Test savepoint/rollback behavior
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
c2 | count
@@ -3718,7 +3731,7 @@ savepoint s3;
update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
ERROR: new row for relation "T 1" violates check constraint "c2positive"
DETAIL: Failing row contains (10, -2, 00010_trig_update_trig_update, 1970-01-11 08:00:00+00, 1970-01-11 00:00:00, 0, 0 , foo).
-CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
+CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = (-2) WHERE ((c2 = 42)) AND (("C 1" = 10))
rollback to savepoint s3;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
c2 | count
@@ -3858,7 +3871,7 @@ CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6,
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
ERROR: new row for relation "T 1" violates check constraint "c2positive"
DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo).
-CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
+CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = (- c2) WHERE (("C 1" = 1))
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
-- But inconsistent check constraints provide inconsistent results
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
@@ -4251,6 +4264,199 @@ NOTICE: NEW: (13,"test triggered !")
(0,27)
(1 row)
+-- cleanup
+DROP TRIGGER trig_row_before ON rem1;
+DROP TRIGGER trig_row_after ON rem1;
+DROP TRIGGER trig_local_before ON loc1;
+-- Test DML pushdown functionality
+-- Test with statement-level triggers
+CREATE TRIGGER trig_stmt_before
+ BEFORE DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------
+ Update on public.rem1
+ -> Foreign Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = ''::text
+(3 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+ QUERY PLAN
+---------------------------------------------
+ Delete on public.rem1
+ -> Foreign Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1
+(3 rows)
+
+DROP TRIGGER trig_stmt_before ON rem1;
+CREATE TRIGGER trig_stmt_after
+ AFTER DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------
+ Update on public.rem1
+ -> Foreign Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = ''::text
+(3 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+ QUERY PLAN
+---------------------------------------------
+ Delete on public.rem1
+ -> Foreign Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1
+(3 rows)
+
+DROP TRIGGER trig_stmt_after ON rem1;
+-- Test with row-level ON INSERT triggers
+CREATE TRIGGER trig_row_before_insert
+BEFORE INSERT ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------
+ Update on public.rem1
+ -> Foreign Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = ''::text
+(3 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+ QUERY PLAN
+---------------------------------------------
+ Delete on public.rem1
+ -> Foreign Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1
+(3 rows)
+
+DROP TRIGGER trig_row_before_insert ON rem1;
+CREATE TRIGGER trig_row_after_insert
+AFTER INSERT ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------
+ Update on public.rem1
+ -> Foreign Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = ''::text
+(3 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+ QUERY PLAN
+---------------------------------------------
+ Delete on public.rem1
+ -> Foreign Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1
+(3 rows)
+
+DROP TRIGGER trig_row_after_insert ON rem1;
+-- Test with row-level ON UPDATE triggers
+CREATE TRIGGER trig_row_before_update
+BEFORE UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can't be pushed down
+ QUERY PLAN
+---------------------------------------------------------------------
+ Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1
+ -> Foreign Scan on public.rem1
+ Output: f1, ''::text, ctid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+(5 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+ QUERY PLAN
+---------------------------------------------
+ Delete on public.rem1
+ -> Foreign Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1
+(3 rows)
+
+DROP TRIGGER trig_row_before_update ON rem1;
+CREATE TRIGGER trig_row_after_update
+AFTER UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can't be pushed down
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2
+ -> Foreign Scan on public.rem1
+ Output: f1, ''::text, ctid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+(5 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+ QUERY PLAN
+---------------------------------------------
+ Delete on public.rem1
+ -> Foreign Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1
+(3 rows)
+
+DROP TRIGGER trig_row_after_update ON rem1;
+-- Test with row-level ON DELETE triggers
+CREATE TRIGGER trig_row_before_delete
+BEFORE DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------
+ Update on public.rem1
+ -> Foreign Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = ''::text
+(3 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can't be pushed down
+ QUERY PLAN
+---------------------------------------------------------------------
+ Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1
+ -> Foreign Scan on public.rem1
+ Output: ctid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+(5 rows)
+
+DROP TRIGGER trig_row_before_delete ON rem1;
+CREATE TRIGGER trig_row_after_delete
+AFTER DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+ QUERY PLAN
+----------------------------------------------------------
+ Update on public.rem1
+ -> Foreign Update on public.rem1
+ Remote SQL: UPDATE public.loc1 SET f2 = ''::text
+(3 rows)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can't be pushed down
+ QUERY PLAN
+------------------------------------------------------------------------
+ Delete on public.rem1
+ Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 RETURNING f1, f2
+ -> Foreign Scan on public.rem1
+ Output: ctid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+(5 rows)
+
+DROP TRIGGER trig_row_after_delete ON rem1;
-- ===================================================================
-- test inheritance features
-- ===================================================================
@@ -4720,6 +4926,56 @@ fetch from c;
update bar set f2 = null where current of c;
ERROR: WHERE CURRENT OF is not supported for this table type
rollback;
+explain (verbose, costs off)
+delete from foo where f1 < 5 returning *;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Delete on public.foo
+ Output: foo.f1, foo.f2
+ Delete on public.foo
+ Foreign Delete on public.foo2
+ -> Index Scan using i_foo_f1 on public.foo
+ Output: foo.ctid
+ Index Cond: (foo.f1 < 5)
+ -> Foreign Delete on public.foo2
+ Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2
+(9 rows)
+
+delete from foo where f1 < 5 returning *;
+ f1 | f2
+----+----
+ 1 | 1
+ 3 | 3
+ 0 | 0
+ 2 | 2
+ 4 | 4
+(5 rows)
+
+explain (verbose, costs off)
+update bar set f2 = f2 + 100 returning *;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Update on public.bar
+ Output: bar.f1, bar.f2
+ Update on public.bar
+ Foreign Update on public.bar2
+ -> Seq Scan on public.bar
+ Output: bar.f1, (bar.f2 + 100), bar.ctid
+ -> Foreign Update on public.bar2
+ Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2
+(8 rows)
+
+update bar set f2 = f2 + 100 returning *;
+ f1 | f2
+----+-----
+ 1 | 311
+ 2 | 322
+ 6 | 266
+ 3 | 333
+ 4 | 344
+ 7 | 277
+(6 rows)
+
drop table foo cascade;
NOTICE: drop cascades to foreign table foo2
drop table bar cascade;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 465f43c..f88d796 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -61,6 +61,8 @@ enum FdwScanPrivateIndex
{
/* SQL statement to execute remotely (as a String node) */
FdwScanPrivateSelectSql,
+ /* List of restriction clauses that can be executed remotely */
+ FdwScanPrivateRemoteConds,
/* Integer list of attribute numbers retrieved by the SELECT */
FdwScanPrivateRetrievedAttrs,
/* Integer representing the desired fetch_size */
@@ -98,6 +100,28 @@ enum FdwModifyPrivateIndex
};
/*
+ * Similarly, this enum describes what's kept in the fdw_private list for
+ * a ForeignScan node that has pushed down an UPDATE/DELETE to the remote
+ * server. We store:
+ *
+ * 1) UPDATE/DELETE statement text to be sent to the remote server
+ * 2) Boolean flag showing if the remote query has a RETURNING clause
+ * 3) Integer list of attribute numbers retrieved by RETURNING, if any
+ * 4) Boolean flag showing if we set the command es_processed
+ */
+enum FdwDmlPushdownPrivateIndex
+{
+ /* SQL statement to execute remotely (as a String node) */
+ FdwDmlPushdownPrivateUpdateSql,
+ /* has-returning flag (as an integer Value node) */
+ FdwDmlPushdownPrivateHasReturning,
+ /* Integer list of attribute numbers retrieved by RETURNING */
+ FdwDmlPushdownPrivateRetrievedAttrs,
+ /* set-processed flag (as an integer Value node) */
+ FdwDmlPushdownPrivateSetProcessed
+};
+
+/*
* Execution state of a foreign scan using postgres_fdw.
*/
typedef struct PgFdwScanState
@@ -164,6 +188,37 @@ typedef struct PgFdwModifyState
} PgFdwModifyState;
/*
+ * Execution state of a foreign scan that has pushed down a foreign table
+ * modification to the remote server
+ */
+typedef struct PgFdwDmlPushdownState
+{
+ Relation rel; /* relcache entry for the foreign table */
+ AttInMetadata *attinmeta; /* attribute datatype conversion metadata */
+
+ /* extracted fdw_private data */
+ char *query; /* text of UPDATE/DELETE command */
+ bool has_returning; /* is there a RETURNING clause? */
+ List *retrieved_attrs; /* attr numbers retrieved by RETURNING */
+ bool set_processed; /* do we set the command es_processed? */
+
+ /* for remote query execution */
+ PGconn *conn; /* connection for the update */
+ int numParams; /* number of parameters passed to query */
+ FmgrInfo *param_flinfo; /* output conversion functions for them */
+ List *param_exprs; /* executable expressions for param values */
+ const char **param_values; /* textual values of query parameters */
+
+ /* for storing result tuples */
+ PGresult *result; /* result for query */
+ int num_tuples; /* # of result tuples */
+ int next_tuple; /* index of next one to return */
+
+ /* working memory context */
+ MemoryContext temp_cxt; /* context for per-tuple temporary data */
+} PgFdwDmlPushdownState;
+
+/*
* Workspace for analyzing a foreign table.
*/
typedef struct PgFdwAnalyzeState
@@ -263,6 +318,13 @@ static TupleTableSlot *postgresExecForeignDelete(EState *estate,
static void postgresEndForeignModify(EState *estate,
ResultRelInfo *resultRelInfo);
static int postgresIsForeignRelUpdatable(Relation rel);
+static bool postgresPlanDMLPushdown(PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index);
+static void postgresBeginDMLPushdown(ForeignScanState *node, int eflags);
+static TupleTableSlot *postgresIterateDMLPushdown(ForeignScanState *node);
+static void postgresEndDMLPushdown(ForeignScanState *node);
static void postgresExplainForeignScan(ForeignScanState *node,
ExplainState *es);
static void postgresExplainForeignModify(ModifyTableState *mtstate,
@@ -270,6 +332,8 @@ static void postgresExplainForeignModify(ModifyTableState *mtstate,
List *fdw_private,
int subplan_index,
ExplainState *es);
+static void postgresExplainDMLPushdown(ForeignScanState *node,
+ ExplainState *es);
static bool postgresAnalyzeForeignTable(Relation relation,
AcquireSampleRowsFunc *func,
BlockNumber *totalpages);
@@ -314,6 +378,18 @@ static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
+static void execute_dml_stmt(ForeignScanState *node);
+static TupleTableSlot *get_returning_data(ForeignScanState *node);
+static void prepare_query_params(PlanState *node,
+ List *fdw_exprs,
+ int numParams,
+ FmgrInfo **param_flinfo,
+ List **param_exprs,
+ const char ***param_values);
+static void process_query_params(ExprContext *econtext,
+ FmgrInfo *param_flinfo,
+ List *param_exprs,
+ const char **param_values);
static int postgresAcquireSampleRowsFunc(Relation relation, int elevel,
HeapTuple *rows, int targrows,
double *totalrows,
@@ -360,12 +436,17 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
routine->ExecForeignDelete = postgresExecForeignDelete;
routine->EndForeignModify = postgresEndForeignModify;
routine->IsForeignRelUpdatable = postgresIsForeignRelUpdatable;
+ routine->PlanDMLPushdown = postgresPlanDMLPushdown;
+ routine->BeginDMLPushdown = postgresBeginDMLPushdown;
+ routine->IterateDMLPushdown = postgresIterateDMLPushdown;
+ routine->EndDMLPushdown = postgresEndDMLPushdown;
/* Function for EvalPlanQual rechecks */
routine->RecheckForeignScan = postgresRecheckForeignScan;
/* Support functions for EXPLAIN */
routine->ExplainForeignScan = postgresExplainForeignScan;
routine->ExplainForeignModify = postgresExplainForeignModify;
+ routine->ExplainDMLPushdown = postgresExplainDMLPushdown;
/* Support functions for ANALYZE */
routine->AnalyzeForeignTable = postgresAnalyzeForeignTable;
@@ -1133,7 +1214,8 @@ postgresGetForeignPlan(PlannerInfo *root,
* Build the fdw_private list that will be available to the executor.
* Items in the list must match order in enum FdwScanPrivateIndex.
*/
- fdw_private = list_make4(makeString(sql.data),
+ fdw_private = list_make5(makeString(sql.data),
+ remote_conds,
retrieved_attrs,
makeInteger(fpinfo->fetch_size),
makeInteger(foreignrel->umid));
@@ -1170,8 +1252,6 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
PgFdwScanState *fsstate;
UserMapping *user;
int numParams;
- int i;
- ListCell *lc;
/*
* Do nothing in EXPLAIN (no ANALYZE) case. node->fdw_state stays NULL.
@@ -1258,42 +1338,18 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
- /* Prepare for output conversion of parameters used in remote query. */
- numParams = list_length(fsplan->fdw_exprs);
- fsstate->numParams = numParams;
- fsstate->param_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * numParams);
-
- i = 0;
- foreach(lc, fsplan->fdw_exprs)
- {
- Node *param_expr = (Node *) lfirst(lc);
- Oid typefnoid;
- bool isvarlena;
-
- getTypeOutputInfo(exprType(param_expr), &typefnoid, &isvarlena);
- fmgr_info(typefnoid, &fsstate->param_flinfo[i]);
- i++;
- }
-
- /*
- * Prepare remote-parameter expressions for evaluation. (Note: in
- * practice, we expect that all these expressions will be just Params, so
- * we could possibly do something more efficient than using the full
- * expression-eval machinery for this. But probably there would be little
- * benefit, and it'd require postgres_fdw to know more than is desirable
- * about Param evaluation.)
- */
- fsstate->param_exprs = (List *)
- ExecInitExpr((Expr *) fsplan->fdw_exprs,
- (PlanState *) node);
-
/*
- * Allocate buffer for text form of query parameters, if any.
+ * Prepare for processing of parameters used in remote query, if any.
*/
+ numParams = list_length(fsplan->fdw_exprs);
+ fsstate->numParams = numParams;
if (numParams > 0)
- fsstate->param_values = (const char **) palloc0(numParams * sizeof(char *));
- else
- fsstate->param_values = NULL;
+ prepare_query_params((PlanState *) node,
+ fsplan->fdw_exprs,
+ numParams,
+ &fsstate->param_flinfo,
+ &fsstate->param_exprs,
+ &fsstate->param_values);
}
/*
@@ -1458,13 +1514,6 @@ postgresAddForeignUpdateTargets(Query *parsetree,
/*
* postgresPlanForeignModify
* Plan an insert/update/delete operation on a foreign table
- *
- * Note: currently, the plan tree generated for UPDATE/DELETE will always
- * include a ForeignScan that retrieves ctids (using SELECT FOR UPDATE)
- * and then the ModifyTable node will have to execute individual remote
- * UPDATE/DELETE commands. If there are no local conditions or joins
- * needed, it'd be better to let the scan node do UPDATE/DELETE RETURNING
- * and then do nothing at ModifyTable. Room for future optimization ...
*/
static List *
postgresPlanForeignModify(PlannerInfo *root,
@@ -2003,6 +2052,314 @@ postgresRecheckForeignScan(ForeignScanState *node, TupleTableSlot *slot)
}
/*
+ * postgresPlanDMLPushdown
+ * Consider pushing down a foreign table modification to the remote server
+ *
+ * Decide whether the table modification is safe to push down to the remote end,
+ * and if so, modify subplan so as to do that.
+ */
+static bool
+postgresPlanDMLPushdown(PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index)
+{
+ CmdType operation = plan->operation;
+ Plan *subplan = (Plan *) list_nth(plan->plans, subplan_index);
+ RangeTblEntry *rte = planner_rt_fetch(resultRelation, root);
+ Relation rel;
+ StringInfoData sql;
+ ForeignScan *fscan;
+ List *targetAttrs = NIL;
+ List *remote_conds;
+ List *params_list = NIL;
+ List *returningList = NIL;
+ List *retrieved_attrs = NIL;
+
+ /*
+ * Decide whether the table modification is pushdown-safe.
+ */
+
+ /*
+ * 1. The table modification must be an UPDATE or DELETE.
+ */
+ if (operation != CMD_UPDATE && operation != CMD_DELETE)
+ return false;
+
+ /*
+ * 2. It's unsafe to push down the command if there are any local joins
+ * needed.
+ */
+ if (!IsA(subplan, ForeignScan))
+ return false;
+
+ /*
+ * 3. It's unsafe to push down the command if there are any quals that
+ * can't be evaluated remotely.
+ */
+ if (subplan->qual != NIL)
+ return false;
+
+ /*
+ * 4. We can't push down an UPDATE or DELETE on a foreign join for now.
+ */
+ fscan = (ForeignScan *) subplan;
+ if (fscan->scan.scanrelid == 0)
+ return false;
+
+ /*
+ * 5. We can't push down an UPDATE, if any expressions to assign to the
+ * target columns are unsafe to evaluate on the remote end.
+ */
+ if (operation == CMD_UPDATE)
+ {
+ RelOptInfo *baserel = root->simple_rel_array[resultRelation];
+ int col;
+
+ /*
+ * We transmit only columns that were explicitly targets of the UPDATE,
+ * so as to avoid unnecessary data transmission.
+ */
+ col = -1;
+ while ((col = bms_next_member(rte->updatedCols, col)) >= 0)
+ {
+ /* bit numbers are offset by FirstLowInvalidHeapAttributeNumber */
+ AttrNumber attno = col + FirstLowInvalidHeapAttributeNumber;
+ TargetEntry *tle;
+
+ if (attno <= InvalidAttrNumber) /* shouldn't happen */
+ elog(ERROR, "system-column update is not supported");
+
+ tle = get_tle_by_resno(subplan->targetlist, attno);
+
+ if (!is_foreign_expr(root, baserel, (Expr *) tle->expr))
+ return false;
+
+ targetAttrs = lappend_int(targetAttrs, attno);
+ }
+ }
+
+ /*
+ * Ok, modify subplan so as to push down the command to the remote server.
+ */
+ initStringInfo(&sql);
+
+ /*
+ * Core code already has some lock on each rel being planned, so we can
+ * use NoLock here.
+ */
+ rel = heap_open(rte->relid, NoLock);
+
+ /*
+ * Extract the baserestrictinfo clauses that can be evaluated remotely.
+ */
+ remote_conds = (List *) list_nth(fscan->fdw_private,
+ FdwScanPrivateRemoteConds);
+
+ /*
+ * Extract the relevant RETURNING list if any.
+ */
+ if (plan->returningLists)
+ returningList = (List *) list_nth(plan->returningLists, subplan_index);
+
+ /*
+ * Construct the SQL command string.
+ */
+ switch (operation)
+ {
+ case CMD_UPDATE:
+ deparsePushedDownUpdateSql(&sql, root, resultRelation, rel,
+ ((Plan *) fscan)->targetlist,
+ targetAttrs,
+ remote_conds, ¶ms_list,
+ returningList, &retrieved_attrs);
+ break;
+ case CMD_DELETE:
+ deparsePushedDownDeleteSql(&sql, root, resultRelation, rel,
+ remote_conds, ¶ms_list,
+ returningList, &retrieved_attrs);
+ break;
+ default:
+ elog(ERROR, "unexpected operation: %d", (int) operation);
+ break;
+ }
+
+ /*
+ * Update the operation info.
+ */
+ fscan->operation = operation;
+
+ /*
+ * Update the fdw_exprs list that will be available to the executor.
+ */
+ fscan->fdw_exprs = params_list;
+
+ /*
+ * Update the fdw_private list that will be available to the executor.
+ * Items in the list must match enum FdwDmlPushdownPrivateIndex, above.
+ */
+ fscan->fdw_private = list_make4(makeString(sql.data),
+ makeInteger((retrieved_attrs != NIL)),
+ retrieved_attrs,
+ makeInteger(plan->canSetTag));
+
+ heap_close(rel, NoLock);
+ return true;
+}
+
+/*
+ * postgresBeginDMLPushdown
+ * Initiate pushing down a foreign table modification to the remote server
+ */
+static void
+postgresBeginDMLPushdown(ForeignScanState *node, int eflags)
+{
+ ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
+ EState *estate = node->ss.ps.state;
+ PgFdwDmlPushdownState *dpstate;
+ RangeTblEntry *rte;
+ Oid userid;
+ ForeignTable *table;
+ UserMapping *user;
+ int numParams;
+
+ /*
+ * Do nothing in EXPLAIN (no ANALYZE) case. node->fdw_state stays NULL.
+ */
+ if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
+ return;
+
+ /*
+ * We'll save private state in node->fdw_state.
+ */
+ dpstate = (PgFdwDmlPushdownState *) palloc0(sizeof(PgFdwDmlPushdownState));
+ node->fdw_state = (void *) dpstate;
+
+ /*
+ * Identify which user to do the remote access as. This should match what
+ * ExecCheckRTEPerms() does.
+ */
+ rte = rt_fetch(fsplan->scan.scanrelid, estate->es_range_table);
+ userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
+
+ /* Get info about foreign table. */
+ dpstate->rel = node->ss.ss_currentRelation;
+ table = GetForeignTable(RelationGetRelid(dpstate->rel));
+ user = GetUserMapping(userid, table->serverid);
+
+ /*
+ * Get connection to the foreign server. Connection manager will
+ * establish new connection if necessary.
+ */
+ dpstate->conn = GetConnection(user, false);
+
+ /* Initialize state variable */
+ dpstate->num_tuples = -1; /* -1 means not set yet */
+
+ /* Get private info created by planner functions. */
+ dpstate->query = strVal(list_nth(fsplan->fdw_private,
+ FdwDmlPushdownPrivateUpdateSql));
+ dpstate->has_returning = intVal(list_nth(fsplan->fdw_private,
+ FdwDmlPushdownPrivateHasReturning));
+ dpstate->retrieved_attrs = (List *) list_nth(fsplan->fdw_private,
+ FdwDmlPushdownPrivateRetrievedAttrs);
+ dpstate->set_processed = intVal(list_nth(fsplan->fdw_private,
+ FdwDmlPushdownPrivateSetProcessed));
+
+ /* Create context for per-tuple temp workspace. */
+ dpstate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt,
+ "postgres_fdw temporary data",
+ ALLOCSET_SMALL_MINSIZE,
+ ALLOCSET_SMALL_INITSIZE,
+ ALLOCSET_SMALL_MAXSIZE);
+
+ /* Prepare for input conversion of RETURNING results. */
+ if (dpstate->has_returning)
+ dpstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(dpstate->rel));
+
+ /*
+ * Prepare for processing of parameters used in remote query, if any.
+ */
+ numParams = list_length(fsplan->fdw_exprs);
+ dpstate->numParams = numParams;
+ if (numParams > 0)
+ prepare_query_params((PlanState *) node,
+ fsplan->fdw_exprs,
+ numParams,
+ &dpstate->param_flinfo,
+ &dpstate->param_exprs,
+ &dpstate->param_values);
+}
+
+/*
+ * postgresIterateDMLPushdown
+ * Execute pushing down a foreign table modification to the remote server
+ */
+static TupleTableSlot *
+postgresIterateDMLPushdown(ForeignScanState *node)
+{
+ PgFdwDmlPushdownState *dpstate = (PgFdwDmlPushdownState *) node->fdw_state;
+ EState *estate = node->ss.ps.state;
+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
+
+ /*
+ * If this is the first call after Begin, execute the statement.
+ */
+ if (dpstate->num_tuples == -1)
+ execute_dml_stmt(node);
+
+ /*
+ * If the local query doesn't specify RETURNING, just clear tuple slot.
+ */
+ if (!resultRelInfo->ri_projectReturning)
+ {
+ TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+ Instrumentation *instr = node->ss.ps.instrument;
+
+ Assert(!dpstate->has_returning);
+
+ /* Increment the command es_processed count if necessary. */
+ if (dpstate->set_processed)
+ estate->es_processed += dpstate->num_tuples;
+
+ /* Increment the tuple count for EXPLAIN ANALYZE if necessary. */
+ if (instr)
+ instr->tuplecount += dpstate->num_tuples;
+
+ return ExecClearTuple(slot);
+ }
+
+ /*
+ * Get the next RETURNING tuple.
+ */
+ return get_returning_data(node);
+}
+
+/*
+ * postgresEndDMLPushdown
+ * Finish pushing down a foreign table modification to the remote server
+ */
+static void
+postgresEndDMLPushdown(ForeignScanState *node)
+{
+ PgFdwDmlPushdownState *dpstate = (PgFdwDmlPushdownState *) node->fdw_state;
+
+ /* if dpstate is NULL, we are in EXPLAIN; nothing to do */
+ if (dpstate == NULL)
+ return;
+
+ /* Release PGresult */
+ if (dpstate->result)
+ PQclear(dpstate->result);
+
+ /* Release remote connection */
+ ReleaseConnection(dpstate->conn);
+ dpstate->conn = NULL;
+
+ /* MemoryContext will be deleted automatically. */
+}
+
+/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
@@ -2055,6 +2412,25 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
}
}
+/*
+ * postgresExplainDMLPushdown
+ * Produce extra output for EXPLAIN of a ForeignScan on a foreign table
+ * that has pushed down an UPDATE/DELETE to the remote server
+ */
+static void
+postgresExplainDMLPushdown(ForeignScanState *node, ExplainState *es)
+{
+ List *fdw_private;
+ char *sql;
+
+ if (es->verbose)
+ {
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDmlPushdownPrivateUpdateSql));
+ ExplainPropertyText("Remote SQL", sql, es);
+ }
+}
+
/*
* estimate_path_cost_size
@@ -2413,38 +2789,14 @@ create_cursor(ForeignScanState *node)
*/
if (numParams > 0)
{
- int nestlevel;
MemoryContext oldcontext;
- int i;
- ListCell *lc;
oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
- nestlevel = set_transmission_modes();
-
- i = 0;
- foreach(lc, fsstate->param_exprs)
- {
- ExprState *expr_state = (ExprState *) lfirst(lc);
- Datum expr_value;
- bool isNull;
-
- /* Evaluate the parameter expression */
- expr_value = ExecEvalExpr(expr_state, econtext, &isNull, NULL);
-
- /*
- * Get string representation of each parameter value by invoking
- * type-specific output function, unless the value is null.
- */
- if (isNull)
- values[i] = NULL;
- else
- values[i] = OutputFunctionCall(&fsstate->param_flinfo[i],
- expr_value);
- i++;
- }
-
- reset_transmission_modes(nestlevel);
+ process_query_params(econtext,
+ fsstate->param_flinfo,
+ fsstate->param_exprs,
+ values);
MemoryContextSwitchTo(oldcontext);
}
@@ -2765,6 +3117,197 @@ store_returning_result(PgFdwModifyState *fmstate,
}
/*
+ * Execute a pushed-down UPDATE/DELETE statement.
+ */
+static void
+execute_dml_stmt(ForeignScanState *node)
+{
+ PgFdwDmlPushdownState *dpstate = (PgFdwDmlPushdownState *) node->fdw_state;
+ ExprContext *econtext = node->ss.ps.ps_ExprContext;
+ int numParams = dpstate->numParams;
+ const char **values = dpstate->param_values;
+
+ /*
+ * Construct array of query parameter values in text format.
+ */
+ if (numParams > 0)
+ process_query_params(econtext,
+ dpstate->param_flinfo,
+ dpstate->param_exprs,
+ values);
+
+ /*
+ * Notice that we pass NULL for paramTypes, thus forcing the remote server
+ * to infer types for all parameters. Since we explicitly cast every
+ * parameter (see deparse.c), the "inference" is trivial and will produce
+ * the desired result. This allows us to avoid assuming that the remote
+ * server has the same OIDs we do for the parameters' types.
+ *
+ * We don't use a PG_TRY block here, so be careful not to throw error
+ * without releasing the PGresult.
+ */
+ dpstate->result = PQexecParams(dpstate->conn, dpstate->query,
+ numParams, NULL, values, NULL, NULL, 0);
+ if (PQresultStatus(dpstate->result) !=
+ (dpstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK))
+ pgfdw_report_error(ERROR, dpstate->result, dpstate->conn, true,
+ dpstate->query);
+
+ /* Get the number of rows affected. */
+ if (dpstate->has_returning)
+ dpstate->num_tuples = PQntuples(dpstate->result);
+ else
+ dpstate->num_tuples = atoi(PQcmdTuples(dpstate->result));
+}
+
+/*
+ * Get the result of a RETURNING clause.
+ */
+static TupleTableSlot *
+get_returning_data(ForeignScanState *node)
+{
+ PgFdwDmlPushdownState *dpstate = (PgFdwDmlPushdownState *) node->fdw_state;
+ EState *estate = node->ss.ps.state;
+ ResultRelInfo *resultRelInfo = estate->es_result_relation_info;
+ TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
+
+ Assert(resultRelInfo->ri_projectReturning);
+
+ /* If we didn't get any tuples, must be end of data. */
+ if (dpstate->next_tuple >= dpstate->num_tuples)
+ return ExecClearTuple(slot);
+
+ /* Increment the command es_processed count if necessary. */
+ if (dpstate->set_processed)
+ estate->es_processed += 1;
+
+ /*
+ * Store a RETURNING tuple. If has_returning is false, just emit a dummy
+ * tuple. (We have has_returning=false if the local query is of the form
+ * UPDATE/DELETE .. RETURNING 1 for example.)
+ */
+ if (!dpstate->has_returning)
+ ExecStoreAllNullTuple(slot);
+ else
+ {
+ /*
+ * On error, be sure to release the PGresult on the way out. Callers
+ * do not have PG_TRY blocks to ensure this happens.
+ */
+ PG_TRY();
+ {
+ HeapTuple newtup;
+
+ newtup = make_tuple_from_result_row(dpstate->result,
+ dpstate->next_tuple,
+ dpstate->rel,
+ dpstate->attinmeta,
+ dpstate->retrieved_attrs,
+ NULL,
+ dpstate->temp_cxt);
+ ExecStoreTuple(newtup, slot, InvalidBuffer, false);
+ }
+ PG_CATCH();
+ {
+ if (dpstate->result)
+ PQclear(dpstate->result);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ }
+ dpstate->next_tuple++;
+
+ /* Make slot available for evaluation of the local query RETURNING list. */
+ resultRelInfo->ri_projectReturning->pi_exprContext->ecxt_scantuple = slot;
+
+ return slot;
+}
+
+/*
+ * Prepare for processing of parameters used in remote query.
+ */
+static void
+prepare_query_params(PlanState *node,
+ List *fdw_exprs,
+ int numParams,
+ FmgrInfo **param_flinfo,
+ List **param_exprs,
+ const char ***param_values)
+{
+ int i;
+ ListCell *lc;
+
+ Assert(numParams > 0);
+
+ /* Prepare for output conversion of parameters used in remote query. */
+ *param_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * numParams);
+
+ i = 0;
+ foreach(lc, fdw_exprs)
+ {
+ Node *param_expr = (Node *) lfirst(lc);
+ Oid typefnoid;
+ bool isvarlena;
+
+ getTypeOutputInfo(exprType(param_expr), &typefnoid, &isvarlena);
+ fmgr_info(typefnoid, &(*param_flinfo)[i]);
+ i++;
+ }
+
+ /*
+ * Prepare remote-parameter expressions for evaluation. (Note: in
+ * practice, we expect that all these expressions will be just Params, so
+ * we could possibly do something more efficient than using the full
+ * expression-eval machinery for this. But probably there would be little
+ * benefit, and it'd require postgres_fdw to know more than is desirable
+ * about Param evaluation.)
+ */
+ *param_exprs = (List *) ExecInitExpr((Expr *) fdw_exprs, node);
+
+ /* Allocate buffer for text form of query parameters. */
+ *param_values = (const char **) palloc0(numParams * sizeof(char *));
+}
+
+/*
+ * Construct array of query parameter values in text format.
+ */
+static void
+process_query_params(ExprContext *econtext,
+ FmgrInfo *param_flinfo,
+ List *param_exprs,
+ const char **param_values)
+{
+ int nestlevel;
+ int i;
+ ListCell *lc;
+
+ nestlevel = set_transmission_modes();
+
+ i = 0;
+ foreach(lc, param_exprs)
+ {
+ ExprState *expr_state = (ExprState *) lfirst(lc);
+ Datum expr_value;
+ bool isNull;
+
+ /* Evaluate the parameter expression */
+ expr_value = ExecEvalExpr(expr_state, econtext, &isNull, NULL);
+
+ /*
+ * Get string representation of each parameter value by invoking
+ * type-specific output function, unless the value is null.
+ */
+ if (isNull)
+ param_values[i] = NULL;
+ else
+ param_values[i] = OutputFunctionCall(¶m_flinfo[i], expr_value);
+ i++;
+ }
+
+ reset_transmission_modes(nestlevel);
+}
+
+/*
* postgresAnalyzeForeignTable
* Test whether analyzing this foreign table is supported
*/
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 4c731be..9fd2415 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -130,10 +130,24 @@ extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetAttrs, List *returningList,
List **retrieved_attrs);
+extern void deparsePushedDownUpdateSql(StringInfo buf, PlannerInfo *root,
+ Index rtindex, Relation rel,
+ List *targetlist,
+ List *targetAttrs,
+ List *remote_conds,
+ List **params_list,
+ List *returningList,
+ List **retrieved_attrs);
extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *returningList,
List **retrieved_attrs);
+extern void deparsePushedDownDeleteSql(StringInfo buf, PlannerInfo *root,
+ Index rtindex, Relation rel,
+ List *remote_conds,
+ List **params_list,
+ List *returningList,
+ List **retrieved_attrs);
extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
List **retrieved_attrs);
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 885a5fb..5a65ea5 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -599,28 +599,32 @@ INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3)
VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
+EXPLAIN (verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
+EXPLAIN (verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
- FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
+ FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
EXPLAIN (verbose, costs off)
- DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
+ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
EXPLAIN (verbose, costs off)
-DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
+DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
EXPLAIN (verbose, costs off)
-UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
+UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
EXPLAIN (verbose, costs off)
-DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
+DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
-- Test that trigger on remote table works as expected
@@ -937,6 +941,90 @@ UPDATE rem1 SET f2 = 'testo';
-- Test returning a system attribute
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
+-- cleanup
+DROP TRIGGER trig_row_before ON rem1;
+DROP TRIGGER trig_row_after ON rem1;
+DROP TRIGGER trig_local_before ON loc1;
+
+
+-- Test DML pushdown functionality
+
+-- Test with statement-level triggers
+CREATE TRIGGER trig_stmt_before
+ BEFORE DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+DROP TRIGGER trig_stmt_before ON rem1;
+
+CREATE TRIGGER trig_stmt_after
+ AFTER DELETE OR INSERT OR UPDATE ON rem1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+DROP TRIGGER trig_stmt_after ON rem1;
+
+-- Test with row-level ON INSERT triggers
+CREATE TRIGGER trig_row_before_insert
+BEFORE INSERT ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+DROP TRIGGER trig_row_before_insert ON rem1;
+
+CREATE TRIGGER trig_row_after_insert
+AFTER INSERT ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+DROP TRIGGER trig_row_after_insert ON rem1;
+
+-- Test with row-level ON UPDATE triggers
+CREATE TRIGGER trig_row_before_update
+BEFORE UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can't be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+DROP TRIGGER trig_row_before_update ON rem1;
+
+CREATE TRIGGER trig_row_after_update
+AFTER UPDATE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can't be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can be pushed down
+DROP TRIGGER trig_row_after_update ON rem1;
+
+-- Test with row-level ON DELETE triggers
+CREATE TRIGGER trig_row_before_delete
+BEFORE DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can't be pushed down
+DROP TRIGGER trig_row_before_delete ON rem1;
+
+CREATE TRIGGER trig_row_after_delete
+AFTER DELETE ON rem1
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+EXPLAIN (verbose, costs off)
+UPDATE rem1 set f2 = ''; -- can be pushed down
+EXPLAIN (verbose, costs off)
+DELETE FROM rem1; -- can't be pushed down
+DROP TRIGGER trig_row_after_delete ON rem1;
+
-- ===================================================================
-- test inheritance features
-- ===================================================================
@@ -1068,6 +1156,13 @@ fetch from c;
update bar set f2 = null where current of c;
rollback;
+explain (verbose, costs off)
+delete from foo where f1 < 5 returning *;
+delete from foo where f1 < 5 returning *;
+explain (verbose, costs off)
+update bar set f2 = f2 + 100 returning *;
+update bar set f2 = f2 + 100 returning *;
+
drop table foo cascade;
drop table bar cascade;
drop table loct1;
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index c24ddfc..087948f 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -678,6 +678,151 @@ IsForeignRelUpdatable (Relation rel);
updatability for display in the <literal>information_schema</> views.)
</para>
+ <para>
+ If an FDW supports optimizing foreign table updates, it still needs to
+ provide <function>PlanDMLPushdown</>, <function>BeginDMLPushdown</>,
+ <function>IterateDMLPushdown</> and <function>EndDMLPushdown</>
+ described below.
+ </para>
+
+ <para>
+<programlisting>
+bool
+PlanDMLPushdown (PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index);
+</programlisting>
+
+ Decide whether it is safe to execute a foreign table update directly
+ on the remote server. If so, return <literal>true</> after performing
+ planning actions needed for that. Otherwise, return <literal>false</>.
+ This optional function is called during query planning.
+ If this function succeeds, <function>BeginDMLPushdown</>,
+ <function>IterateDMLPushdown</> and <function>EndDMLPushdown</> will be
+ called at the execution stage, instead. Otherwise, the table update
+ will be executed using the table-updating functions described above.
+ The parameters are the same as for <function>PlanForeignModify</>.
+ </para>
+
+ <para>
+ To execute the table update directly on the remote server, this function
+ must rewrite the target subplan with a <structname>ForeignScan</> plan
+ node that executes the table update directly on the remote server. The
+ <structfield>operation</> field of the <structname>ForeignScan</> must
+ be set to the <literal>CmdType</> enumeration appropriately; that is,
+ <literal>CMD_UPDATE</> for <command>UPDATE</>,
+ <literal>CMD_INSERT</> for <command>INSERT</>, and
+ <literal>CMD_DELETE</> for <command>DELETE</>.
+ </para>
+
+ <para>
+ See <xref linkend="fdw-planning"> for additional information.
+ </para>
+
+ <para>
+ If the <function>PlanDMLPushdown</> pointer is set to
+ <literal>NULL</>, no attempts to execute the table update directly on
+ the remote server are taken.
+ </para>
+
+ <para>
+<programlisting>
+void
+BeginDMLPushdown (ForeignScanState *node,
+ int eflags);
+</programlisting>
+
+ Begin executing a foreign table update directly on the remote server.
+ This is called during executor startup. It should perform any
+ initialization needed prior to the actual table update (that should be
+ done upon the first call to <function>IterateDMLPushdown</>).
+ The <structname>ForeignScanState</> node has already been created, but
+ its <structfield>fdw_state</> field is still NULL. Information about
+ the table to update is accessible through the
+ <structname>ForeignScanState</> node (in particular, from the underlying
+ <structname>ForeignScan</> plan node, which contains any FDW-private
+ information provided by <function>PlanDMLPushdown</>).
+ <literal>eflags</> contains flag bits describing the executor's
+ operating mode for this plan node.
+ </para>
+
+ <para>
+ Note that when <literal>(eflags & EXEC_FLAG_EXPLAIN_ONLY)</> is
+ true, this function should not perform any externally-visible actions;
+ it should only do the minimum required to make the node state valid
+ for <function>ExplainDMLPushdown</> and <function>EndDMLPushdown</>.
+ </para>
+
+ <para>
+ If the <function>BeginDMLPushdown</> pointer is set to
+ <literal>NULL</>, attempts to execute the table update directly on
+ the remote server will fail with an error message.
+ </para>
+
+ <para>
+<programlisting>
+TupleTableSlot *
+IterateDMLPushdown (ForeignScanState *node);
+</programlisting>
+
+ When the <command>INSERT</>, <command>UPDATE</> or <command>DELETE</>
+ query doesn't have a <literal>RETURNING</> clause, just return NULL
+ after the actual table update directly executed on the remote server.
+ When the query has the clause, fetch one result containing the data
+ needed for the <literal>RETURNING</> calculation, returning it in a
+ tuple table slot (the node's <structfield>ScanTupleSlot</> should be
+ used for this purpose). The data that was actually inserted, updated
+ or deleted must be stored in the
+ <literal>es_result_relation_info->ri_projectReturning->pi_exprContext->ecxt_scantuple</>
+ of the node's <structname>EState</>.
+ Return NULL if no more rows are available.
+ Note that this is called in a short-lived memory context that will be
+ reset between invocations. Create a memory context in
+ <function>BeginDMLPushdown</> if you need longer-lived storage, or use
+ the <structfield>es_query_cxt</> of the node's <structname>EState</>.
+ </para>
+
+ <para>
+ The rows returned must match the <structfield>fdw_scan_tlist</> target
+ list if one was supplied, otherwise they must match the row type of the
+ foreign table being updated. If you choose to optimize away fetching
+ columns that are not needed for the <literal>RETURNING</> calculation,
+ you should insert nulls in those column positions, or else generate a
+ <structfield>fdw_scan_tlist</> list with those columns omitted.
+ </para>
+
+ <para>
+ Whether the query has the clause or not, the query's reported row count
+ must be incremented by the FDW itself. When the query doesn't have the
+ clause, the FDW must also increment the row count for the
+ <structname>ForeignScanState</> node in the <command>EXPLAIN ANALYZE</>
+ case.
+ </para>
+
+ <para>
+ If the <function>IterateDMLPushdown</> pointer is set to
+ <literal>NULL</>, attempts to execute the table update directly on
+ the remote server will fail with an error message.
+ </para>
+
+ <para>
+<programlisting>
+void
+EndDMLPushdown (ForeignScanState *node);
+</programlisting>
+
+ End the table update and release resources. It is normally not important
+ to release palloc'd memory, but for example open files and connections
+ to remote servers should be cleaned up.
+ </para>
+
+ <para>
+ If the <function>EndDMLPushdown</> pointer is set to
+ <literal>NULL</>, attempts to execute the table update directly on
+ the remote server will fail with an error message.
+ </para>
+
</sect2>
<sect2 id="fdw-callbacks-row-locking">
@@ -865,6 +1010,29 @@ ExplainForeignModify (ModifyTableState *mtstate,
<command>EXPLAIN</>.
</para>
+ <para>
+<programlisting>
+void
+ExplainDMLPushdown (ForeignScanState *node,
+ ExplainState *es);
+</programlisting>
+
+ Print additional <command>EXPLAIN</> output for a foreign table update
+ that is executed directly on the remote server.
+ This function can call <function>ExplainPropertyText</> and
+ related functions to add fields to the <command>EXPLAIN</> output.
+ The flag fields in <literal>es</> can be used to determine what to
+ print, and the state of the <structname>ForeignScanState</> node
+ can be inspected to provide run-time statistics in the <command>EXPLAIN
+ ANALYZE</> case.
+ </para>
+
+ <para>
+ If the <function>ExplainDMLPushdown</> pointer is set to
+ <literal>NULL</>, no additional information is printed during
+ <command>EXPLAIN</>.
+ </para>
+
</sect2>
<sect2 id="fdw-callbacks-analyze">
@@ -1146,7 +1314,8 @@ GetForeignServerByName(const char *name, bool missing_ok);
<para>
The FDW callback functions <function>GetForeignRelSize</>,
<function>GetForeignPaths</>, <function>GetForeignPlan</>,
- <function>PlanForeignModify</>, and <function>GetForeignJoinPaths</>
+ <function>PlanForeignModify</>, <function>GetForeignJoinPaths</>, and
+ <function>PlanDMLPushdown</>
must fit into the workings of the <productname>PostgreSQL</> planner.
Here are some notes about what they must do.
</para>
@@ -1306,7 +1475,8 @@ GetForeignServerByName(const char *name, bool missing_ok);
<para>
When planning an <command>UPDATE</> or <command>DELETE</>,
- <function>PlanForeignModify</> can look up the <structname>RelOptInfo</>
+ <function>PlanForeignModify</> and <function>PlanDMLPushdown</>
+ can look up the <structname>RelOptInfo</>
struct for the foreign table and make use of the
<literal>baserel->fdw_private</> data previously created by the
scan-planning functions. However, in <command>INSERT</> the target
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index c408ba6..31547b5 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -484,6 +484,15 @@
extension that's listed in the foreign server's <literal>extensions</>
option. Operators and functions in such clauses must
be <literal>IMMUTABLE</> as well.
+ For an <command>UPDATE</> or <command>DELETE</> query,
+ <filename>postgres_fdw</> attempts to optimize the query execution by
+ sending the whole query to the remote server if there are no query
+ <literal>WHERE</> clauses that cannot be sent to the remote server,
+ no local joins for the query, and no row-level local <literal>BEFORE</> or
+ <literal>AFTER</> triggers on the target table. In <command>UPDATE</>,
+ expressions to assign to target columns must use only built-in data types,
+ <literal>IMMUTABLE</> operators, or <literal>IMMUTABLE</> functions,
+ to reduce the risk of misexecution of the query.
</para>
<para>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index ee13136..2b2ee3c 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -900,7 +900,29 @@ ExplainNode(PlanState *planstate, List *ancestors,
pname = sname = "WorkTable Scan";
break;
case T_ForeignScan:
- pname = sname = "Foreign Scan";
+ sname = "Foreign Scan";
+ switch (((ForeignScan *) plan)->operation)
+ {
+ case CMD_SELECT:
+ pname = "Foreign Scan";
+ operation = "Select";
+ break;
+ case CMD_INSERT:
+ pname = "Foreign Insert";
+ operation = "Insert";
+ break;
+ case CMD_UPDATE:
+ pname = "Foreign Update";
+ operation = "Update";
+ break;
+ case CMD_DELETE:
+ pname = "Foreign Delete";
+ operation = "Delete";
+ break;
+ default:
+ pname = "???";
+ break;
+ }
break;
case T_CustomScan:
sname = "Custom Scan";
@@ -1648,6 +1670,21 @@ show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es)
return;
if (IsA(plan, RecursiveUnion))
return;
+ /*
+ * Likewise for ForeignScan that has pushed down INSERT/UPDATE/DELETE
+ *
+ * Note: the tlist for an ForeignScan that has pushed down an INSERT/
+ * UPDATE might contain subplan output expressions that are confusing
+ * in this context. The tlist for an ForeignScan that has pushed down
+ * an UPDATE/DELETE always contains "junk" target columns to identify
+ * the exact row to update or delete, which would be confusing in this
+ * context. So, we suppress it in all the cases.
+ */
+ if (IsA(plan, ForeignScan) &&
+ (((ForeignScan *) plan)->operation == CMD_INSERT ||
+ ((ForeignScan *) plan)->operation == CMD_UPDATE ||
+ ((ForeignScan *) plan)->operation == CMD_DELETE))
+ return;
/* Set up deparsing context */
context = set_deparse_context_planstate(es->deparse_cxt,
@@ -2236,8 +2273,16 @@ show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es)
FdwRoutine *fdwroutine = fsstate->fdwroutine;
/* Let the FDW emit whatever fields it wants */
- if (fdwroutine->ExplainForeignScan != NULL)
- fdwroutine->ExplainForeignScan(fsstate, es);
+ if (((ForeignScan *) fsstate->ss.ps.plan)->operation != CMD_SELECT)
+ {
+ if (fdwroutine->ExplainDMLPushdown != NULL)
+ fdwroutine->ExplainDMLPushdown(fsstate, es);
+ }
+ else
+ {
+ if (fdwroutine->ExplainForeignScan != NULL)
+ fdwroutine->ExplainForeignScan(fsstate, es);
+ }
}
/*
@@ -2623,8 +2668,10 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
}
}
- /* Give FDW a chance */
- if (fdwroutine && fdwroutine->ExplainForeignModify != NULL)
+ /* Give FDW a chance if needed */
+ if (!resultRelInfo->ri_FdwPushdown &&
+ fdwroutine != NULL &&
+ fdwroutine->ExplainForeignModify != NULL)
{
List *fdw_private = (List *) list_nth(node->fdwPrivLists, j);
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 76f7297..4883396 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1245,6 +1245,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
else
resultRelInfo->ri_FdwRoutine = NULL;
resultRelInfo->ri_FdwState = NULL;
+ resultRelInfo->ri_FdwPushdown = false;
resultRelInfo->ri_ConstraintExprs = NULL;
resultRelInfo->ri_junkFilter = NULL;
resultRelInfo->ri_projectReturning = NULL;
diff --git a/src/backend/executor/nodeForeignscan.c b/src/backend/executor/nodeForeignscan.c
index 388c922..2cfe256 100644
--- a/src/backend/executor/nodeForeignscan.c
+++ b/src/backend/executor/nodeForeignscan.c
@@ -48,7 +48,10 @@ ForeignNext(ForeignScanState *node)
/* Call the Iterate function in short-lived context */
oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
- slot = node->fdwroutine->IterateForeignScan(node);
+ if (plan->operation != CMD_SELECT)
+ slot = node->fdwroutine->IterateDMLPushdown(node);
+ else
+ slot = node->fdwroutine->IterateForeignScan(node);
MemoryContextSwitchTo(oldcontext);
/*
@@ -226,7 +229,10 @@ ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags)
/*
* Tell the FDW to initialize the scan.
*/
- fdwroutine->BeginForeignScan(scanstate, eflags);
+ if (node->operation != CMD_SELECT)
+ fdwroutine->BeginDMLPushdown(scanstate, eflags);
+ else
+ fdwroutine->BeginForeignScan(scanstate, eflags);
return scanstate;
}
@@ -240,8 +246,13 @@ ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags)
void
ExecEndForeignScan(ForeignScanState *node)
{
+ ForeignScan *plan = (ForeignScan *) node->ss.ps.plan;
+
/* Let the FDW shut down */
- node->fdwroutine->EndForeignScan(node);
+ if (plan->operation != CMD_SELECT)
+ node->fdwroutine->EndDMLPushdown(node);
+ else
+ node->fdwroutine->EndForeignScan(node);
/* Shut down any outer plan. */
if (outerPlanState(node))
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 27051e8..890ed59 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -138,13 +138,17 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList)
* tupleSlot: slot holding tuple actually inserted/updated/deleted
* planSlot: slot holding tuple returned by top subplan node
*
+ * Note: If tupleSlot is NULL, the FDW should have already provided econtext's
+ * scan tuple.
+ *
* Returns a slot holding the result tuple
*/
static TupleTableSlot *
-ExecProcessReturning(ProjectionInfo *projectReturning,
+ExecProcessReturning(ResultRelInfo *resultRelInfo,
TupleTableSlot *tupleSlot,
TupleTableSlot *planSlot)
{
+ ProjectionInfo *projectReturning = resultRelInfo->ri_projectReturning;
ExprContext *econtext = projectReturning->pi_exprContext;
/*
@@ -154,7 +158,20 @@ ExecProcessReturning(ProjectionInfo *projectReturning,
ResetExprContext(econtext);
/* Make tuple and any needed join variables available to ExecProject */
- econtext->ecxt_scantuple = tupleSlot;
+ if (tupleSlot)
+ econtext->ecxt_scantuple = tupleSlot;
+ else
+ {
+ HeapTuple tuple;
+
+ /*
+ * RETURNING expressions might reference the tableoid column, so
+ * initialize t_tableOid before evaluating them.
+ */
+ Assert(!TupIsNull(econtext->ecxt_scantuple));
+ tuple = ExecMaterializeSlot(econtext->ecxt_scantuple);
+ tuple->t_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
+ }
econtext->ecxt_outertuple = planSlot;
/* Compute the RETURNING expressions */
@@ -496,8 +513,7 @@ ExecInsert(ModifyTableState *mtstate,
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- return ExecProcessReturning(resultRelInfo->ri_projectReturning,
- slot, planSlot);
+ return ExecProcessReturning(resultRelInfo, slot, planSlot);
return NULL;
}
@@ -738,8 +754,7 @@ ldelete:;
ExecStoreTuple(&deltuple, slot, InvalidBuffer, false);
}
- rslot = ExecProcessReturning(resultRelInfo->ri_projectReturning,
- slot, planSlot);
+ rslot = ExecProcessReturning(resultRelInfo, slot, planSlot);
/*
* Before releasing the target tuple again, make sure rslot has a
@@ -1024,8 +1039,7 @@ lreplace:;
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
- return ExecProcessReturning(resultRelInfo->ri_projectReturning,
- slot, planSlot);
+ return ExecProcessReturning(resultRelInfo, slot, planSlot);
return NULL;
}
@@ -1380,6 +1394,21 @@ ExecModifyTable(ModifyTableState *node)
break;
}
+ /*
+ * If ri_FdwPushdown is true, all we need to do here is compute the
+ * RETURNING expressions.
+ */
+ if (resultRelInfo->ri_FdwPushdown)
+ {
+ Assert(resultRelInfo->ri_projectReturning);
+
+ /* No need to provide scan tuple to ExecProcessReturning. */
+ slot = ExecProcessReturning(resultRelInfo, NULL, planSlot);
+
+ estate->es_result_relation_info = saved_resultRelInfo;
+ return slot;
+ }
+
EvalPlanQualSetSlot(&node->mt_epqstate, planSlot);
slot = planSlot;
@@ -1559,6 +1588,9 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
{
subplan = (Plan *) lfirst(l);
+ /* Initialize the FdwPushdown flag */
+ resultRelInfo->ri_FdwPushdown = bms_is_member(i, node->fdwPushdowns);
+
/*
* Verify result relation is a valid target for the current operation
*/
@@ -1583,7 +1615,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags);
/* Also let FDWs init themselves for foreign-table result rels */
- if (resultRelInfo->ri_FdwRoutine != NULL &&
+ if (!resultRelInfo->ri_FdwPushdown &&
+ resultRelInfo->ri_FdwRoutine != NULL &&
resultRelInfo->ri_FdwRoutine->BeginForeignModify != NULL)
{
List *fdw_private = (List *) list_nth(node->fdwPrivLists, i);
@@ -1754,13 +1787,26 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
erm = ExecFindRowMark(estate, rc->rti, false);
/* build ExecAuxRowMark for each subplan */
+ resultRelInfo = mtstate->resultRelInfo;
for (i = 0; i < nplans; i++)
{
ExecAuxRowMark *aerm;
+ /*
+ * ignore subplan if the FDW pushes down the command to the remote
+ * server; the ModifyTable won't have anything to do except for
+ * evaluation of RETURNING expressions
+ */
+ if (resultRelInfo->ri_FdwPushdown)
+ {
+ resultRelInfo++;
+ continue;
+ }
+
subplan = mtstate->mt_plans[i]->plan;
aerm = ExecBuildAuxRowMark(erm, subplan->targetlist);
mtstate->mt_arowmarks[i] = lappend(mtstate->mt_arowmarks[i], aerm);
+ resultRelInfo++;
}
}
@@ -1821,6 +1867,17 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
ExecCheckPlanOutput(resultRelInfo->ri_RelationDesc,
subplan->targetlist);
+ /*
+ * ignore subplan if the FDW pushes down the command to the
+ * remote server; the ModifyTable won't have anything to do
+ * except for evaluation of RETURNING expressions
+ */
+ if (resultRelInfo->ri_FdwPushdown)
+ {
+ resultRelInfo++;
+ continue;
+ }
+
j = ExecInitJunkFilter(subplan->targetlist,
resultRelInfo->ri_RelationDesc->rd_att->tdhasoid,
ExecInitExtraTupleSlot(estate));
@@ -1910,7 +1967,8 @@ ExecEndModifyTable(ModifyTableState *node)
{
ResultRelInfo *resultRelInfo = node->resultRelInfo + i;
- if (resultRelInfo->ri_FdwRoutine != NULL &&
+ if (!resultRelInfo->ri_FdwPushdown &&
+ resultRelInfo->ri_FdwRoutine != NULL &&
resultRelInfo->ri_FdwRoutine->EndForeignModify != NULL)
resultRelInfo->ri_FdwRoutine->EndForeignModify(node->ps.state,
resultRelInfo);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index a9e9cc3..2604fa3 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -188,6 +188,7 @@ _copyModifyTable(const ModifyTable *from)
COPY_NODE_FIELD(withCheckOptionLists);
COPY_NODE_FIELD(returningLists);
COPY_NODE_FIELD(fdwPrivLists);
+ COPY_BITMAPSET_FIELD(fdwPushdowns);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(epqParam);
COPY_SCALAR_FIELD(onConflictAction);
@@ -648,6 +649,7 @@ _copyForeignScan(const ForeignScan *from)
/*
* copy remainder of node
*/
+ COPY_SCALAR_FIELD(operation);
COPY_SCALAR_FIELD(fs_server);
COPY_NODE_FIELD(fdw_exprs);
COPY_NODE_FIELD(fdw_private);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 85acce8..e1113d5 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -356,6 +356,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node)
WRITE_NODE_FIELD(withCheckOptionLists);
WRITE_NODE_FIELD(returningLists);
WRITE_NODE_FIELD(fdwPrivLists);
+ WRITE_BITMAPSET_FIELD(fdwPushdowns);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(epqParam);
WRITE_ENUM_FIELD(onConflictAction, OnConflictAction);
@@ -608,6 +609,7 @@ _outForeignScan(StringInfo str, const ForeignScan *node)
_outScanInfo(str, (const Scan *) node);
+ WRITE_ENUM_FIELD(operation, CmdType);
WRITE_OID_FIELD(fs_server);
WRITE_NODE_FIELD(fdw_exprs);
WRITE_NODE_FIELD(fdw_private);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index e6e6f29..beefa8e 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1481,6 +1481,7 @@ _readModifyTable(void)
READ_NODE_FIELD(withCheckOptionLists);
READ_NODE_FIELD(returningLists);
READ_NODE_FIELD(fdwPrivLists);
+ READ_BITMAPSET_FIELD(fdwPushdowns);
READ_NODE_FIELD(rowMarks);
READ_INT_FIELD(epqParam);
READ_ENUM_FIELD(onConflictAction, OnConflictAction);
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 198b06b..48d161c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -3791,6 +3791,7 @@ make_foreignscan(List *qptlist,
plan->lefttree = outer_plan;
plan->righttree = NULL;
node->scan.scanrelid = scanrelid;
+ node->operation = CMD_SELECT;
/* fs_server will be filled in by create_foreignscan_plan */
node->fs_server = InvalidOid;
node->fdw_exprs = fdw_exprs;
@@ -5068,6 +5069,7 @@ make_modifytable(PlannerInfo *root,
Plan *plan = &node->plan;
double total_size;
List *fdw_private_list;
+ Bitmapset *fdwpushdowns;
ListCell *subnode;
ListCell *lc;
int i;
@@ -5148,12 +5150,14 @@ make_modifytable(PlannerInfo *root,
* construct private plan data, and accumulate it all into a list.
*/
fdw_private_list = NIL;
+ fdwpushdowns = NULL;
i = 0;
foreach(lc, resultRelations)
{
Index rti = lfirst_int(lc);
FdwRoutine *fdwroutine;
List *fdw_private;
+ bool fdwpushdown;
/*
* If possible, we want to get the FdwRoutine from our RelOptInfo for
@@ -5180,7 +5184,23 @@ make_modifytable(PlannerInfo *root,
fdwroutine = NULL;
}
+ /*
+ * If the target relation has any row-level triggers, we can't push
+ * down the command to the remote server.
+ */
+ fdwpushdown = false;
if (fdwroutine != NULL &&
+ fdwroutine->PlanDMLPushdown != NULL &&
+ fdwroutine->BeginDMLPushdown != NULL &&
+ fdwroutine->IterateDMLPushdown != NULL &&
+ fdwroutine->EndDMLPushdown != NULL &&
+ !has_row_triggers(root, rti, operation))
+ fdwpushdown = fdwroutine->PlanDMLPushdown(root, node, rti, i);
+ if (fdwpushdown)
+ fdwpushdowns = bms_add_member(fdwpushdowns, i);
+
+ if (!fdwpushdown &&
+ fdwroutine != NULL &&
fdwroutine->PlanForeignModify != NULL)
fdw_private = fdwroutine->PlanForeignModify(root, node, rti, i);
else
@@ -5189,6 +5209,7 @@ make_modifytable(PlannerInfo *root,
i++;
}
node->fdwPrivLists = fdw_private_list;
+ node->fdwPushdowns = fdwpushdowns;
return node;
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 0ea9fcf..8d19b37 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1520,3 +1520,50 @@ has_unique_index(RelOptInfo *rel, AttrNumber attno)
}
return false;
}
+
+
+/*
+ * has_row_triggers
+ *
+ * Detect whether the specified relation has any row-level triggers for event.
+ */
+bool
+has_row_triggers(PlannerInfo *root, Index rti, CmdType event)
+{
+ RangeTblEntry *rte = planner_rt_fetch(rti, root);
+ Relation relation;
+ TriggerDesc *trigDesc;
+ bool result = false;
+
+ /* Assume we already have adequate lock */
+ relation = heap_open(rte->relid, NoLock);
+
+ trigDesc = relation->trigdesc;
+ switch (event)
+ {
+ case CMD_INSERT:
+ if (trigDesc &&
+ (trigDesc->trig_insert_after_row ||
+ trigDesc->trig_insert_before_row))
+ result = true;
+ break;
+ case CMD_UPDATE:
+ if (trigDesc &&
+ (trigDesc->trig_update_after_row ||
+ trigDesc->trig_update_before_row))
+ result = true;
+ break;
+ case CMD_DELETE:
+ if (trigDesc &&
+ (trigDesc->trig_delete_after_row ||
+ trigDesc->trig_delete_before_row))
+ result = true;
+ break;
+ default:
+ elog(ERROR, "unrecognized CmdType: %d", (int) event);
+ break;
+ }
+
+ heap_close(relation, NoLock);
+ return result;
+}
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index 9fafab0..e150bea 100644
--- a/src/include/foreign/fdwapi.h
+++ b/src/include/foreign/fdwapi.h
@@ -94,6 +94,18 @@ typedef void (*EndForeignModify_function) (EState *estate,
typedef int (*IsForeignRelUpdatable_function) (Relation rel);
+typedef bool (*PlanDMLPushdown_function) (PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index);
+
+typedef void (*BeginDMLPushdown_function) (ForeignScanState *node,
+ int eflags);
+
+typedef TupleTableSlot *(*IterateDMLPushdown_function) (ForeignScanState *node);
+
+typedef void (*EndDMLPushdown_function) (ForeignScanState *node);
+
typedef RowMarkType (*GetForeignRowMarkType_function) (RangeTblEntry *rte,
LockClauseStrength strength);
@@ -111,6 +123,9 @@ typedef void (*ExplainForeignModify_function) (ModifyTableState *mtstate,
int subplan_index,
struct ExplainState *es);
+typedef void (*ExplainDMLPushdown_function) (ForeignScanState *node,
+ struct ExplainState *es);
+
typedef int (*AcquireSampleRowsFunc) (Relation relation, int elevel,
HeapTuple *rows, int targrows,
double *totalrows,
@@ -171,6 +186,10 @@ typedef struct FdwRoutine
ExecForeignDelete_function ExecForeignDelete;
EndForeignModify_function EndForeignModify;
IsForeignRelUpdatable_function IsForeignRelUpdatable;
+ PlanDMLPushdown_function PlanDMLPushdown;
+ BeginDMLPushdown_function BeginDMLPushdown;
+ IterateDMLPushdown_function IterateDMLPushdown;
+ EndDMLPushdown_function EndDMLPushdown;
/* Functions for SELECT FOR UPDATE/SHARE row locking */
GetForeignRowMarkType_function GetForeignRowMarkType;
@@ -180,6 +199,7 @@ typedef struct FdwRoutine
/* Support functions for EXPLAIN */
ExplainForeignScan_function ExplainForeignScan;
ExplainForeignModify_function ExplainForeignModify;
+ ExplainDMLPushdown_function ExplainDMLPushdown;
/* Support functions for ANALYZE */
AnalyzeForeignTable_function AnalyzeForeignTable;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 064a050..fe160db 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -311,6 +311,7 @@ typedef struct JunkFilter
* TrigInstrument optional runtime measurements for triggers
* FdwRoutine FDW callback functions, if foreign table
* FdwState available to save private state of FDW
+ * FdwPushdown true when the command is pushed down
* WithCheckOptions list of WithCheckOption's to be checked
* WithCheckOptionExprs list of WithCheckOption expr states
* ConstraintExprs array of constraint-checking expr states
@@ -334,6 +335,7 @@ typedef struct ResultRelInfo
Instrumentation *ri_TrigInstrument;
struct FdwRoutine *ri_FdwRoutine;
void *ri_FdwState;
+ bool ri_FdwPushdown;
List *ri_WithCheckOptions;
List *ri_WithCheckOptionExprs;
List **ri_ConstraintExprs;
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 1cb95ee..77b50ff 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -134,16 +134,19 @@ list_length(const List *l)
#define list_make2(x1,x2) lcons(x1, list_make1(x2))
#define list_make3(x1,x2,x3) lcons(x1, list_make2(x2, x3))
#define list_make4(x1,x2,x3,x4) lcons(x1, list_make3(x2, x3, x4))
+#define list_make5(x1,x2,x3,x4,x5) lcons(x1, list_make4(x2, x3, x4, x5))
#define list_make1_int(x1) lcons_int(x1, NIL)
#define list_make2_int(x1,x2) lcons_int(x1, list_make1_int(x2))
#define list_make3_int(x1,x2,x3) lcons_int(x1, list_make2_int(x2, x3))
#define list_make4_int(x1,x2,x3,x4) lcons_int(x1, list_make3_int(x2, x3, x4))
+#define list_make5_int(x1,x2,x3,x4,x5) lcons_int(x1, list_make4_int(x2, x3, x4, x5))
#define list_make1_oid(x1) lcons_oid(x1, NIL)
#define list_make2_oid(x1,x2) lcons_oid(x1, list_make1_oid(x2))
#define list_make3_oid(x1,x2,x3) lcons_oid(x1, list_make2_oid(x2, x3))
#define list_make4_oid(x1,x2,x3,x4) lcons_oid(x1, list_make3_oid(x2, x3, x4))
+#define list_make5_oid(x1,x2,x3,x4,x5) lcons_oid(x1, list_make4_oid(x2, x3, x4, x5))
/*
* foreach -
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index ae224cf..9f8185a 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -189,6 +189,7 @@ typedef struct ModifyTable
List *withCheckOptionLists; /* per-target-table WCO lists */
List *returningLists; /* per-target-table RETURNING tlists */
List *fdwPrivLists; /* per-target-table FDW private data lists */
+ Bitmapset *fdwPushdowns; /* indices of plans that push down command */
List *rowMarks; /* PlanRowMarks (non-locking only) */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
OnConflictAction onConflictAction; /* ON CONFLICT action */
@@ -531,6 +532,7 @@ typedef struct WorkTableScan
typedef struct ForeignScan
{
Scan scan;
+ CmdType operation; /* SELECT/INSERT/UPDATE/DELETE */
Oid fs_server; /* OID of foreign server */
List *fdw_exprs; /* expressions that FDW may evaluate */
List *fdw_private; /* private data for FDW */
diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h
index 52335fa..125274e 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -55,4 +55,6 @@ extern Selectivity join_selectivity(PlannerInfo *root,
JoinType jointype,
SpecialJoinInfo *sjinfo);
+extern bool has_row_triggers(PlannerInfo *root, Index rti, CmdType event);
+
#endif /* PLANCAT_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
