From e690b9515072fd7767fdeca5c54166f6a77733bc Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 22 Mar 2013 00:31:11 -0400 Subject: [PATCH] Avoid retrieving dummy NULL columns in postgres_fdw. This should provide some marginal overall savings, since it surely takes many more cycles for the remote server to deal with the NULL columns than it takes for postgres_fdw not to emit them. But really the reason is to keep the emitted queries from looking quite so silly ... --- contrib/postgres_fdw/deparse.c | 95 +++++-- .../postgres_fdw/expected/postgres_fdw.out | 250 +++++++++--------- contrib/postgres_fdw/postgres_fdw.c | 155 ++++++----- contrib/postgres_fdw/postgres_fdw.h | 15 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 4 +- 5 files changed, 301 insertions(+), 218 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 8d05b19934..a03eec3c82 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -106,10 +106,12 @@ static void deparseTargetList(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - Bitmapset *attrs_used); + Bitmapset *attrs_used, + List **retrieved_attrs); static void deparseReturningList(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *returningList); + List *returningList, + List **retrieved_attrs); static void deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root); static void deparseRelation(StringInfo buf, Relation rel); @@ -652,12 +654,16 @@ is_builtin(Oid oid) * Construct a simple SELECT statement that retrieves desired columns * of the specified foreign table, and append it to "buf". The output * contains just "SELECT ... FROM tablename". + * + * We also create an integer List of the columns being retrieved, which is + * returned to *retrieved_attrs. */ void deparseSelectSql(StringInfo buf, PlannerInfo *root, RelOptInfo *baserel, - Bitmapset *attrs_used) + Bitmapset *attrs_used, + List **retrieved_attrs) { RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root); Relation rel; @@ -672,7 +678,8 @@ deparseSelectSql(StringInfo buf, * Construct SELECT list */ appendStringInfoString(buf, "SELECT "); - deparseTargetList(buf, root, baserel->relid, rel, attrs_used); + deparseTargetList(buf, root, baserel->relid, rel, attrs_used, + retrieved_attrs); /* * Construct FROM clause @@ -687,24 +694,24 @@ deparseSelectSql(StringInfo buf, * Emit a target list that retrieves the columns specified in attrs_used. * This is used for both SELECT and RETURNING targetlists. * - * We list attributes in order of the foreign table's columns, but replace - * any attributes that need not be fetched with NULL constants. (We can't - * just omit such attributes, or we'll lose track of which columns are - * which at runtime.) Note however that any dropped columns are ignored. - * Also, if ctid needs to be retrieved, it's added at the end. + * 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. */ static void deparseTargetList(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - Bitmapset *attrs_used) + Bitmapset *attrs_used, + List **retrieved_attrs) { TupleDesc tupdesc = RelationGetDescr(rel); bool have_wholerow; bool first; int i; + *retrieved_attrs = NIL; + /* If there's a whole-row reference, we'll need all the columns. */ have_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, attrs_used); @@ -718,16 +725,18 @@ deparseTargetList(StringInfo buf, if (attr->attisdropped) continue; - if (!first) - appendStringInfoString(buf, ", "); - first = false; - if (have_wholerow || bms_is_member(i - FirstLowInvalidHeapAttributeNumber, attrs_used)) + { + if (!first) + appendStringInfoString(buf, ", "); + first = false; + deparseColumnRef(buf, rtindex, i, root); - else - appendStringInfoString(buf, "NULL"); + + *retrieved_attrs = lappend_int(*retrieved_attrs, i); + } } /* @@ -742,6 +751,9 @@ deparseTargetList(StringInfo buf, first = false; appendStringInfoString(buf, "ctid"); + + *retrieved_attrs = lappend_int(*retrieved_attrs, + SelfItemPointerAttributeNumber); } /* Don't generate bad syntax if no undropped columns */ @@ -809,11 +821,16 @@ appendWhereClause(StringInfo buf, /* * deparse remote INSERT 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 deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList) + List *targetAttrs, List *returningList, + List **retrieved_attrs) { AttrNumber pindex; bool first; @@ -858,16 +875,24 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root, appendStringInfoString(buf, " DEFAULT VALUES"); if (returningList) - deparseReturningList(buf, root, rtindex, rel, returningList); + deparseReturningList(buf, root, rtindex, rel, returningList, + retrieved_attrs); + else + *retrieved_attrs = NIL; } /* * 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 deparseUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList) + List *targetAttrs, List *returningList, + List **retrieved_attrs) { AttrNumber pindex; bool first; @@ -894,23 +919,34 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root, appendStringInfoString(buf, " WHERE ctid = $1"); if (returningList) - deparseReturningList(buf, root, rtindex, rel, returningList); + deparseReturningList(buf, root, rtindex, rel, returningList, + retrieved_attrs); + else + *retrieved_attrs = NIL; } /* * 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 deparseDeleteSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *returningList) + List *returningList, + List **retrieved_attrs) { appendStringInfoString(buf, "DELETE FROM "); deparseRelation(buf, rel); appendStringInfoString(buf, " WHERE ctid = $1"); if (returningList) - deparseReturningList(buf, root, rtindex, rel, returningList); + deparseReturningList(buf, root, rtindex, rel, returningList, + retrieved_attrs); + else + *retrieved_attrs = NIL; } /* @@ -919,7 +955,8 @@ deparseDeleteSql(StringInfo buf, PlannerInfo *root, static void deparseReturningList(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *returningList) + List *returningList, + List **retrieved_attrs) { Bitmapset *attrs_used; @@ -931,7 +968,8 @@ deparseReturningList(StringInfo buf, PlannerInfo *root, &attrs_used); appendStringInfoString(buf, " RETURNING "); - deparseTargetList(buf, root, rtindex, rel, attrs_used); + deparseTargetList(buf, root, rtindex, rel, attrs_used, + retrieved_attrs); } /* @@ -959,10 +997,11 @@ deparseAnalyzeSizeSql(StringInfo buf, Relation rel) /* * Construct SELECT statement to acquire sample rows of given relation. * - * Note: command is appended to whatever might be in buf already. + * SELECT command is appended to buf, and list of columns retrieved + * is returned to *retrieved_attrs. */ void -deparseAnalyzeSql(StringInfo buf, Relation rel) +deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs) { Oid relid = RelationGetRelid(rel); TupleDesc tupdesc = RelationGetDescr(rel); @@ -972,6 +1011,8 @@ deparseAnalyzeSql(StringInfo buf, Relation rel) ListCell *lc; bool first = true; + *retrieved_attrs = NIL; + appendStringInfoString(buf, "SELECT "); for (i = 0; i < tupdesc->natts; i++) { @@ -999,6 +1040,8 @@ deparseAnalyzeSql(StringInfo buf, Relation rel) } appendStringInfoString(buf, quote_identifier(colname)); + + *retrieved_attrs = lappend_int(*retrieved_attrs, i + 1); } /* Don't generate bad syntax for zero-column relation. */ diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 3909ef8b58..cb007cd58d 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -473,16 +473,16 @@ SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2; -- simple join PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2); - QUERY PLAN --------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- Nested Loop Output: t1.c3, t2.c3 -> Foreign Scan on public.ft1 t1 Output: t1.c3 - Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 1)) + Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 1)) -> Foreign Scan on public.ft2 t2 Output: t2.c3 - Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 2)) + Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 2)) (8 rows) EXECUTE st1(1, 1); @@ -500,8 +500,8 @@ EXECUTE st1(101, 101); -- subquery using stable function (can't be sent to remote) PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c4) = '1970-01-17'::date) ORDER BY c1; EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Sort Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Sort Key: t1.c1 @@ -516,7 +516,7 @@ EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20); -> Foreign Scan on public.ft2 t2 Output: t2.c3 Filter: (date(t2.c4) = '01-17-1970'::date) - Remote SQL: SELECT NULL, NULL, c3, c4, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10)) + Remote SQL: SELECT c3, c4 FROM "S 1"."T 1" WHERE (("C 1" > 10)) (15 rows) EXECUTE st2(10, 20); @@ -534,8 +534,8 @@ EXECUTE st2(101, 121); -- subquery using immutable function (can be sent to remote) PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1; EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Sort Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Sort Key: t1.c1 @@ -549,7 +549,7 @@ EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20); Output: t2.c3 -> Foreign Scan on public.ft2 t2 Output: t2.c3 - Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10)) AND ((date(c5) = '1970-01-17'::date)) + Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" > 10)) AND ((date(c5) = '1970-01-17'::date)) (14 rows) EXECUTE st3(10, 20); @@ -834,7 +834,7 @@ INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; Output: ((ft2_1.c1 + 1000)), ((ft2_1.c2 + 100)), ((ft2_1.c3 || ft2_1.c3)) -> Foreign Scan on public.ft2 ft2_1 Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3) - Remote SQL: SELECT "C 1", c2, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" + Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" (9 rows) INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20; @@ -968,7 +968,7 @@ UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT Hash Cond: (ft2.c2 = ft1.c1) -> Foreign Scan on public.ft2 Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, NULL, c8, ctid FROM "S 1"."T 1" FOR UPDATE + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" FOR UPDATE -> Hash Output: ft1.*, ft1.c1 -> Foreign Scan on public.ft1 @@ -978,112 +978,124 @@ 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; -DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING *; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+------------+------------------------------+--------------------------+----+------------+----- - 5 | 5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 15 | 5 | 00015 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 25 | 5 | 00025 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 35 | 5 | 00035 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 45 | 5 | 00045 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 55 | 5 | 00055 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 65 | 5 | 00065 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 75 | 5 | 00075 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 85 | 5 | 00085 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 95 | 5 | 00095 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 105 | 5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 115 | 5 | 00115 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 125 | 5 | 00125 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 135 | 5 | 00135 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 145 | 5 | 00145 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 155 | 5 | 00155 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 165 | 5 | 00165 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 175 | 5 | 00175 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 185 | 5 | 00185 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 195 | 5 | 00195 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 205 | 5 | 00205 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 215 | 5 | 00215 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 225 | 5 | 00225 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 235 | 5 | 00235 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 245 | 5 | 00245 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 255 | 5 | 00255 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 265 | 5 | 00265 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 275 | 5 | 00275 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 285 | 5 | 00285 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 295 | 5 | 00295 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 305 | 5 | 00305 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 315 | 5 | 00315 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 325 | 5 | 00325 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 335 | 5 | 00335 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 345 | 5 | 00345 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 355 | 5 | 00355 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 365 | 5 | 00365 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 375 | 5 | 00375 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 385 | 5 | 00385 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 395 | 5 | 00395 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 405 | 5 | 00405 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 415 | 5 | 00415 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 425 | 5 | 00425 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 435 | 5 | 00435 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 445 | 5 | 00445 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 455 | 5 | 00455 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 465 | 5 | 00465 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 475 | 5 | 00475 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 485 | 5 | 00485 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 495 | 5 | 00495 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 505 | 5 | 00505 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 515 | 5 | 00515 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 525 | 5 | 00525 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 535 | 5 | 00535 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 545 | 5 | 00545 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 555 | 5 | 00555 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 565 | 5 | 00565 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 575 | 5 | 00575 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 585 | 5 | 00585 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 595 | 5 | 00595 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 605 | 5 | 00605 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 615 | 5 | 00615 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 625 | 5 | 00625 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 635 | 5 | 00635 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 645 | 5 | 00645 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 655 | 5 | 00655 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 665 | 5 | 00665 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 675 | 5 | 00675 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 685 | 5 | 00685 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 695 | 5 | 00695 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 705 | 5 | 00705 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 715 | 5 | 00715 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 725 | 5 | 00725 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 735 | 5 | 00735 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 745 | 5 | 00745 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 755 | 5 | 00755 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 765 | 5 | 00765 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 775 | 5 | 00775 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 785 | 5 | 00785 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 795 | 5 | 00795 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 805 | 5 | 00805 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 815 | 5 | 00815 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 825 | 5 | 00825 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 835 | 5 | 00835 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 845 | 5 | 00845 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 855 | 5 | 00855 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 865 | 5 | 00865 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 875 | 5 | 00875 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 885 | 5 | 00885 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 895 | 5 | 00895 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 905 | 5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo - 915 | 5 | 00915 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo - 925 | 5 | 00925 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo - 935 | 5 | 00935 | Thu Feb 05 00:00:00 1970 PST | Thu Feb 05 00:00:00 1970 | 5 | 5 | foo - 945 | 5 | 00945 | Sun Feb 15 00:00:00 1970 PST | Sun Feb 15 00:00:00 1970 | 5 | 5 | foo - 955 | 5 | 00955 | Wed Feb 25 00:00:00 1970 PST | Wed Feb 25 00:00:00 1970 | 5 | 5 | foo - 965 | 5 | 00965 | Sat Mar 07 00:00:00 1970 PST | Sat Mar 07 00:00:00 1970 | 5 | 5 | foo - 975 | 5 | 00975 | Tue Mar 17 00:00:00 1970 PST | Tue Mar 17 00:00:00 1970 | 5 | 5 | foo - 985 | 5 | 00985 | Fri Mar 27 00:00:00 1970 PST | Fri Mar 27 00:00:00 1970 | 5 | 5 | foo - 995 | 5 | 00995 | Mon Apr 06 00:00:00 1970 PST | Mon Apr 06 00:00:00 1970 | 5 | 5 | foo - 1005 | 105 | 0000500005 | | | | ft2 | - 1015 | 105 | 0001500015 | | | | ft2 | - 1105 | 205 | eee | | | | ft2 | +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 +------+------------------------------ + 5 | Tue Jan 06 00:00:00 1970 PST + 15 | Fri Jan 16 00:00:00 1970 PST + 25 | Mon Jan 26 00:00:00 1970 PST + 35 | Thu Feb 05 00:00:00 1970 PST + 45 | Sun Feb 15 00:00:00 1970 PST + 55 | Wed Feb 25 00:00:00 1970 PST + 65 | Sat Mar 07 00:00:00 1970 PST + 75 | Tue Mar 17 00:00:00 1970 PST + 85 | Fri Mar 27 00:00:00 1970 PST + 95 | Mon Apr 06 00:00:00 1970 PST + 105 | Tue Jan 06 00:00:00 1970 PST + 115 | Fri Jan 16 00:00:00 1970 PST + 125 | Mon Jan 26 00:00:00 1970 PST + 135 | Thu Feb 05 00:00:00 1970 PST + 145 | Sun Feb 15 00:00:00 1970 PST + 155 | Wed Feb 25 00:00:00 1970 PST + 165 | Sat Mar 07 00:00:00 1970 PST + 175 | Tue Mar 17 00:00:00 1970 PST + 185 | Fri Mar 27 00:00:00 1970 PST + 195 | Mon Apr 06 00:00:00 1970 PST + 205 | Tue Jan 06 00:00:00 1970 PST + 215 | Fri Jan 16 00:00:00 1970 PST + 225 | Mon Jan 26 00:00:00 1970 PST + 235 | Thu Feb 05 00:00:00 1970 PST + 245 | Sun Feb 15 00:00:00 1970 PST + 255 | Wed Feb 25 00:00:00 1970 PST + 265 | Sat Mar 07 00:00:00 1970 PST + 275 | Tue Mar 17 00:00:00 1970 PST + 285 | Fri Mar 27 00:00:00 1970 PST + 295 | Mon Apr 06 00:00:00 1970 PST + 305 | Tue Jan 06 00:00:00 1970 PST + 315 | Fri Jan 16 00:00:00 1970 PST + 325 | Mon Jan 26 00:00:00 1970 PST + 335 | Thu Feb 05 00:00:00 1970 PST + 345 | Sun Feb 15 00:00:00 1970 PST + 355 | Wed Feb 25 00:00:00 1970 PST + 365 | Sat Mar 07 00:00:00 1970 PST + 375 | Tue Mar 17 00:00:00 1970 PST + 385 | Fri Mar 27 00:00:00 1970 PST + 395 | Mon Apr 06 00:00:00 1970 PST + 405 | Tue Jan 06 00:00:00 1970 PST + 415 | Fri Jan 16 00:00:00 1970 PST + 425 | Mon Jan 26 00:00:00 1970 PST + 435 | Thu Feb 05 00:00:00 1970 PST + 445 | Sun Feb 15 00:00:00 1970 PST + 455 | Wed Feb 25 00:00:00 1970 PST + 465 | Sat Mar 07 00:00:00 1970 PST + 475 | Tue Mar 17 00:00:00 1970 PST + 485 | Fri Mar 27 00:00:00 1970 PST + 495 | Mon Apr 06 00:00:00 1970 PST + 505 | Tue Jan 06 00:00:00 1970 PST + 515 | Fri Jan 16 00:00:00 1970 PST + 525 | Mon Jan 26 00:00:00 1970 PST + 535 | Thu Feb 05 00:00:00 1970 PST + 545 | Sun Feb 15 00:00:00 1970 PST + 555 | Wed Feb 25 00:00:00 1970 PST + 565 | Sat Mar 07 00:00:00 1970 PST + 575 | Tue Mar 17 00:00:00 1970 PST + 585 | Fri Mar 27 00:00:00 1970 PST + 595 | Mon Apr 06 00:00:00 1970 PST + 605 | Tue Jan 06 00:00:00 1970 PST + 615 | Fri Jan 16 00:00:00 1970 PST + 625 | Mon Jan 26 00:00:00 1970 PST + 635 | Thu Feb 05 00:00:00 1970 PST + 645 | Sun Feb 15 00:00:00 1970 PST + 655 | Wed Feb 25 00:00:00 1970 PST + 665 | Sat Mar 07 00:00:00 1970 PST + 675 | Tue Mar 17 00:00:00 1970 PST + 685 | Fri Mar 27 00:00:00 1970 PST + 695 | Mon Apr 06 00:00:00 1970 PST + 705 | Tue Jan 06 00:00:00 1970 PST + 715 | Fri Jan 16 00:00:00 1970 PST + 725 | Mon Jan 26 00:00:00 1970 PST + 735 | Thu Feb 05 00:00:00 1970 PST + 745 | Sun Feb 15 00:00:00 1970 PST + 755 | Wed Feb 25 00:00:00 1970 PST + 765 | Sat Mar 07 00:00:00 1970 PST + 775 | Tue Mar 17 00:00:00 1970 PST + 785 | Fri Mar 27 00:00:00 1970 PST + 795 | Mon Apr 06 00:00:00 1970 PST + 805 | Tue Jan 06 00:00:00 1970 PST + 815 | Fri Jan 16 00:00:00 1970 PST + 825 | Mon Jan 26 00:00:00 1970 PST + 835 | Thu Feb 05 00:00:00 1970 PST + 845 | Sun Feb 15 00:00:00 1970 PST + 855 | Wed Feb 25 00:00:00 1970 PST + 865 | Sat Mar 07 00:00:00 1970 PST + 875 | Tue Mar 17 00:00:00 1970 PST + 885 | Fri Mar 27 00:00:00 1970 PST + 895 | Mon Apr 06 00:00:00 1970 PST + 905 | Tue Jan 06 00:00:00 1970 PST + 915 | Fri Jan 16 00:00:00 1970 PST + 925 | Mon Jan 26 00:00:00 1970 PST + 935 | Thu Feb 05 00:00:00 1970 PST + 945 | Sun Feb 15 00:00:00 1970 PST + 955 | Wed Feb 25 00:00:00 1970 PST + 965 | Sat Mar 07 00:00:00 1970 PST + 975 | Tue Mar 17 00:00:00 1970 PST + 985 | Fri Mar 27 00:00:00 1970 PST + 995 | Mon Apr 06 00:00:00 1970 PST + 1005 | + 1015 | + 1105 | (103 rows) EXPLAIN (verbose, costs off) @@ -1097,7 +1109,7 @@ DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; Hash Cond: (ft2.c2 = ft1.c1) -> Foreign Scan on public.ft2 Output: ft2.ctid, ft2.c2 - Remote SQL: SELECT NULL, c2, NULL, NULL, NULL, NULL, NULL, NULL, ctid FROM "S 1"."T 1" FOR UPDATE + Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE -> Hash Output: ft1.*, ft1.c1 -> Foreign Scan on public.ft1 diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 982a8d9a61..687b87b860 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -84,9 +84,10 @@ typedef struct PgFdwRelationInfo * Indexes of FDW-private information stored in fdw_private lists. * * We store various information in ForeignScan.fdw_private to pass it from - * planner to executor. Currently there is just: + * 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: @@ -95,7 +96,9 @@ typedef struct PgFdwRelationInfo enum FdwScanPrivateIndex { /* SQL statement to execute remotely (as a String node) */ - FdwScanPrivateSelectSql + FdwScanPrivateSelectSql, + /* Integer list of attribute numbers retrieved by the SELECT */ + FdwScanPrivateRetrievedAttrs }; /* @@ -106,6 +109,7 @@ enum FdwScanPrivateIndex * 2) Integer list of target attribute numbers for INSERT/UPDATE * (NIL for a DELETE) * 3) Boolean flag showing if there's a RETURNING clause + * 4) Integer list of attribute numbers retrieved by RETURNING, if any */ enum FdwModifyPrivateIndex { @@ -114,7 +118,9 @@ enum FdwModifyPrivateIndex /* Integer list of target attribute numbers for INSERT/UPDATE */ FdwModifyPrivateTargetAttnums, /* has-returning flag (as an integer Value node) */ - FdwModifyPrivateHasReturning + FdwModifyPrivateHasReturning, + /* Integer list of attribute numbers retrieved by RETURNING */ + FdwModifyPrivateRetrievedAttrs }; /* @@ -125,7 +131,9 @@ typedef struct PgFdwScanState Relation rel; /* relcache entry for the foreign table */ AttInMetadata *attinmeta; /* attribute datatype conversion metadata */ - List *fdw_private; /* FDW-private information from planner */ + /* extracted fdw_private data */ + char *query; /* text of SELECT command */ + List *retrieved_attrs; /* list of retrieved attribute numbers */ /* for remote query execution */ PGconn *conn; /* connection for the scan */ @@ -166,6 +174,7 @@ typedef struct PgFdwModifyState char *query; /* text of INSERT/UPDATE/DELETE command */ List *target_attrs; /* list of target attribute numbers */ bool has_returning; /* is there a RETURNING clause? */ + List *retrieved_attrs; /* attr numbers retrieved by RETURNING */ /* info about parameters for prepared statement */ AttrNumber ctidAttno; /* attnum of input resjunk ctid column */ @@ -183,6 +192,7 @@ typedef struct PgFdwAnalyzeState { Relation rel; /* relcache entry for the foreign table */ AttInMetadata *attinmeta; /* attribute datatype conversion metadata */ + List *retrieved_attrs; /* attr numbers retrieved by query */ /* collected sample rows */ HeapTuple *rows; /* array of size targrows */ @@ -314,6 +324,7 @@ static HeapTuple make_tuple_from_result_row(PGresult *res, int row, Relation rel, AttInMetadata *attinmeta, + List *retrieved_attrs, MemoryContext temp_context); static void conversion_error_callback(void *arg); @@ -728,6 +739,7 @@ postgresGetForeignPlan(PlannerInfo *root, List *remote_conds = NIL; List *local_exprs = NIL; List *params_list = NIL; + List *retrieved_attrs; StringInfoData sql; ListCell *lc; @@ -777,7 +789,8 @@ postgresGetForeignPlan(PlannerInfo *root, * expressions to be sent as parameters. */ initStringInfo(&sql); - deparseSelectSql(&sql, root, baserel, fpinfo->attrs_used); + deparseSelectSql(&sql, root, baserel, fpinfo->attrs_used, + &retrieved_attrs); if (remote_conds) appendWhereClause(&sql, root, baserel, remote_conds, true, ¶ms_list); @@ -829,7 +842,8 @@ 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_make1(makeString(sql.data)); + fdw_private = list_make2(makeString(sql.data), + retrieved_attrs); /* * Create the ForeignScan node from target list, local filtering @@ -901,7 +915,10 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) fsstate->cursor_exists = false; /* Get private info created by planner functions. */ - fsstate->fdw_private = fsplan->fdw_private; + fsstate->query = strVal(list_nth(fsplan->fdw_private, + FdwScanPrivateSelectSql)); + fsstate->retrieved_attrs = (List *) list_nth(fsplan->fdw_private, + FdwScanPrivateRetrievedAttrs); /* Create contexts for batches of tuples and per-tuple temp workspace. */ fsstate->batch_cxt = AllocSetContextCreate(estate->es_query_cxt, @@ -915,7 +932,7 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) ALLOCSET_SMALL_INITSIZE, ALLOCSET_SMALL_MAXSIZE); - /* Get info we'll need for data conversion. */ + /* Get info we'll need for input data conversion. */ fsstate->attinmeta = TupleDescGetAttInMetadata(RelationGetDescr(fsstate->rel)); /* Prepare for output conversion of parameters used in remote query. */ @@ -1138,6 +1155,7 @@ postgresPlanForeignModify(PlannerInfo *root, StringInfoData sql; List *targetAttrs = NIL; List *returningList = NIL; + List *retrieved_attrs = NIL; initStringInfo(&sql); @@ -1194,15 +1212,18 @@ postgresPlanForeignModify(PlannerInfo *root, { case CMD_INSERT: deparseInsertSql(&sql, root, resultRelation, rel, - targetAttrs, returningList); + targetAttrs, returningList, + &retrieved_attrs); break; case CMD_UPDATE: deparseUpdateSql(&sql, root, resultRelation, rel, - targetAttrs, returningList); + targetAttrs, returningList, + &retrieved_attrs); break; case CMD_DELETE: deparseDeleteSql(&sql, root, resultRelation, rel, - returningList); + returningList, + &retrieved_attrs); break; default: elog(ERROR, "unexpected operation: %d", (int) operation); @@ -1215,9 +1236,10 @@ postgresPlanForeignModify(PlannerInfo *root, * Build the fdw_private list that will be available to the executor. * Items in the list must match enum FdwModifyPrivateIndex, above. */ - return list_make3(makeString(sql.data), + return list_make4(makeString(sql.data), targetAttrs, - makeInteger((returningList != NIL))); + makeInteger((returningList != NIL)), + retrieved_attrs); } /* @@ -1279,6 +1301,8 @@ postgresBeginForeignModify(ModifyTableState *mtstate, FdwModifyPrivateTargetAttnums); fmstate->has_returning = intVal(list_nth(fdw_private, FdwModifyPrivateHasReturning)); + fmstate->retrieved_attrs = (List *) list_nth(fdw_private, + FdwModifyPrivateRetrievedAttrs); /* Create context for per-tuple temp workspace. */ fmstate->temp_cxt = AllocSetContextCreate(estate->es_query_cxt, @@ -1641,6 +1665,7 @@ estimate_path_cost_size(PlannerInfo *root, if (fpinfo->use_remote_estimate) { StringInfoData sql; + List *retrieved_attrs; PGconn *conn; /* @@ -1650,7 +1675,8 @@ estimate_path_cost_size(PlannerInfo *root, */ initStringInfo(&sql); appendStringInfoString(&sql, "EXPLAIN "); - deparseSelectSql(&sql, root, baserel, fpinfo->attrs_used); + deparseSelectSql(&sql, root, baserel, fpinfo->attrs_used, + &retrieved_attrs); if (fpinfo->remote_conds) appendWhereClause(&sql, root, baserel, fpinfo->remote_conds, true, NULL); @@ -1819,7 +1845,6 @@ create_cursor(ForeignScanState *node) int numParams = fsstate->numParams; const char **values = fsstate->param_values; PGconn *conn = fsstate->conn; - char *sql; StringInfoData buf; PGresult *res; @@ -1867,10 +1892,9 @@ create_cursor(ForeignScanState *node) } /* Construct the DECLARE CURSOR command */ - sql = strVal(list_nth(fsstate->fdw_private, FdwScanPrivateSelectSql)); initStringInfo(&buf); appendStringInfo(&buf, "DECLARE c%u CURSOR FOR\n%s", - fsstate->cursor_number, sql); + fsstate->cursor_number, fsstate->query); /* * Notice that we pass NULL for paramTypes, thus forcing the remote server @@ -1885,7 +1909,7 @@ create_cursor(ForeignScanState *node) res = PQexecParams(conn, buf.data, numParams, NULL, values, NULL, NULL, 0); if (PQresultStatus(res) != PGRES_COMMAND_OK) - pgfdw_report_error(ERROR, res, true, sql); + pgfdw_report_error(ERROR, res, true, fsstate->query); PQclear(res); /* Mark the cursor as created, and show no tuples have been retrieved */ @@ -1936,9 +1960,7 @@ fetch_more_data(ForeignScanState *node) res = PQexec(conn, sql); /* On error, report the original query, not the FETCH. */ if (PQresultStatus(res) != PGRES_TUPLES_OK) - pgfdw_report_error(ERROR, res, false, - strVal(list_nth(fsstate->fdw_private, - FdwScanPrivateSelectSql))); + pgfdw_report_error(ERROR, res, false, fsstate->query); /* Convert the data into HeapTuples */ numrows = PQntuples(res); @@ -1952,6 +1974,7 @@ fetch_more_data(ForeignScanState *node) make_tuple_from_result_row(res, i, fsstate->rel, fsstate->attinmeta, + fsstate->retrieved_attrs, fsstate->temp_cxt); } @@ -2170,6 +2193,7 @@ store_returning_result(PgFdwModifyState *fmstate, newtup = make_tuple_from_result_row(res, 0, fmstate->rel, fmstate->attinmeta, + fmstate->retrieved_attrs, fmstate->temp_cxt); /* tuple will be deleted when it is cleared from the slot */ ExecStoreTuple(newtup, slot, InvalidBuffer, true); @@ -2316,7 +2340,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel, cursor_number = GetCursorNumber(conn); initStringInfo(&sql); appendStringInfo(&sql, "DECLARE c%u CURSOR FOR ", cursor_number); - deparseAnalyzeSql(&sql, relation); + deparseAnalyzeSql(&sql, relation, &astate.retrieved_attrs); /* In what follows, do not risk leaking any PGresults. */ PG_TRY(); @@ -2461,6 +2485,7 @@ analyze_row_processor(PGresult *res, int row, PgFdwAnalyzeState *astate) astate->rows[pos] = make_tuple_from_result_row(res, row, astate->rel, astate->attinmeta, + astate->retrieved_attrs, astate->temp_cxt); MemoryContextSwitchTo(oldcontext); @@ -2471,26 +2496,27 @@ analyze_row_processor(PGresult *res, int row, PgFdwAnalyzeState *astate) * Create a tuple from the specified row of the PGresult. * * rel is the local representation of the foreign table, attinmeta is - * conversion data for the rel's tupdesc, and temp_context is a working - * context that can be reset after each tuple. + * conversion data for the rel's tupdesc, and retrieved_attrs is an + * integer list of the table column numbers present in the PGresult. + * temp_context is a working context that can be reset after each tuple. */ static HeapTuple make_tuple_from_result_row(PGresult *res, int row, Relation rel, AttInMetadata *attinmeta, + List *retrieved_attrs, MemoryContext temp_context) { HeapTuple tuple; TupleDesc tupdesc = RelationGetDescr(rel); - Form_pg_attribute *attrs = tupdesc->attrs; Datum *values; bool *nulls; ItemPointer ctid = NULL; ConversionLocation errpos; ErrorContextCallback errcallback; MemoryContext oldcontext; - int i; + ListCell *lc; int j; Assert(row < PQntuples(res)); @@ -2502,8 +2528,10 @@ make_tuple_from_result_row(PGresult *res, */ oldcontext = MemoryContextSwitchTo(temp_context); - values = (Datum *) palloc(tupdesc->natts * sizeof(Datum)); + values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum)); nulls = (bool *) palloc(tupdesc->natts * sizeof(bool)); + /* Initialize to nulls for any columns not present in result */ + memset(nulls, true, tupdesc->natts * sizeof(bool)); /* * Set up and install callback to report where conversion error occurs. @@ -2517,63 +2545,56 @@ make_tuple_from_result_row(PGresult *res, /* * i indexes columns in the relation, j indexes columns in the PGresult. - * We assume dropped columns are not represented in the PGresult. */ - for (i = 0, j = 0; i < tupdesc->natts; i++) + j = 0; + foreach(lc, retrieved_attrs) { + int i = lfirst_int(lc); char *valstr; - /* skip dropped columns. */ - if (attrs[i]->attisdropped) - { - values[i] = (Datum) 0; - nulls[i] = true; - continue; - } - - /* convert value to internal representation */ + /* fetch next column's textual value */ if (PQgetisnull(res, row, j)) - { valstr = NULL; - nulls[i] = true; - } else - { valstr = PQgetvalue(res, row, j); - nulls[i] = false; - } - - /* Note: apply the input function even to nulls, to support domains */ - errpos.cur_attno = i + 1; - values[i] = InputFunctionCall(&attinmeta->attinfuncs[i], - valstr, - attinmeta->attioparams[i], - attinmeta->atttypmods[i]); - errpos.cur_attno = 0; - j++; - } + /* convert value to internal representation */ + if (i > 0) + { + /* ordinary column */ + Assert(i <= tupdesc->natts); + nulls[i - 1] = (valstr == NULL); + /* Apply the input function even to nulls, to support domains */ + errpos.cur_attno = i; + values[i - 1] = InputFunctionCall(&attinmeta->attinfuncs[i - 1], + valstr, + attinmeta->attioparams[i - 1], + attinmeta->atttypmods[i - 1]); + errpos.cur_attno = 0; + } + else if (i == SelfItemPointerAttributeNumber) + { + /* ctid --- note we ignore any other system column in result */ + if (valstr != NULL) + { + Datum datum; - /* - * Convert ctid if present. XXX we could stand to have a cleaner way of - * detecting whether ctid is included in the result. - */ - if (j < PQnfields(res)) - { - char *valstr; - Datum datum; + datum = DirectFunctionCall1(tidin, CStringGetDatum(valstr)); + ctid = (ItemPointer) DatumGetPointer(datum); + } + } - valstr = PQgetvalue(res, row, j); - datum = DirectFunctionCall1(tidin, CStringGetDatum(valstr)); - ctid = (ItemPointer) DatumGetPointer(datum); j++; } /* Uninstall error context callback. */ error_context_stack = errcallback.previous; - /* check result and tuple descriptor have the same number of columns */ - if (j != PQnfields(res)) + /* + * Check we got the expected number of columns. Note: j == 0 and + * PQnfields == 1 is expected, since deparse emits a NULL if no columns. + */ + if (j > 0 && j != PQnfields(res)) elog(ERROR, "remote query result does not match the foreign table"); /* diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 78a57ea057..2939d2b61d 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -49,7 +49,8 @@ extern bool is_foreign_expr(PlannerInfo *root, extern void deparseSelectSql(StringInfo buf, PlannerInfo *root, RelOptInfo *baserel, - Bitmapset *attrs_used); + Bitmapset *attrs_used, + List **retrieved_attrs); extern void appendWhereClause(StringInfo buf, PlannerInfo *root, RelOptInfo *baserel, @@ -58,14 +59,18 @@ extern void appendWhereClause(StringInfo buf, List **params); extern void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList); + List *targetAttrs, List *returningList, + List **retrieved_attrs); extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList); + List *targetAttrs, List *returningList, + List **retrieved_attrs); extern void deparseDeleteSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *returningList); + List *returningList, + List **retrieved_attrs); extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel); -extern void deparseAnalyzeSql(StringInfo buf, Relation rel); +extern void deparseAnalyzeSql(StringInfo buf, Relation rel, + List **retrieved_attrs); #endif /* POSTGRES_FDW_H */ diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 1d5989e8dd..670d769a80 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -311,7 +311,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; 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; -DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING *; +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; -- 2.40.0