On 2016/01/28 15:20, Rushabh Lathia wrote:
On Thu, Jan 28, 2016 at 11:33 AM, Etsuro Fujita
<fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.jp>> wrote:
On 2016/01/27 21:23, Rushabh Lathia wrote:
If I understood correctly, above documentation means, that if
FDW have
DMLPushdown APIs that is enough. But in reality thats not the
case, we
need ExecForeignInsert, ExecForeignUpdate, or ExecForeignDelete
in case
DML is not pushable.
And here fact is DMLPushdown APIs are optional for FDW, so that
if FDW
don't have DMLPushdown APIs they can still very well perform the DML
operations using ExecForeignInsert, ExecForeignUpdate, or
ExecForeignDelete.
So documentation should be like:
If the IsForeignRelUpdatable pointer is set to NULL, foreign
tables are
assumed to be insertable, updatable, or deletable if the FDW
provides
ExecForeignInsert, ExecForeignUpdate, or ExecForeignDelete
respectively,
If FDW provides DMLPushdown APIs and the DML are pushable to the
foreign
server, then FDW still needs ExecForeignInsert,
ExecForeignUpdate, or
ExecForeignDelete for the non-pushable DML operation.
What's your opinion ?
I agree that we should add this to the documentation, too.
I added docs to the IsForeignRelUpdatable documentation. Also, a brief
introductory remark has been added at the beginning of the DML pushdown
APIs' documentation.
BTW, if I understand correctly, I think we should also modify
relation_is_updatabale() accordingly. Am I right?
Yep, we need to modify relation_is_updatable().
I thought I'd modify that function in the same way as
CheckValidResultRel(), but I noticed that we cannot do that, because we
don't have any information on whether each update is pushed down to the
remote server by PlanDMLPushdown, during relation_is_updatabale(). So,
I left that function as-is. relation_is_updatabale() is just used for
display in the information_schema views, so ISTM that that function is
fine as-is. (As for CheckValidResultRel(), I revised it so as to check
the presence of DML pushdown APIs after checking the existing APIs if
the given command will be pushed down. The reason is because we assume
the presence of the existing APIs, anyway.)
I revised other docs and some comments, mostly for consistency.
Attached is an updated version of the patch, which has been created on
top of the updated version of the bugfix patch posted by Robert in [1]
(attached).
Best regards,
Etsuro Fujita
[1]
http://www.postgresql.org/message-id/ca+tgmoz40j2uc5ac1nxu03oj4crvolks15xx+ptfp-1u-8z...@mail.gmail.com
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index df3d1ee..d778e61 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -112,6 +112,7 @@ static void deparseTargetList(StringInfo buf,
PlannerInfo *root,
Index rtindex,
Relation rel,
+ bool is_returning,
Bitmapset *attrs_used,
List **retrieved_attrs);
static void deparseReturningList(StringInfo buf, PlannerInfo *root,
@@ -776,7 +777,7 @@ deparseSelectSql(Bitmapset *attrs_used, List **retrieved_attrs,
* Construct SELECT list
*/
appendStringInfoString(buf, "SELECT ");
- deparseTargetList(buf, root, foreignrel->relid, rel, attrs_used,
+ deparseTargetList(buf, root, foreignrel->relid, rel, false, attrs_used,
retrieved_attrs);
/*
@@ -790,7 +791,8 @@ deparseSelectSql(Bitmapset *attrs_used, List **retrieved_attrs,
/*
* Emit a target list that retrieves the columns specified in attrs_used.
- * This is used for both SELECT and RETURNING targetlists.
+ * This is used for both SELECT and RETURNING targetlists; the is_returning
+ * parameter is true only for a RETURNING targetlist.
*
* The tlist text is appended to buf, and we also create an integer List
* of the columns being retrieved, which is returned to *retrieved_attrs.
@@ -800,6 +802,7 @@ deparseTargetList(StringInfo buf,
PlannerInfo *root,
Index rtindex,
Relation rel,
+ bool is_returning,
Bitmapset *attrs_used,
List **retrieved_attrs)
{
@@ -829,6 +832,8 @@ deparseTargetList(StringInfo buf,
{
if (!first)
appendStringInfoString(buf, ", ");
+ else if (is_returning)
+ appendStringInfoString(buf, " RETURNING ");
first = false;
deparseColumnRef(buf, rtindex, i, root);
@@ -846,6 +851,8 @@ deparseTargetList(StringInfo buf,
{
if (!first)
appendStringInfoString(buf, ", ");
+ else if (is_returning)
+ appendStringInfoString(buf, " RETURNING ");
first = false;
appendStringInfoString(buf, "ctid");
@@ -855,7 +862,7 @@ deparseTargetList(StringInfo buf,
}
/* Don't generate bad syntax if no undropped columns */
- if (first)
+ if (first && !is_returning)
appendStringInfoString(buf, "NULL");
}
@@ -1113,11 +1120,8 @@ deparseReturningList(StringInfo buf, PlannerInfo *root,
}
if (attrs_used != NULL)
- {
- appendStringInfoString(buf, " RETURNING ");
- deparseTargetList(buf, root, rtindex, rel, attrs_used,
+ deparseTargetList(buf, root, rtindex, rel, true, attrs_used,
retrieved_attrs);
- }
else
*retrieved_attrs = NIL;
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2390e61..f90ab5a 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2408,6 +2408,59 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
1104 | 204 | ddd |
(819 rows)
+EXPLAIN (verbose, costs off)
+INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Insert on public.ft2
+ Output: (tableoid)::regclass
+ Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
+ -> Result
+ Output: 9999, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10), NULL::user_enum
+(5 rows)
+
+INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
+ tableoid
+----------
+ ft2
+(1 row)
+
+EXPLAIN (verbose, costs off)
+UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
+ 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)
+
+UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
+ tableoid
+----------
+ ft2
+(1 row)
+
+EXPLAIN (verbose, costs off)
+DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
+ 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)
+
+DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
+ tableoid
+----------
+ ft2
+(1 row)
+
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
BEGIN
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 5c6ead1..f198e8e 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -413,6 +413,15 @@ 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;
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;
+EXPLAIN (verbose, costs off)
+DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
+DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 46299fc..27051e8 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -308,6 +308,12 @@ ExecInsert(ModifyTableState *mtstate,
/* FDW might have changed tuple */
tuple = ExecMaterializeSlot(slot);
+ /*
+ * AFTER ROW Triggers or RETURNING expressions might reference the
+ * tableoid column, so initialize t_tableOid before evaluating them.
+ */
+ tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
+
newId = InvalidOid;
}
else
@@ -561,6 +567,8 @@ ExecDelete(ItemPointer tupleid,
}
else if (resultRelInfo->ri_FdwRoutine)
{
+ HeapTuple tuple;
+
/*
* delete from foreign table: let the FDW do it
*
@@ -579,6 +587,15 @@ ExecDelete(ItemPointer tupleid,
if (slot == NULL) /* "do nothing" */
return NULL;
+
+ /*
+ * RETURNING expressions might reference the tableoid column, so
+ * initialize t_tableOid before evaluating them.
+ */
+ if (slot->tts_isempty)
+ ExecStoreAllNullTuple(slot);
+ tuple = ExecMaterializeSlot(slot);
+ tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
}
else
{
@@ -838,6 +855,12 @@ ExecUpdate(ItemPointer tupleid,
/* FDW might have changed tuple */
tuple = ExecMaterializeSlot(slot);
+
+ /*
+ * AFTER ROW Triggers or RETURNING expressions might reference the
+ * tableoid column, so initialize t_tableOid before evaluating them.
+ */
+ tuple->t_tableOid = RelationGetRelid(resultRelationDesc);
}
else
{
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 1069,1074 **** deparseUpdateSql(StringInfo buf, PlannerInfo *root,
--- 1069,1134 ----
}
/*
+ * 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);
+ appendStringInfoString(buf, " = ");
+ deparseExpr((Expr *) tle->expr, &context);
+ }
+
+ reset_transmission_modes(nestlevel);
+
+ if (remote_conds)
+ appendWhereClause(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
***************
*** 1091,1096 **** deparseDeleteSql(StringInfo buf, PlannerInfo *root,
--- 1151,1190 ----
}
/*
+ * 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)
+ appendWhereClause(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
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1314,1320 **** INSERT INTO ft2 (c1,c2,c3)
--- 1314,1339 ----
(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
------+-----+--------------------+------------------------------+--------------------------+----+------------+-----
***************
*** 1424,1430 **** 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;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
--- 1443,1449 ----
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; -- can't be pushed down
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
***************
*** 1445,1460 **** 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 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)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
c1 | c4
--- 1464,1477 ----
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; -- can be pushed down
! QUERY PLAN
! --------------------------------------------------------------------------------------------
Delete on public.ft2
Output: c1, c4
! -> 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
***************
*** 1565,1571 **** 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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
--- 1582,1588 ----
(103 rows)
EXPLAIN (verbose, costs off)
! 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
***************
*** 2426,2441 **** 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 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)
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
tableoid
--- 2443,2456 ----
(1 row)
EXPLAIN (verbose, costs off)
! UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
! QUERY PLAN
! ------------------------------------------------------------------------------------
Update on public.ft2
Output: (tableoid)::regclass
! -> 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
***************
*** 2444,2459 **** 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 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)
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
tableoid
--- 2459,2472 ----
(1 row)
EXPLAIN (verbose, costs off)
! DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass; -- can be pushed down
! QUERY PLAN
! --------------------------------------------------------------------
Delete on public.ft2
Output: (tableoid)::regclass
! -> 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
***************
*** 2607,2613 **** 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
-- Test savepoint/rollback behavior
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
c2 | count
--- 2620,2626 ----
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 = (- 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
***************
*** 2766,2772 **** 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
rollback to savepoint s3;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
c2 | count
--- 2779,2785 ----
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 ((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
***************
*** 2906,2912 **** 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
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);
--- 2919,2925 ----
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 = (- 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);
***************
*** 3299,3304 **** NOTICE: NEW: (13,"test triggered !")
--- 3312,3510 ----
(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
-- ===================================================================
***************
*** 3768,3773 **** fetch from c;
--- 3974,4029 ----
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;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 57,63 **** PG_MODULE_MAGIC;
* planner to executor. Currently we store:
*
* 1) SELECT statement text to be sent to the remote server
! * 2) Integer list of attribute numbers retrieved by the SELECT
*
* These items are indexed with the enum FdwScanPrivateIndex, so an item
* can be fetched with list_nth(). For example, to get the SELECT statement:
--- 57,64 ----
* planner to executor. Currently we store:
*
* 1) SELECT statement text to be sent to the remote server
! * 2) List of restriction clauses that can be executed remotely
! * 3) Integer list of attribute numbers retrieved by the SELECT
*
* These items are indexed with the enum FdwScanPrivateIndex, so an item
* can be fetched with list_nth(). For example, to get the SELECT statement:
***************
*** 67,72 **** enum FdwScanPrivateIndex
--- 68,75 ----
{
/* 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
};
***************
*** 94,99 **** enum FdwModifyPrivateIndex
--- 97,124 ----
};
/*
+ * 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
***************
*** 156,161 **** typedef struct PgFdwModifyState
--- 181,217 ----
} 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
***************
*** 247,252 **** static TupleTableSlot *postgresExecForeignDelete(EState *estate,
--- 303,315 ----
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,
***************
*** 254,259 **** static void postgresExplainForeignModify(ModifyTableState *mtstate,
--- 317,324 ----
List *fdw_private,
int subplan_index,
ExplainState *es);
+ static void postgresExplainDMLPushdown(ForeignScanState *node,
+ ExplainState *es);
static bool postgresAnalyzeForeignTable(Relation relation,
AcquireSampleRowsFunc *func,
BlockNumber *totalpages);
***************
*** 290,295 **** static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
--- 355,362 ----
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 int postgresAcquireSampleRowsFunc(Relation relation, int elevel,
HeapTuple *rows, int targrows,
double *totalrows,
***************
*** 332,341 **** postgres_fdw_handler(PG_FUNCTION_ARGS)
--- 399,413 ----
routine->ExecForeignDelete = postgresExecForeignDelete;
routine->EndForeignModify = postgresEndForeignModify;
routine->IsForeignRelUpdatable = postgresIsForeignRelUpdatable;
+ routine->PlanDMLPushdown = postgresPlanDMLPushdown;
+ routine->BeginDMLPushdown = postgresBeginDMLPushdown;
+ routine->IterateDMLPushdown = postgresIterateDMLPushdown;
+ routine->EndDMLPushdown = postgresEndDMLPushdown;
/* Support functions for EXPLAIN */
routine->ExplainForeignScan = postgresExplainForeignScan;
routine->ExplainForeignModify = postgresExplainForeignModify;
+ routine->ExplainDMLPushdown = postgresExplainDMLPushdown;
/* Support functions for ANALYZE */
routine->AnalyzeForeignTable = postgresAnalyzeForeignTable;
***************
*** 1010,1016 **** postgresGetForeignPlan(PlannerInfo *root,
* Build the fdw_private list that will be available to the executor.
* Items in the list must match enum FdwScanPrivateIndex, above.
*/
! fdw_private = list_make2(makeString(sql.data),
retrieved_attrs);
/*
--- 1082,1089 ----
* Build the fdw_private list that will be available to the executor.
* Items in the list must match enum FdwScanPrivateIndex, above.
*/
! fdw_private = list_make3(makeString(sql.data),
! remote_conds,
retrieved_attrs);
/*
***************
*** 1304,1316 **** 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,
--- 1377,1382 ----
***************
*** 1821,1826 **** postgresIsForeignRelUpdatable(Relation rel)
--- 1887,2223 ----
}
/*
+ * 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.
+ *
+ * Conditions checked here:
+ *
+ * 1. The table modification must be an UPDATE or DELETE.
+ *
+ * 2. It's unsafe to push down the command if there are any local joins needed.
+ *
+ * 3. It's unsafe to push down the command if there are any quals that can't be
+ * evaluated remotely.
+ *
+ * 4. We can't push down an UPDATE, if any expressions to assign to the target
+ * columns are unsafe to evaluate on the remote end.
+ */
+ 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 safe to push down to the remote
+ * server.
+ */
+
+ /* 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)
+ {
+ 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.
+ */
+ fscan = (ForeignScan *) subplan;
+
+ 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;
+ int i;
+ ListCell *lc;
+
+ /*
+ * 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 output conversion of parameters used in remote query. */
+ numParams = list_length(fsplan->fdw_exprs);
+ dpstate->numParams = numParams;
+ dpstate->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, &dpstate->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.)
+ */
+ dpstate->param_exprs = (List *)
+ ExecInitExpr((Expr *) fsplan->fdw_exprs,
+ (PlanState *) node);
+
+ /*
+ * Allocate buffer for text form of query parameters, if any.
+ */
+ if (numParams > 0)
+ dpstate->param_values = (const char **) palloc0(numParams * sizeof(char *));
+ else
+ dpstate->param_values = NULL;
+ }
+
+ /*
+ * 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
*/
***************
*** 1858,1863 **** postgresExplainForeignModify(ModifyTableState *mtstate,
--- 2255,2279 ----
}
}
+ /*
+ * 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
***************
*** 2474,2479 **** store_returning_result(PgFdwModifyState *fmstate,
--- 2890,3028 ----
}
/*
+ * 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)
+ {
+ int nestlevel;
+ int i;
+ ListCell *lc;
+
+ nestlevel = set_transmission_modes();
+
+ i = 0;
+ foreach(lc, dpstate->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(&dpstate->param_flinfo[i],
+ expr_value);
+ i++;
+ }
+
+ reset_transmission_modes(nestlevel);
+ }
+
+ /*
+ * 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,
+ 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;
+ }
+
+ /*
* postgresAnalyzeForeignTable
* Test whether analyzing this foreign table is supported
*/
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 91,100 **** extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
--- 91,114 ----
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);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 399,426 **** 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');
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
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;
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;
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;
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;
EXPLAIN (verbose, costs off)
! DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
-- Test that trigger on remote table works as expected
--- 399,430 ----
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; -- 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; -- 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; -- 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; -- 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; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
-- Test that trigger on remote table works as expected
***************
*** 737,742 **** UPDATE rem1 SET f2 = 'testo';
--- 741,830 ----
-- 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
-- ===================================================================
***************
*** 868,873 **** fetch from c;
--- 956,968 ----
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;
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 657,669 **** IsForeignRelUpdatable (Relation rel);
<literal>NULL</>, foreign tables are assumed to be insertable, updatable,
or deletable if the FDW provides <function>ExecForeignInsert</>,
<function>ExecForeignUpdate</>, or <function>ExecForeignDelete</>
! respectively. This function is only needed if the FDW supports some
tables that are updatable and some that are not. (Even then, it's
permissible to throw an error in the execution routine instead of
checking in this function. However, this function is used to determine
updatability for display in the <literal>information_schema</> views.)
</para>
</sect2>
<sect2 id="fdw-callbacks-row-locking">
--- 657,819 ----
<literal>NULL</>, foreign tables are assumed to be insertable, updatable,
or deletable if the FDW provides <function>ExecForeignInsert</>,
<function>ExecForeignUpdate</>, or <function>ExecForeignDelete</>
! respectively.
! (If the FDW optimizes a foreign table update on a foreign table using
! <function>PlanDMLPushdown</>, it still needs to provide
! <function>BeginDMLPushdown</>, <function>IterateDMLPushdown</>, and
! <function>EndDMLPushdown</> to execute the optimized update.)
! This function is only needed if the FDW supports some
tables that are updatable and some that are not. (Even then, it's
permissible to throw an error in the execution routine instead of
checking in this function. However, this function is used to determine
updatability for display in the <literal>information_schema</> views.)
</para>
+ <para>
+ If an FDW supports an optimization that executes a foreign table update
+ directly on the remote server, it should provide the following callback
+ functions in addition to the table-updating functions described above:
+ </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 function is executed just before <function>PlanForeignModify</>
+ is called. If this function succeeds, <function>PlanForeignModify</>
+ won't be executed, and <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 has 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">
***************
*** 848,853 **** ExplainForeignModify (ModifyTableState *mtstate,
--- 998,1026 ----
<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">
***************
*** 1068,1074 **** GetForeignServerByName(const char *name, bool missing_ok);
<para>
The FDW callback functions <function>GetForeignRelSize</>,
<function>GetForeignPaths</>, <function>GetForeignPlan</>,
! <function>PlanForeignModify</>, and <function>GetForeignJoinPaths</>
must fit into the workings of the <productname>PostgreSQL</> planner.
Here are some notes about what they must do.
</para>
--- 1241,1248 ----
<para>
The FDW callback functions <function>GetForeignRelSize</>,
<function>GetForeignPaths</>, <function>GetForeignPlan</>,
! <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>
***************
*** 1228,1234 **** GetForeignServerByName(const char *name, bool missing_ok);
<para>
When planning an <command>UPDATE</> or <command>DELETE</>,
! <function>PlanForeignModify</> 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
--- 1402,1409 ----
<para>
When planning an <command>UPDATE</> or <command>DELETE</>,
! <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
*** a/doc/src/sgml/postgres-fdw.sgml
--- b/doc/src/sgml/postgres-fdw.sgml
***************
*** 471,476 ****
--- 471,485 ----
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>
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 888,894 **** ExplainNode(PlanState *planstate, List *ancestors,
pname = sname = "WorkTable Scan";
break;
case T_ForeignScan:
! pname = sname = "Foreign Scan";
break;
case T_CustomScan:
sname = "Custom Scan";
--- 888,916 ----
pname = sname = "WorkTable Scan";
break;
case T_ForeignScan:
! 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";
***************
*** 1636,1641 **** show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es)
--- 1658,1669 ----
return;
if (IsA(plan, RecursiveUnion))
return;
+ /* 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;
/* Set up deparsing context */
context = set_deparse_context_planstate(es->deparse_cxt,
***************
*** 2224,2231 **** 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);
}
/*
--- 2252,2267 ----
FdwRoutine *fdwroutine = fsstate->fdwroutine;
/* Let the FDW emit whatever fields it wants */
! 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);
! }
}
/*
***************
*** 2611,2618 **** show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
}
}
! /* Give FDW a chance */
! if (fdwroutine && fdwroutine->ExplainForeignModify != NULL)
{
List *fdw_private = (List *) list_nth(node->fdwPrivLists, j);
--- 2647,2656 ----
}
}
! /* Give FDW a chance if needed */
! if (!resultRelInfo->ri_FdwPushdown &&
! fdwroutine != NULL &&
! fdwroutine->ExplainForeignModify != NULL)
{
List *fdw_private = (List *) list_nth(node->fdwPrivLists, j);
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 1011,1018 **** InitPlan(QueryDesc *queryDesc, int eflags)
* CheckValidRowMarkRel.
*/
void
! CheckValidResultRel(Relation resultRel, CmdType operation)
{
TriggerDesc *trigDesc = resultRel->trigdesc;
FdwRoutine *fdwroutine;
--- 1011,1019 ----
* CheckValidRowMarkRel.
*/
void
! CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
{
+ Relation resultRel = resultRelInfo->ri_RelationDesc;
TriggerDesc *trigDesc = resultRel->trigdesc;
FdwRoutine *fdwroutine;
***************
*** 1128,1133 **** CheckValidResultRel(Relation resultRel, CmdType operation)
--- 1129,1166 ----
elog(ERROR, "unrecognized CmdType: %d", (int) operation);
break;
}
+ if (resultRelInfo->ri_FdwPushdown)
+ {
+ if (fdwroutine->BeginDMLPushdown == NULL ||
+ fdwroutine->IterateDMLPushdown == NULL ||
+ fdwroutine->EndDMLPushdown == NULL)
+ {
+ switch (operation)
+ {
+ case CMD_INSERT:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot push down insert on foreign table \"%s\"",
+ RelationGetRelationName(resultRel))));
+ break;
+ case CMD_UPDATE:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot push down update on foreign table \"%s\"",
+ RelationGetRelationName(resultRel))));
+ break;
+ case CMD_DELETE:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot push down delete on foreign table \"%s\"",
+ RelationGetRelationName(resultRel))));
+ break;
+ default:
+ elog(ERROR, "unrecognized CmdType: %d", (int) operation);
+ break;
+ }
+ }
+ }
break;
default:
ereport(ERROR,
***************
*** 1245,1250 **** InitResultRelInfo(ResultRelInfo *resultRelInfo,
--- 1278,1284 ----
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;
*** a/src/backend/executor/nodeForeignscan.c
--- b/src/backend/executor/nodeForeignscan.c
***************
*** 48,54 **** ForeignNext(ForeignScanState *node)
/* Call the Iterate function in short-lived context */
oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
! slot = node->fdwroutine->IterateForeignScan(node);
MemoryContextSwitchTo(oldcontext);
/*
--- 48,57 ----
/* Call the Iterate function in short-lived context */
oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
! if (plan->operation != CMD_SELECT)
! slot = node->fdwroutine->IterateDMLPushdown(node);
! else
! slot = node->fdwroutine->IterateForeignScan(node);
MemoryContextSwitchTo(oldcontext);
/*
***************
*** 226,232 **** ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags)
/*
* Tell the FDW to initialize the scan.
*/
! fdwroutine->BeginForeignScan(scanstate, eflags);
return scanstate;
}
--- 229,238 ----
/*
* Tell the FDW to initialize the scan.
*/
! if (node->operation != CMD_SELECT)
! fdwroutine->BeginDMLPushdown(scanstate, eflags);
! else
! fdwroutine->BeginForeignScan(scanstate, eflags);
return scanstate;
}
***************
*** 240,247 **** ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags)
void
ExecEndForeignScan(ForeignScanState *node)
{
/* Let the FDW shut down */
! node->fdwroutine->EndForeignScan(node);
/* Shut down any outer plan. */
if (outerPlanState(node))
--- 246,258 ----
void
ExecEndForeignScan(ForeignScanState *node)
{
+ ForeignScan *plan = (ForeignScan *) node->ss.ps.plan;
+
/* Let the FDW shut down */
! if (plan->operation != CMD_SELECT)
! node->fdwroutine->EndDMLPushdown(node);
! else
! node->fdwroutine->EndForeignScan(node);
/* Shut down any outer plan. */
if (outerPlanState(node))
*** a/src/backend/executor/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 138,150 **** ExecCheckPlanOutput(Relation resultRel, List *targetList)
* tupleSlot: slot holding tuple actually inserted/updated/deleted
* planSlot: slot holding tuple returned by top subplan node
*
* Returns a slot holding the result tuple
*/
static TupleTableSlot *
! ExecProcessReturning(ProjectionInfo *projectReturning,
TupleTableSlot *tupleSlot,
TupleTableSlot *planSlot)
{
ExprContext *econtext = projectReturning->pi_exprContext;
/*
--- 138,154 ----
* 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(ResultRelInfo *resultRelInfo,
TupleTableSlot *tupleSlot,
TupleTableSlot *planSlot)
{
+ ProjectionInfo *projectReturning = resultRelInfo->ri_projectReturning;
ExprContext *econtext = projectReturning->pi_exprContext;
/*
***************
*** 154,160 **** ExecProcessReturning(ProjectionInfo *projectReturning,
ResetExprContext(econtext);
/* Make tuple and any needed join variables available to ExecProject */
! econtext->ecxt_scantuple = tupleSlot;
econtext->ecxt_outertuple = planSlot;
/* Compute the RETURNING expressions */
--- 158,177 ----
ResetExprContext(econtext);
/* Make tuple and any needed join variables available to ExecProject */
! 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,503 **** ExecInsert(ModifyTableState *mtstate,
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
! return ExecProcessReturning(resultRelInfo->ri_projectReturning,
! slot, planSlot);
return NULL;
}
--- 513,519 ----
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
! return ExecProcessReturning(resultRelInfo, slot, planSlot);
return NULL;
}
***************
*** 738,745 **** ldelete:;
ExecStoreTuple(&deltuple, slot, InvalidBuffer, false);
}
! rslot = ExecProcessReturning(resultRelInfo->ri_projectReturning,
! slot, planSlot);
/*
* Before releasing the target tuple again, make sure rslot has a
--- 754,760 ----
ExecStoreTuple(&deltuple, slot, InvalidBuffer, false);
}
! rslot = ExecProcessReturning(resultRelInfo, slot, planSlot);
/*
* Before releasing the target tuple again, make sure rslot has a
***************
*** 1024,1031 **** lreplace:;
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
! return ExecProcessReturning(resultRelInfo->ri_projectReturning,
! slot, planSlot);
return NULL;
}
--- 1039,1045 ----
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
! return ExecProcessReturning(resultRelInfo, slot, planSlot);
return NULL;
}
***************
*** 1380,1385 **** ExecModifyTable(ModifyTableState *node)
--- 1394,1414 ----
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,1568 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
{
subplan = (Plan *) lfirst(l);
/*
* Verify result relation is a valid target for the current operation
*/
! CheckValidResultRel(resultRelInfo->ri_RelationDesc, operation);
/*
* If there are indices on the result relation, open them and save
--- 1588,1600 ----
{
subplan = (Plan *) lfirst(l);
+ /* Initialize the FdwPushdown flag */
+ resultRelInfo->ri_FdwPushdown = list_nth_int(node->fdwPushdowns, i);
+
/*
* Verify result relation is a valid target for the current operation
*/
! CheckValidResultRel(resultRelInfo, operation);
/*
* If there are indices on the result relation, open them and save
***************
*** 1583,1589 **** 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 &&
resultRelInfo->ri_FdwRoutine->BeginForeignModify != NULL)
{
List *fdw_private = (List *) list_nth(node->fdwPrivLists, i);
--- 1615,1622 ----
mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags);
/* Also let FDWs init themselves for foreign-table result rels */
! if (!resultRelInfo->ri_FdwPushdown &&
! resultRelInfo->ri_FdwRoutine != NULL &&
resultRelInfo->ri_FdwRoutine->BeginForeignModify != NULL)
{
List *fdw_private = (List *) list_nth(node->fdwPrivLists, i);
***************
*** 1754,1766 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
--- 1787,1811 ----
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
+ */
+ 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,1826 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
--- 1866,1881 ----
ExecCheckPlanOutput(resultRelInfo->ri_RelationDesc,
subplan->targetlist);
+ /*
+ * ignore subplan if the FDW pushes down the command to the
+ * remote server
+ */
+ if (resultRelInfo->ri_FdwPushdown)
+ {
+ resultRelInfo++;
+ continue;
+ }
+
j = ExecInitJunkFilter(subplan->targetlist,
resultRelInfo->ri_RelationDesc->rd_att->tdhasoid,
ExecInitExtraTupleSlot(estate));
***************
*** 1910,1916 **** ExecEndModifyTable(ModifyTableState *node)
{
ResultRelInfo *resultRelInfo = node->resultRelInfo + i;
! if (resultRelInfo->ri_FdwRoutine != NULL &&
resultRelInfo->ri_FdwRoutine->EndForeignModify != NULL)
resultRelInfo->ri_FdwRoutine->EndForeignModify(node->ps.state,
resultRelInfo);
--- 1965,1972 ----
{
ResultRelInfo *resultRelInfo = node->resultRelInfo + i;
! if (!resultRelInfo->ri_FdwPushdown &&
! resultRelInfo->ri_FdwRoutine != NULL &&
resultRelInfo->ri_FdwRoutine->EndForeignModify != NULL)
resultRelInfo->ri_FdwRoutine->EndForeignModify(node->ps.state,
resultRelInfo);
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 187,192 **** _copyModifyTable(const ModifyTable *from)
--- 187,193 ----
COPY_NODE_FIELD(withCheckOptionLists);
COPY_NODE_FIELD(returningLists);
COPY_NODE_FIELD(fdwPrivLists);
+ COPY_NODE_FIELD(fdwPushdowns);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(epqParam);
COPY_SCALAR_FIELD(onConflictAction);
***************
*** 646,651 **** _copyForeignScan(const ForeignScan *from)
--- 647,653 ----
/*
* copy remainder of node
*/
+ COPY_SCALAR_FIELD(operation);
COPY_SCALAR_FIELD(fs_server);
COPY_NODE_FIELD(fdw_exprs);
COPY_NODE_FIELD(fdw_private);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 341,346 **** _outModifyTable(StringInfo str, const ModifyTable *node)
--- 341,347 ----
WRITE_NODE_FIELD(withCheckOptionLists);
WRITE_NODE_FIELD(returningLists);
WRITE_NODE_FIELD(fdwPrivLists);
+ WRITE_NODE_FIELD(fdwPushdowns);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(epqParam);
WRITE_ENUM_FIELD(onConflictAction, OnConflictAction);
***************
*** 592,597 **** _outForeignScan(StringInfo str, const ForeignScan *node)
--- 593,599 ----
_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);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 1472,1477 **** _readModifyTable(void)
--- 1472,1478 ----
READ_NODE_FIELD(withCheckOptionLists);
READ_NODE_FIELD(returningLists);
READ_NODE_FIELD(fdwPrivLists);
+ READ_NODE_FIELD(fdwPushdowns);
READ_NODE_FIELD(rowMarks);
READ_INT_FIELD(epqParam);
READ_ENUM_FIELD(onConflictAction, OnConflictAction);
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3779,3784 **** make_foreignscan(List *qptlist,
--- 3779,3785 ----
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;
***************
*** 5055,5060 **** make_modifytable(PlannerInfo *root,
--- 5056,5062 ----
Plan *plan = &node->plan;
double total_size;
List *fdw_private_list;
+ List *fdwpushdown_list;
ListCell *subnode;
ListCell *lc;
int i;
***************
*** 5135,5146 **** make_modifytable(PlannerInfo *root,
--- 5137,5150 ----
* construct private plan data, and accumulate it all into a list.
*/
fdw_private_list = NIL;
+ fdwpushdown_list = NIL;
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
***************
*** 5167,5173 **** make_modifytable(PlannerInfo *root,
--- 5171,5190 ----
fdwroutine = NULL;
}
+ /*
+ * If the target relation has any row-level triggers, we can't push
+ * down the command to the remote server.
+ */
if (fdwroutine != NULL &&
+ fdwroutine->PlanDMLPushdown != NULL &&
+ !has_row_triggers(root, rti, operation))
+ fdwpushdown = fdwroutine->PlanDMLPushdown(root, node, rti, i);
+ else
+ fdwpushdown = false;
+ fdwpushdown_list = lappend_int(fdwpushdown_list, fdwpushdown);
+
+ if (!fdwpushdown &&
+ fdwroutine != NULL &&
fdwroutine->PlanForeignModify != NULL)
fdw_private = fdwroutine->PlanForeignModify(root, node, rti, i);
else
***************
*** 5176,5181 **** make_modifytable(PlannerInfo *root,
--- 5193,5199 ----
i++;
}
node->fdwPrivLists = fdw_private_list;
+ node->fdwPushdowns = fdwpushdown_list;
return node;
}
*** a/src/backend/optimizer/util/plancat.c
--- b/src/backend/optimizer/util/plancat.c
***************
*** 1520,1522 **** has_unique_index(RelOptInfo *rel, AttrNumber attno)
--- 1520,1569 ----
}
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;
+ }
*** a/src/include/executor/executor.h
--- b/src/include/executor/executor.h
***************
*** 184,190 **** extern void ExecutorEnd(QueryDesc *queryDesc);
extern void standard_ExecutorEnd(QueryDesc *queryDesc);
extern void ExecutorRewind(QueryDesc *queryDesc);
extern bool ExecCheckRTPerms(List *rangeTable, bool ereport_on_violation);
! extern void CheckValidResultRel(Relation resultRel, CmdType operation);
extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
--- 184,190 ----
extern void standard_ExecutorEnd(QueryDesc *queryDesc);
extern void ExecutorRewind(QueryDesc *queryDesc);
extern bool ExecCheckRTPerms(List *rangeTable, bool ereport_on_violation);
! extern void CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation);
extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
***************
*** 93,98 **** typedef void (*EndForeignModify_function) (EState *estate,
--- 93,110 ----
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);
***************
*** 110,115 **** typedef void (*ExplainForeignModify_function) (ModifyTableState *mtstate,
--- 122,130 ----
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,
***************
*** 162,167 **** typedef struct FdwRoutine
--- 177,186 ----
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;
***************
*** 171,176 **** typedef struct FdwRoutine
--- 190,196 ----
/* Support functions for EXPLAIN */
ExplainForeignScan_function ExplainForeignScan;
ExplainForeignModify_function ExplainForeignModify;
+ ExplainDMLPushdown_function ExplainDMLPushdown;
/* Support functions for ANALYZE */
AnalyzeForeignTable_function AnalyzeForeignTable;
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 311,316 **** typedef struct JunkFilter
--- 311,317 ----
* 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,339 **** typedef struct ResultRelInfo
--- 335,341 ----
Instrumentation *ri_TrigInstrument;
struct FdwRoutine *ri_FdwRoutine;
void *ri_FdwState;
+ bool ri_FdwPushdown;
List *ri_WithCheckOptions;
List *ri_WithCheckOptionExprs;
List **ri_ConstraintExprs;
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 189,194 **** typedef struct ModifyTable
--- 189,195 ----
List *withCheckOptionLists; /* per-target-table WCO lists */
List *returningLists; /* per-target-table RETURNING tlists */
List *fdwPrivLists; /* per-target-table FDW private data lists */
+ List *fdwPushdowns; /* per-target-table FDW pushdown flags */
List *rowMarks; /* PlanRowMarks (non-locking only) */
int epqParam; /* ID of Param for EvalPlanQual re-eval */
OnConflictAction onConflictAction; /* ON CONFLICT action */
***************
*** 531,536 **** typedef struct WorkTableScan
--- 532,538 ----
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 */
*** a/src/include/optimizer/plancat.h
--- b/src/include/optimizer/plancat.h
***************
*** 55,58 **** extern Selectivity join_selectivity(PlannerInfo *root,
--- 55,60 ----
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
