On 2016/01/07 21:45, Etsuro Fujita wrote:
On 2016/01/06 18:58, Rushabh Lathia wrote:
.) Documentation for the new API is missing (fdw-callbacks).
Will add the docs.
I added docs for new FDW APIs.
Other changes:
* Rename relation_has_row_level_triggers to relation_has_row_triggers
shortly, and move it to rewriteHandler.c. I'm not sure rewriteHandler.c
is a good place for that, though.
* Revise code, including a helper function get_result_result, whcih I
implemented using a modified version of store_returning_result in the
previous patch, but on second thought, I think that that is a bit too
invasive. So, I re-implemented that function directly using
make_tuple_from_result_row.
* Add more comments.
* Add more regression tests.
Attached is an updated version of the patch. Comments are wellcome!
(If the fix [1] is okay, I'd like to update this patch on top of the
patch in [1].)
Best regards,
Etsuro Fujita
[1] http://www.postgresql.org/message-id/568f4430.6060...@lab.ntt.co.jp
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 816,822 **** deparseTargetList(StringInfo buf,
*
* If params is not NULL, it receives a list of Params and other-relation Vars
* used in the clauses; these values must be transmitted to the remote server
! * as parameter values.
*
* If params is NULL, we're generating the query for EXPLAIN purposes,
* so Params and other-relation Vars should be replaced by dummy values.
--- 816,822 ----
*
* If params is not NULL, it receives a list of Params and other-relation Vars
* used in the clauses; these values must be transmitted to the remote server
! * as parameter values. Caller is responsible for initializing it to empty.
*
* If params is NULL, we're generating the query for EXPLAIN purposes,
* so Params and other-relation Vars should be replaced by dummy values.
***************
*** 833,841 **** appendWhereClause(StringInfo buf,
int nestlevel;
ListCell *lc;
- if (params)
- *params = NIL; /* initialize result list to empty */
-
/* Set up context struct for recursion */
context.root = root;
context.foreignrel = baserel;
--- 833,838 ----
***************
*** 971,976 **** deparseUpdateSql(StringInfo buf, PlannerInfo *root,
--- 968,1030 ----
}
/*
+ * 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;
+ bool first;
+ ListCell *lc;
+
+ if (params_list)
+ *params_list = NIL; /* initialize result list to empty */
+
+ /* 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 ");
+
+ 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);
+ }
+ if (remote_conds)
+ appendWhereClause(buf, root, baserel, remote_conds,
+ true, params_list);
+
+ 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
***************
*** 993,998 **** deparseDeleteSql(StringInfo buf, PlannerInfo *root,
--- 1047,1082 ----
}
/*
+ * 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];
+
+ if (params_list)
+ *params_list = NIL; /* initialize result list to empty */
+
+ appendStringInfoString(buf, "DELETE FROM ");
+ deparseRelation(buf, rel);
+ if (remote_conds)
+ appendWhereClause(buf, root, baserel, remote_conds,
+ true, params_list);
+
+ 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
***************
*** 2408,2413 **** SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
--- 2425,2498 ----
1104 | 204 | ddd |
(819 rows)
+ INSERT INTO ft2 (c1,c2,c3)
+ VALUES (1201,1201,'aaa'), (1202,1202,'bbb'), (1203,1203,'ccc') RETURNING *;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+ ------+------+-----+----+----+----+------------+----
+ 1201 | 1201 | aaa | | | | ft2 |
+ 1202 | 1202 | bbb | | | | ft2 |
+ 1203 | 1203 | ccc | | | | ft2 |
+ (3 rows)
+
+ INSERT INTO ft2 (c1,c2,c3) VALUES (1204,1204,'ddd'), (1205,1205,'eee');
+ PREPARE mt1(int, int, text) AS UPDATE ft2 SET c2 = c2 + $2, c3 = c3 || $3 WHERE c1 % 10 = $1; -- can be pushed down
+ EXPLAIN (verbose, costs off) EXECUTE mt1(1, 0, '_update1');
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 0), c3 = (c3 || '_update1'::text) WHERE ((("C 1" % 10) = 1))
+ (3 rows)
+
+ EXECUTE mt1(1, 0, '_update1');
+ PREPARE mt2(int) AS DELETE FROM ft2 WHERE c1 = $1 RETURNING c1, c2, c3; -- can be pushed down
+ EXPLAIN (verbose, costs off) EXECUTE mt2(1201);
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------
+ Delete on public.ft2
+ Output: c1, c2, c3
+ -> Foreign Delete on public.ft2
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE (("C 1" = 1201)) RETURNING "C 1", c2, c3
+ (4 rows)
+
+ EXECUTE mt2(1201);
+ c1 | c2 | c3
+ ------+------+-------------
+ 1201 | 1201 | aaa_update1
+ (1 row)
+
+ PREPARE mt3(int) AS DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft2.c2 > $1 RETURNING ft2.*; -- can't be pushed down
+ EXPLAIN (verbose, costs off) EXECUTE mt3(1201);
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------------
+ Delete on public.ft2
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ -> Hash Join
+ Output: ft2.ctid, ft1.*
+ Hash Cond: (ft1.c1 = ft2.c2)
+ -> Foreign Scan on public.ft1
+ Output: ft1.*, ft1.c1
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+ -> Hash
+ Output: ft2.ctid, ft2.c2
+ -> Foreign Scan on public.ft2
+ Output: ft2.ctid, ft2.c2
+ Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" WHERE ((c2 > 1201)) FOR UPDATE
+ (14 rows)
+
+ EXECUTE mt3(1201);
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+ ------+------+-----+----+----+----+------------+----
+ 1202 | 1202 | bbb | | | | ft2 |
+ 1203 | 1203 | ccc | | | | ft2 |
+ 1204 | 1204 | ddd | | | | ft2 |
+ 1205 | 1205 | eee | | | | ft2 |
+ (4 rows)
+
+ DEALLOCATE mt1;
+ DEALLOCATE mt2;
+ DEALLOCATE mt3;
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
BEGIN
***************
*** 2553,2560 **** DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , nul
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
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
--- 2638,2645 ----
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
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_update1_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
***************
*** 2713,2719 **** 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
--- 2798,2804 ----
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
***************
*** 2852,2859 **** DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , nul
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
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);
--- 2937,2944 ----
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
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_update1_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);
***************
*** 3246,3251 **** NOTICE: NEW: (13,"test triggered !")
--- 3331,3529 ----
(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
-- ===================================================================
***************
*** 3715,3720 **** fetch from c;
--- 3993,4048 ----
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
***************
*** 32,37 ****
--- 32,38 ----
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
#include "parser/parsetree.h"
+ #include "rewrite/rewriteHandler.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
***************
*** 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:
--- 58,65 ----
* 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
--- 69,76 ----
{
/* 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
--- 98,125 ----
};
/*
+ * 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
--- 182,217 ----
} PgFdwModifyState;
/*
+ * Execution state of a pushed-down update/delete operation.
+ */
+ 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 scan */
+ 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 contexts */
+ 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,368 ----
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
+ static bool dml_is_pushdown_safe(PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index,
+ Relation rel,
+ List *targetAttrs);
+ 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)
--- 405,419 ----
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;
***************
*** 1067,1073 **** 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);
/*
--- 1145,1152 ----
* 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);
/*
***************
*** 1363,1375 **** 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,
--- 1442,1447 ----
***************
*** 1882,1887 **** postgresIsForeignRelUpdatable(Relation rel)
--- 1954,2274 ----
}
/*
+ * postgresPlanDMLPushdown
+ * Consider pushing down a foreign table modification to the remote server
+ */
+ static bool
+ postgresPlanDMLPushdown(PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index)
+ {
+ CmdType operation = plan->operation;
+ RangeTblEntry *rte = planner_rt_fetch(resultRelation, root);
+ Relation rel;
+ StringInfoData sql;
+ List *targetAttrs = NIL;
+ List *returningList = NIL;
+ List *retrieved_attrs = NIL;
+ ForeignScan *fscan;
+ List *remote_conds;
+ List *params_list = NIL;
+
+ /*
+ * We don't currently support pushing down an insert to the remote server
+ */
+ if (operation == CMD_INSERT)
+ return false;
+
+ 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);
+
+ /*
+ * In an UPDATE, we transmit only columns that were explicitly targets
+ * of the UPDATE, so as to avoid unnecessary data transmission.
+ */
+ if (operation == CMD_UPDATE)
+ {
+ int col;
+
+ col = -1;
+ while ((col = bms_next_member(rte->updatedCols, col)) >= 0)
+ {
+ /* bit numbers are offset by FirstLowInvalidHeapAttributeNumber */
+ AttrNumber attno = col + FirstLowInvalidHeapAttributeNumber;
+
+ if (attno <= InvalidAttrNumber) /* shouldn't happen */
+ elog(ERROR, "system-column update is not supported");
+ targetAttrs = lappend_int(targetAttrs, attno);
+ }
+ }
+
+ /*
+ * Ok, check to see whether the query is safe to push down.
+ */
+ if (!dml_is_pushdown_safe(root, plan,
+ resultRelation,
+ subplan_index,
+ rel, targetAttrs))
+ {
+ heap_close(rel, NoLock);
+ return false;
+ }
+
+ /*
+ * Ok, modify subplan to push down the query.
+ */
+ fscan = (ForeignScan *) list_nth(plan->plans, subplan_index);
+
+ /*
+ * 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));
+
+ /*
+ * RETURNING expressions might reference the tableoid column, so initialize
+ * t_tableOid before evaluating them (see ForeignNext).
+ */
+ Assert(fscan->fsSystemCol);
+
+ 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;
+ ForeignServer *server;
+ 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));
+ server = GetForeignServer(table->serverid);
+ user = GetUserMapping(userid, server->serverid);
+
+ /*
+ * Get connection to the foreign server. Connection manager will
+ * establish new connection if necessary.
+ */
+ dpstate->conn = GetConnection(server, 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;
+
+ /* MemoryContexts will be deleted automatically. */
+ }
+
+ /*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
***************
*** 1919,1924 **** postgresExplainForeignModify(ModifyTableState *mtstate,
--- 2306,2330 ----
}
}
+ /*
+ * 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
***************
*** 2535,2540 **** store_returning_result(PgFdwModifyState *fmstate,
--- 2941,3134 ----
}
/*
+ * Check to see whether it's safe to push down an UPDATE/DELETE to the remote
+ * server
+ *
+ * Conditions checked here:
+ *
+ * 1. If there are any local joins needed, we mustn't push the command down,
+ * because that breaks execution of the joins.
+ *
+ * 2. If there are any quals that can't be evaluated remotely, we mustn't push
+ * the command down, because that breaks evaluation of the quals.
+ *
+ * 3. If the target relation has any row-level local triggers, we mustn't push
+ * the command down, because that breaks execution of the triggers.
+ *
+ * 4. We can't push an UPDATE down, if any expressions to assign to the target
+ * columns are unsafe to evaluate on the remote server.
+ */
+ static bool
+ dml_is_pushdown_safe(PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index,
+ Relation rel,
+ List *targetAttrs)
+ {
+ RelOptInfo *baserel = root->simple_rel_array[resultRelation];
+ Plan *subplan = (Plan *) list_nth(plan->plans, subplan_index);
+ CmdType operation = plan->operation;
+ ListCell *lc;
+
+ Assert(operation == CMD_UPDATE || operation == CMD_DELETE);
+
+ /* Check point 1 */
+ if (nodeTag(subplan) != T_ForeignScan)
+ return false;
+
+ /* Check point 2 */
+ if (subplan->qual != NIL)
+ return false;
+
+ /* Check point 3 */
+ if (relation_has_row_triggers(rel, operation))
+ return false;
+
+ /* Check point 4 */
+ foreach(lc, targetAttrs)
+ {
+ int attnum = lfirst_int(lc);
+ TargetEntry *tle = get_tle_by_resno(subplan->targetlist,
+ attnum);
+
+ if (!is_foreign_expr(root, baserel, (Expr *) tle->expr))
+ return false;
+ }
+
+ return true;
+ }
+
+ /*
+ * Execute the 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. Note that if the local query is of the form
+ * e.g., UPDATE/DELETE .. RETURNING 1, we have has_returning=false, so
+ * just emit a dummy tuple in that case.
+ */
+ if (!dpstate->has_returning)
+ ExecStoreAllNullTuple(slot);
+ else
+ {
+ 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
***************
*** 103,112 **** extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root,
--- 103,126 ----
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,419 **** 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;
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
BEGIN
--- 399,439 ----
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;
+ INSERT INTO ft2 (c1,c2,c3)
+ VALUES (1201,1201,'aaa'), (1202,1202,'bbb'), (1203,1203,'ccc') RETURNING *;
+ INSERT INTO ft2 (c1,c2,c3) VALUES (1204,1204,'ddd'), (1205,1205,'eee');
+ PREPARE mt1(int, int, text) AS UPDATE ft2 SET c2 = c2 + $2, c3 = c3 || $3 WHERE c1 % 10 = $1; -- can be pushed down
+ EXPLAIN (verbose, costs off) EXECUTE mt1(1, 0, '_update1');
+ EXECUTE mt1(1, 0, '_update1');
+ PREPARE mt2(int) AS DELETE FROM ft2 WHERE c1 = $1 RETURNING c1, c2, c3; -- can be pushed down
+ EXPLAIN (verbose, costs off) EXECUTE mt2(1201);
+ EXECUTE mt2(1201);
+ PREPARE mt3(int) AS DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft2.c2 > $1 RETURNING ft2.*; -- can't be pushed down
+ EXPLAIN (verbose, costs off) EXECUTE mt3(1201);
+ EXECUTE mt3(1201);
+ DEALLOCATE mt1;
+ DEALLOCATE mt2;
+ DEALLOCATE mt3;
+
-- Test that trigger on remote table works as expected
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
BEGIN
***************
*** 728,733 **** UPDATE rem1 SET f2 = 'testo';
--- 748,837 ----
-- 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
-- ===================================================================
***************
*** 859,864 **** fetch from c;
--- 963,975 ----
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
***************
*** 664,669 **** IsForeignRelUpdatable (Relation rel);
--- 664,793 ----
updatability for display in the <literal>information_schema</> views.)
</para>
+ <para>
+ <programlisting>
+ bool
+ PlanDMLPushdown (PlannerInfo *root,
+ ModifyTable *plan,
+ Index resultRelation,
+ int subplan_index);
+ </programlisting>
+
+ Check to see whether a foreign table modification is safe to push down
+ to the remote server. If so, return <literal>true</> after performing
+ planning actions needed for that. Otherwise, return <literal>false</>.
+ This optional function is called during query planning.
+ The parameters are the same as for <function>PlanForeignModify</>.
+ If this function succeeds in pushing down the table modification
+ to the remote server, <function>BeginDMLPushdown</>,
+ <function>IterateDMLPushdown</>, and <function>EndDMLPushdown</> will
+ be called at the execution stage, instead. Otherwise, the table
+ modification will be performed using the above table-updating functions.
+ </para>
+
+ <para>
+ To push down the table modification to the remote server, this function
+ must rewrite the target subplan with a <structname>ForeignScan</> plan
+ node that performs the table modification. 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 push down the foreign table modification
+ to the remote server are taken.
+ </para>
+
+ <para>
+ <programlisting>
+ void
+ BeginDMLPushdown (ForeignScanState *node,
+ int eflags);
+ </programlisting>
+
+ Begin pushing down a foreign table modification to the remote server.
+ This routine is called during executor startup. It should perform any
+ initialization needed prior to the actual table modification (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 push down the foreign table modification
+ to the remote server will fail with an error message.
+ </para>
+
+ <para>
+ <programlisting>
+ TupleTableSlot *
+ IterateForeignScan (ForeignScanState *node);
+ </programlisting>
+
+ When the local query has a <literal>RETURNING</> clause, return one row
+ containing the data that was actually inserted, updated, or deleted, in
+ a tuple table slot (the node's <structfield>ScanTupleSlot</> should be
+ used for this purpose). Return NULL if no more rows are available.
+ When the query doesn't have the clause, return NULL. 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 data in the returned slot is used only if the local query has
+ a <literal>RETURNING</> clause. The FDW could choose to optimize away
+ returning some or all columns depending on the contents of the
+ <literal>RETURNING</> clause. Regardless, some slot must be returned to
+ indicate success, or the query's reported row count will be wrong.
+ </para>
+
+ <para>
+ If the <function>IterateDMLPushdown</> pointer is set to
+ <literal>NULL</>, attempts to push down the foreign table modification
+ to 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 push down the foreign table modification
+ to the remote server will fail with an error message.
+ </para>
+
</sect2>
<sect2 id="fdw-callbacks-row-locking">
***************
*** 848,853 **** ExplainForeignModify (ModifyTableState *mtstate,
--- 972,1000 ----
<command>EXPLAIN</>.
</para>
+ <para>
+ <programlisting>
+ void
+ ExplainDMLPushdown (ForeignScanState *node,
+ ExplainState *es);
+ </programlisting>
+
+ Print additional <command>EXPLAIN</> output for a foreign table update
+ that has been pushed down to 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>
--- 1215,1222 ----
<para>
The FDW callback functions <function>GetForeignRelSize</>,
<function>GetForeignPaths</>, <function>GetForeignPlan</>,
! <function>PlanForeignModify</>, <function>PlanDMLPushdown</>, and
! <function>GetForeignJoinPaths</>
must fit into the workings of the <productname>PostgreSQL</> planner.
Here are some notes about what they must do.
</para>
***************
*** 1228,1237 **** 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
table is not scanned so there is no <structname>RelOptInfo</> for it.
The <structname>List</> returned by <function>PlanForeignModify</> has
the same restrictions as the <structfield>fdw_private</> list of a
--- 1376,1386 ----
<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
table is not scanned so there is no <structname>RelOptInfo</> for it.
The <structname>List</> returned by <function>PlanForeignModify</> has
the same restrictions as the <structfield>fdw_private</> list of a
*** 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, or 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";
***************
*** 1624,1629 **** show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es)
--- 1646,1657 ----
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,
***************
*** 2212,2219 **** 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);
}
/*
--- 2240,2255 ----
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);
! }
}
/*
***************
*** 2599,2606 **** 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);
--- 2635,2644 ----
}
}
! /* 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,1020 **** InitPlan(QueryDesc *queryDesc, int eflags)
* CheckValidRowMarkRel.
*/
void
! CheckValidResultRel(Relation resultRel, CmdType operation)
{
TriggerDesc *trigDesc = resultRel->trigdesc;
FdwRoutine *fdwroutine;
switch (resultRel->rd_rel->relkind)
{
--- 1011,1022 ----
* CheckValidRowMarkRel.
*/
void
! CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
{
+ Relation resultRel = resultRelInfo->ri_RelationDesc;
TriggerDesc *trigDesc = resultRel->trigdesc;
FdwRoutine *fdwroutine;
+ bool allow_pushdown;
switch (resultRel->rd_rel->relkind)
{
***************
*** 1083,1096 **** CheckValidResultRel(Relation resultRel, CmdType operation)
case RELKIND_FOREIGN_TABLE:
/* Okay only if the FDW supports it */
fdwroutine = GetFdwRoutineForRelation(resultRel, false);
switch (operation)
{
case CMD_INSERT:
! if (fdwroutine->ExecForeignInsert == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot insert into foreign table \"%s\"",
! RelationGetRelationName(resultRel))));
if (fdwroutine->IsForeignRelUpdatable != NULL &&
(fdwroutine->IsForeignRelUpdatable(resultRel) & (1 << CMD_INSERT)) == 0)
ereport(ERROR,
--- 1085,1107 ----
case RELKIND_FOREIGN_TABLE:
/* Okay only if the FDW supports it */
fdwroutine = GetFdwRoutineForRelation(resultRel, false);
+ allow_pushdown = ((fdwroutine->BeginDMLPushdown != NULL) &&
+ (fdwroutine->IterateDMLPushdown != NULL) &&
+ (fdwroutine->EndDMLPushdown != NULL));
switch (operation)
{
case CMD_INSERT:
! if (resultRelInfo->ri_FdwPushdown && !allow_pushdown)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot push down insert on foreign table \"%s\"",
! RelationGetRelationName(resultRel))));
! if (!resultRelInfo->ri_FdwPushdown &&
! fdwroutine->ExecForeignInsert == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot insert into foreign table \"%s\"",
! RelationGetRelationName(resultRel))));
if (fdwroutine->IsForeignRelUpdatable != NULL &&
(fdwroutine->IsForeignRelUpdatable(resultRel) & (1 << CMD_INSERT)) == 0)
ereport(ERROR,
***************
*** 1099,1105 **** CheckValidResultRel(Relation resultRel, CmdType operation)
RelationGetRelationName(resultRel))));
break;
case CMD_UPDATE:
! if (fdwroutine->ExecForeignUpdate == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot update foreign table \"%s\"",
--- 1110,1122 ----
RelationGetRelationName(resultRel))));
break;
case CMD_UPDATE:
! if (resultRelInfo->ri_FdwPushdown && !allow_pushdown)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot push down update on foreign table \"%s\"",
! RelationGetRelationName(resultRel))));
! if (!resultRelInfo->ri_FdwPushdown &&
! fdwroutine->ExecForeignUpdate == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot update foreign table \"%s\"",
***************
*** 1112,1118 **** CheckValidResultRel(Relation resultRel, CmdType operation)
RelationGetRelationName(resultRel))));
break;
case CMD_DELETE:
! if (fdwroutine->ExecForeignDelete == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot delete from foreign table \"%s\"",
--- 1129,1141 ----
RelationGetRelationName(resultRel))));
break;
case CMD_DELETE:
! if (resultRelInfo->ri_FdwPushdown && !allow_pushdown)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot push down delete on foreign table \"%s\"",
! RelationGetRelationName(resultRel))));
! if (!resultRelInfo->ri_FdwPushdown &&
! fdwroutine->ExecForeignDelete == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot delete from foreign table \"%s\"",
***************
*** 1245,1250 **** InitResultRelInfo(ResultRelInfo *resultRelInfo,
--- 1268,1274 ----
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,143 **** ExecCheckPlanOutput(Relation resultRel, List *targetList)
--- 138,146 ----
* 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 *
***************
*** 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 */
--- 157,166 ----
ResetExprContext(econtext);
/* Make tuple and any needed join variables available to ExecProject */
! if (tupleSlot)
! econtext->ecxt_scantuple = tupleSlot;
! else
! Assert(econtext->ecxt_scantuple);
econtext->ecxt_outertuple = planSlot;
/* Compute the RETURNING expressions */
***************
*** 1357,1362 **** ExecModifyTable(ModifyTableState *node)
--- 1363,1384 ----
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 (see ExecProcessReturning) */
+ slot = ExecProcessReturning(resultRelInfo->ri_projectReturning,
+ NULL, planSlot);
+
+ estate->es_result_relation_info = saved_resultRelInfo;
+ return slot;
+ }
+
EvalPlanQualSetSlot(&node->mt_epqstate, planSlot);
slot = planSlot;
***************
*** 1536,1545 **** 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
--- 1558,1570 ----
{
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
***************
*** 1560,1566 **** 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);
--- 1585,1592 ----
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);
***************
*** 1731,1743 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
--- 1757,1781 ----
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 the command down 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++;
}
}
***************
*** 1798,1803 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
--- 1836,1851 ----
ExecCheckPlanOutput(resultRelInfo->ri_RelationDesc,
subplan->targetlist);
+ /*
+ * ignore subplan if the FDW pushes the command down to the
+ * remote server
+ */
+ if (resultRelInfo->ri_FdwPushdown)
+ {
+ resultRelInfo++;
+ continue;
+ }
+
j = ExecInitJunkFilter(subplan->targetlist,
resultRelInfo->ri_RelationDesc->rd_att->tdhasoid,
ExecInitExtraTupleSlot(estate));
***************
*** 1887,1893 **** 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);
--- 1935,1942 ----
{
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
***************
*** 186,191 **** _copyModifyTable(const ModifyTable *from)
--- 186,192 ----
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);
***************
*** 645,650 **** _copyForeignScan(const ForeignScan *from)
--- 646,652 ----
/*
* 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
***************
*** 340,345 **** _outModifyTable(StringInfo str, const ModifyTable *node)
--- 340,346 ----
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);
***************
*** 591,596 **** _outForeignScan(StringInfo str, const ForeignScan *node)
--- 592,598 ----
_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
***************
*** 1471,1476 **** _readModifyTable(void)
--- 1471,1477 ----
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
***************
*** 3768,3773 **** make_foreignscan(List *qptlist,
--- 3768,3774 ----
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;
***************
*** 5043,5048 **** make_modifytable(PlannerInfo *root,
--- 5044,5050 ----
Plan *plan = &node->plan;
double total_size;
List *fdw_private_list;
+ List *fdwpushdown_list;
ListCell *subnode;
ListCell *lc;
int i;
***************
*** 5123,5134 **** make_modifytable(PlannerInfo *root,
--- 5125,5138 ----
* 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
***************
*** 5156,5161 **** make_modifytable(PlannerInfo *root,
--- 5160,5173 ----
}
if (fdwroutine != NULL &&
+ fdwroutine->PlanDMLPushdown != NULL)
+ 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
***************
*** 5164,5169 **** make_modifytable(PlannerInfo *root,
--- 5176,5182 ----
i++;
}
node->fdwPrivLists = fdw_private_list;
+ node->fdwPushdowns = fdwpushdown_list;
return node;
}
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
***************
*** 2530,2535 **** relation_is_updatable(Oid reloid,
--- 2530,2571 ----
/*
+ * relation_has_row_triggers - does relation have row level triggers for event?
+ */
+ bool
+ relation_has_row_triggers(Relation rel, CmdType event)
+ {
+ TriggerDesc *trigDesc = rel->trigdesc;
+
+ switch (event)
+ {
+ case CMD_INSERT:
+ if (trigDesc &&
+ (trigDesc->trig_insert_after_row ||
+ trigDesc->trig_insert_before_row))
+ return true;
+ break;
+ case CMD_UPDATE:
+ if (trigDesc &&
+ (trigDesc->trig_update_after_row ||
+ trigDesc->trig_update_before_row))
+ return true;
+ break;
+ case CMD_DELETE:
+ if (trigDesc &&
+ (trigDesc->trig_delete_after_row ||
+ trigDesc->trig_delete_before_row))
+ return true;
+ break;
+ default:
+ elog(ERROR, "unrecognized CmdType: %d", (int) event);
+ break;
+ }
+ return false;
+ }
+
+
+ /*
* adjust_view_column_set - map a set of column numbers according to targetlist
*
* This is used with simply-updatable views to map column-permissions sets for
*** 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
***************
*** 188,193 **** typedef struct ModifyTable
--- 188,194 ----
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 */
***************
*** 530,535 **** typedef struct WorkTableScan
--- 531,537 ----
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/rewrite/rewriteHandler.h
--- b/src/include/rewrite/rewriteHandler.h
***************
*** 29,33 **** extern const char *view_query_is_auto_updatable(Query *viewquery,
--- 29,34 ----
extern int relation_is_updatable(Oid reloid,
bool include_triggers,
Bitmapset *include_cols);
+ extern bool relation_has_row_triggers(Relation rel, CmdType event);
#endif /* REWRITEHANDLER_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
