On 2016/07/15 11:48, Tom Lane wrote:
Etsuro Fujita <fujita.ets...@lab.ntt.co.jp> writes:
One thing I'm not sure about is: should we insist that a join can be
pushed down only if the checkAsUser fields of the relevant RTEs are
equal in the case where user mappings are meaningless to the FDW, like
file_fdw?
If we add a mechanism to let us know that the FDW doesn't care, we could
relax the requirement for such cases. I don't have a strong opinion on
whether that's worthwhile. It'd depend in part on how many FDWs there
are that don't care, versus those that do; and I have no idea about that.
So, I'd vote for leaving that for future work if necessary.
Here is a patch for that redesign proposed by you; reverts commits
fbe5a3fb73102c2cfec11aaaa4a67943f4474383 and
5d4171d1c70edfe3e9be1de9e66603af28e3afe1, adds changes for that redesign
to the core, and adjusts the postgres_fdw code to that changes. Also, I
rearranged the postgres_fdw regression tests to match that changes.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 2053,2207 **** SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
1
(10 rows)
- -- create another user for permission, user mapping, effective user tests
- CREATE USER view_owner;
- -- grant privileges on ft4 and ft5 to view_owner
- GRANT ALL ON ft4 TO view_owner;
- GRANT ALL ON ft5 TO view_owner;
- -- prepare statement with current session user
- PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
- EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
- QUERY PLAN
- -------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Limit
- Output: t1.c1, t2.c1
- -> Foreign Scan
- Output: t1.c1, t2.c1
- Relations: (public.ft4 t1) LEFT JOIN (public.ft5 t2)
- Remote SQL: SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 LEFT JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) ORDER BY r1.c1 ASC NULLS LAST, r2.c1 ASC NULLS LAST
- (6 rows)
-
- EXECUTE join_stmt;
- c1 | c1
- ----+----
- 22 |
- 24 | 24
- 26 |
- 28 |
- 30 | 30
- 32 |
- 34 |
- 36 | 36
- 38 |
- 40 |
- (10 rows)
-
- -- change the session user to view_owner and execute the statement. Because of
- -- change in session user, the plan should get invalidated and created again.
- -- The join will not be pushed down since the joining relations do not have a
- -- valid user mapping.
- SET SESSION ROLE view_owner;
- EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
- QUERY PLAN
- ------------------------------------------------------------------
- Limit
- Output: t1.c1, t2.c1
- -> Sort
- Output: t1.c1, t2.c1
- Sort Key: t1.c1, t2.c1
- -> Hash Left Join
- Output: t1.c1, t2.c1
- Hash Cond: (t1.c1 = t2.c1)
- -> Foreign Scan on public.ft4 t1
- Output: t1.c1, t1.c2, t1.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 3"
- -> Hash
- Output: t2.c1
- -> Foreign Scan on public.ft5 t2
- Output: t2.c1
- Remote SQL: SELECT c1 FROM "S 1"."T 4"
- (16 rows)
-
- RESET ROLE;
- DEALLOCATE join_stmt;
- CREATE VIEW v_ft5 AS SELECT * FROM ft5;
- -- change owner of v_ft5 to view_owner so that the effective user for scan on
- -- ft5 is view_owner and not the current user.
- ALTER VIEW v_ft5 OWNER TO view_owner;
- -- create a public user mapping for loopback server
- -- drop user mapping for current_user.
- DROP USER MAPPING FOR CURRENT_USER SERVER loopback;
- CREATE USER MAPPING FOR PUBLIC SERVER loopback;
- -- different effective user for permission check, but same user mapping for the
- -- joining sides, join pushed down, no result expected.
- PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
- EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------------------------
- Limit
- Output: t1.c1, ft5.c1
- -> Foreign Scan
- Output: t1.c1, ft5.c1
- Relations: (public.ft5 t1) INNER JOIN (public.ft5)
- Remote SQL: SELECT r1.c1, r6.c1 FROM ("S 1"."T 4" r1 INNER JOIN "S 1"."T 4" r6 ON (((r1.c1 = r6.c1)))) ORDER BY r1.c1 ASC NULLS LAST
- (6 rows)
-
- EXECUTE join_stmt;
- c1 | c1
- ----+----
- (0 rows)
-
- -- create user mapping for view_owner and execute the prepared statement
- -- the join should not be pushed down since joining relations now use two
- -- different user mappings
- CREATE USER MAPPING FOR view_owner SERVER loopback;
- EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
- QUERY PLAN
- ---------------------------------------------------------------------------------------
- Limit
- Output: t1.c1, ft5.c1
- -> Merge Join
- Output: t1.c1, ft5.c1
- Merge Cond: (t1.c1 = ft5.c1)
- -> Foreign Scan on public.ft5 t1
- Output: t1.c1, t1.c2, t1.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC NULLS LAST
- -> Materialize
- Output: ft5.c1, ft5.c2, ft5.c3
- -> Foreign Scan on public.ft5
- Output: ft5.c1, ft5.c2, ft5.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC NULLS LAST
- (13 rows)
-
- EXECUTE join_stmt;
- c1 | c1
- ----+----
- (0 rows)
-
- -- If a sub-join can't be pushed down, upper level join shouldn't be either.
- EXPLAIN (COSTS false, VERBOSE)
- SELECT t1.c1, t2.c1 FROM (ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1)) left join (ft5 t3 JOIN v_ft5 t4 ON (t3.c1 = t4.c1)) ON (t1.c1 = t3.c1);
- QUERY PLAN
- ------------------------------------------------------------------
- Hash Join
- Output: t1.c1, ft5.c1
- Hash Cond: (t1.c1 = ft5.c1)
- -> Hash Right Join
- Output: t1.c1
- Hash Cond: (t3.c1 = t1.c1)
- -> Hash Join
- Output: t3.c1
- Hash Cond: (t3.c1 = ft5_1.c1)
- -> Foreign Scan on public.ft5 t3
- Output: t3.c1, t3.c2, t3.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4"
- -> Hash
- Output: ft5_1.c1
- -> Foreign Scan on public.ft5 ft5_1
- Output: ft5_1.c1
- Remote SQL: SELECT c1 FROM "S 1"."T 4"
- -> Hash
- Output: t1.c1
- -> Foreign Scan on public.ft5 t1
- Output: t1.c1
- Remote SQL: SELECT c1 FROM "S 1"."T 4"
- -> Hash
- Output: ft5.c1
- -> Foreign Scan on public.ft5
- Output: ft5.c1
- Remote SQL: SELECT c1 FROM "S 1"."T 4"
- (27 rows)
-
-- non-Var items in targelist of the nullable rel of a join preventing
-- push-down in some cases
-- unable to push {ft1, ft2}
--- 2053,2058 ----
***************
*** 2260,2268 **** SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT
14 | | |
(3 rows)
- -- recreate the dropped user mapping for further tests
- CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
- DROP USER MAPPING FOR PUBLIC SERVER loopback;
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN VERBOSE SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
--- 2111,2116 ----
***************
*** 2283,2288 **** SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5
--- 2131,2324 ----
(30,31,AAA030) | 30 | 31 | AAA030 | 30 | 31
(4 rows)
+ -- join encapsulated in a view
+ DROP USER MAPPING FOR CURRENT_USER SERVER loopback;
+ CREATE USER MAPPING FOR PUBLIC SERVER loopback;
+ CREATE USER view_owner;
+ GRANT SELECT ON ft4 TO view_owner;
+ GRANT SELECT ON ft5 TO view_owner;
+ CREATE VIEW join_view AS SELECT t1.c1, t2.c2 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ ALTER VIEW join_view OWNER TO view_owner;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT * FROM join_view; -- can be pushed down
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on join_view
+ Output: join_view.c1, join_view.c2
+ -> Limit
+ Output: t1.c1, t2.c2, t2.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c2, t2.c1
+ Relations: (public.ft4 t1) LEFT JOIN (public.ft5 t2)
+ Remote SQL: SELECT r3.c1, r4.c2, r4.c1 FROM ("S 1"."T 3" r3 LEFT JOIN "S 1"."T 4" r4 ON (((r3.c1 = r4.c1)))) ORDER BY r3.c1 ASC NULLS LAST, r4.c1 ASC NULLS LAST
+ (8 rows)
+
+ SELECT * FROM join_view;
+ c1 | c2
+ ----+----
+ 22 |
+ 24 | 25
+ 26 |
+ 28 |
+ 30 | 31
+ 32 |
+ 34 |
+ 36 | 37
+ 38 |
+ 40 |
+ (10 rows)
+
+ -- join between two views
+ CREATE VIEW v4 AS SELECT * FROM ft4;
+ CREATE VIEW v5 AS SELECT * FROM ft5;
+ ALTER VIEW v5 OWNER TO view_owner;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down
+ QUERY PLAN
+ ----------------------------------------------------------------------
+ Limit
+ Output: ft4.c1, ft5.c2, ft5.c1
+ -> Sort
+ Output: ft4.c1, ft5.c2, ft5.c1
+ Sort Key: ft4.c1, ft5.c1
+ -> Hash Left Join
+ Output: ft4.c1, ft5.c2, ft5.c1
+ Hash Cond: (ft4.c1 = ft5.c1)
+ -> Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 3"
+ -> Hash
+ Output: ft5.c2, ft5.c1
+ -> Foreign Scan on public.ft5
+ Output: ft5.c2, ft5.c1
+ Remote SQL: SELECT c1, c2 FROM "S 1"."T 4"
+ (16 rows)
+
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ c1 | c2
+ ----+----
+ 22 |
+ 24 | 25
+ 26 |
+ 28 |
+ 30 | 31
+ 32 |
+ 34 |
+ 36 | 37
+ 38 |
+ 40 |
+ (10 rows)
+
+ ALTER VIEW v4 OWNER TO view_owner;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: ft4.c1, ft5.c2, ft5.c1
+ -> Foreign Scan
+ Output: ft4.c1, ft5.c2, ft5.c1
+ Relations: (public.ft4) LEFT JOIN (public.ft5)
+ Remote SQL: SELECT r6.c1, r9.c2, r9.c1 FROM ("S 1"."T 3" r6 LEFT JOIN "S 1"."T 4" r9 ON (((r6.c1 = r9.c1)))) ORDER BY r6.c1 ASC NULLS LAST, r9.c1 ASC NULLS LAST
+ (6 rows)
+
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ c1 | c2
+ ----+----
+ 22 |
+ 24 | 25
+ 26 |
+ 28 |
+ 30 | 31
+ 32 |
+ 34 |
+ 36 | 37
+ 38 |
+ 40 |
+ (10 rows)
+
+ -- join between a view and a foreign table
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down
+ QUERY PLAN
+ ----------------------------------------------------------------------
+ Limit
+ Output: ft4.c1, t2.c2, t2.c1
+ -> Sort
+ Output: ft4.c1, t2.c2, t2.c1
+ Sort Key: ft4.c1, t2.c1
+ -> Hash Left Join
+ Output: ft4.c1, t2.c2, t2.c1
+ Hash Cond: (ft4.c1 = t2.c1)
+ -> Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 3"
+ -> Hash
+ Output: t2.c2, t2.c1
+ -> Foreign Scan on public.ft5 t2
+ Output: t2.c2, t2.c1
+ Remote SQL: SELECT c1, c2 FROM "S 1"."T 4"
+ (16 rows)
+
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ c1 | c2
+ ----+----
+ 22 |
+ 24 | 25
+ 26 |
+ 28 |
+ 30 | 31
+ 32 |
+ 34 |
+ 36 | 37
+ 38 |
+ 40 |
+ (10 rows)
+
+ ALTER VIEW v4 OWNER TO CURRENT_USER;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down
+ QUERY PLAN
+ ----------------------------------------------------------------------
+ Limit
+ Output: ft4.c1, t2.c2, t2.c1
+ -> Sort
+ Output: ft4.c1, t2.c2, t2.c1
+ Sort Key: ft4.c1, t2.c1
+ -> Hash Left Join
+ Output: ft4.c1, t2.c2, t2.c1
+ Hash Cond: (ft4.c1 = t2.c1)
+ -> Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 3"
+ -> Hash
+ Output: t2.c2, t2.c1
+ -> Foreign Scan on public.ft5 t2
+ Output: t2.c2, t2.c1
+ Remote SQL: SELECT c1, c2 FROM "S 1"."T 4"
+ (16 rows)
+
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ c1 | c2
+ ----+----
+ 22 |
+ 24 | 25
+ 26 |
+ 28 |
+ 30 | 31
+ 32 |
+ 34 |
+ 36 | 37
+ 38 |
+ 40 |
+ (10 rows)
+
+ ALTER VIEW v4 OWNER TO view_owner;
+ -- cleanup
+ DROP OWNED BY view_owner;
+ DROP USER view_owner;
+ DROP USER MAPPING FOR PUBLIC SERVER loopback;
+ CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
-- ===================================================================
-- parameterized queries
-- ===================================================================
***************
*** 5867,5872 **** AND ftoptions @> array['fetch_size=60000'];
(1 row)
ROLLBACK;
- -- Cleanup
- DROP OWNED BY view_owner;
- DROP USER view_owner;
--- 5903,5905 ----
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 67,74 **** enum FdwScanPrivateIndex
FdwScanPrivateRetrievedAttrs,
/* Integer representing the desired fetch_size */
FdwScanPrivateFetchSize,
- /* Oid of user mapping to be used while connecting to the foreign server */
- FdwScanPrivateUserMappingOid,
/*
* String describing join i.e. names of relations being joined and types
--- 67,72 ----
***************
*** 1226,1236 **** postgresGetForeignPlan(PlannerInfo *root,
* Build the fdw_private list that will be available to the executor.
* Items in the list must match order in enum FdwScanPrivateIndex.
*/
! fdw_private = list_make5(makeString(sql.data),
remote_conds,
retrieved_attrs,
! makeInteger(fpinfo->fetch_size),
! makeInteger(foreignrel->umid));
if (foreignrel->reloptkind == RELOPT_JOINREL)
fdw_private = lappend(fdw_private,
makeString(fpinfo->relation_name->data));
--- 1224,1233 ----
* Build the fdw_private list that will be available to the executor.
* Items in the list must match order in enum FdwScanPrivateIndex.
*/
! fdw_private = list_make4(makeString(sql.data),
remote_conds,
retrieved_attrs,
! makeInteger(fpinfo->fetch_size));
if (foreignrel->reloptkind == RELOPT_JOINREL)
fdw_private = lappend(fdw_private,
makeString(fpinfo->relation_name->data));
***************
*** 1262,1268 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
--- 1259,1269 ----
ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
EState *estate = node->ss.ps.state;
PgFdwScanState *fsstate;
+ RangeTblEntry *rte;
+ Oid userid;
+ ForeignTable *table;
UserMapping *user;
+ int rtindex;
int numParams;
/*
***************
*** 1278,1313 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
node->fdw_state = (void *) fsstate;
/*
! * Obtain the foreign server where to connect and user mapping to use for
! * connection. For base relations we obtain this information from
! * catalogs. For join relations, this information is frozen at the time of
! * planning to ensure that the join is safe to pushdown. In case the
! * information goes stale between planning and execution, plan will be
! * invalidated and replanned.
*/
if (fsplan->scan.scanrelid > 0)
! {
! ForeignTable *table;
!
! /*
! * Identify which user to do the remote access as. This should match
! * what ExecCheckRTEPerms() does.
! */
! RangeTblEntry *rte = rt_fetch(fsplan->scan.scanrelid, estate->es_range_table);
! Oid userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
!
! fsstate->rel = node->ss.ss_currentRelation;
! table = GetForeignTable(RelationGetRelid(fsstate->rel));
!
! user = GetUserMapping(userid, table->serverid);
! }
else
{
! Oid umid = intVal(list_nth(fsplan->fdw_private, FdwScanPrivateUserMappingOid));
!
! user = GetUserMappingById(umid);
! Assert(fsplan->fs_server == user->serverid);
}
/*
* Get connection to the foreign server. Connection manager will
--- 1279,1301 ----
node->fdw_state = (void *) fsstate;
/*
! * Identify which user to do the remote access as. This should match what
! * ExecCheckRTEPerms() does.
*/
if (fsplan->scan.scanrelid > 0)
! rtindex = fsplan->scan.scanrelid;
else
{
! /* Pick the lowest-numbered one as a representative. */
! rtindex = -1;
! rtindex = bms_next_member(fsplan->fs_relids, rtindex);
}
+ rte = rt_fetch(rtindex, estate->es_range_table);
+ userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
+
+ /* Get info about foreign table. */
+ table = GetForeignTable(rte->relid);
+ user = GetUserMapping(userid, table->serverid);
/*
* Get connection to the foreign server. Connection manager will
***************
*** 1344,1352 **** postgresBeginForeignScan(ForeignScanState *node, int eflags)
--- 1332,1346 ----
* into local representation and error reporting during that process.
*/
if (fsplan->scan.scanrelid > 0)
+ {
+ fsstate->rel = node->ss.ss_currentRelation;
fsstate->tupdesc = RelationGetDescr(fsstate->rel);
+ }
else
+ {
+ fsstate->rel = NULL;
fsstate->tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
+ }
fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
***************
*** 3966,3981 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
List *otherclauses;
/*
- * Core code may call GetForeignJoinPaths hook even when the join relation
- * doesn't have a valid user mapping associated with it. See
- * build_join_rel() for details. We can't push down such join, since there
- * doesn't exist a user mapping which can be used to connect to the
- * foreign server.
- */
- if (!OidIsValid(joinrel->umid))
- return false;
-
- /*
* We support pushing down INNER, LEFT, RIGHT and FULL OUTER joins.
* Constructing queries representing SEMI and ANTI joins is hard, hence
* not considered right now.
--- 3960,3965 ----
***************
*** 4151,4156 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4135,4154 ----
fpinfo->use_remote_estimate = fpinfo_o->use_remote_estimate ||
fpinfo_i->use_remote_estimate;
+ /* Get user mapping */
+ if (fpinfo->use_remote_estimate)
+ {
+ if (fpinfo_o->use_remote_estimate)
+ fpinfo->user = fpinfo_o->user;
+ else
+ fpinfo->user = fpinfo_i->user;
+ }
+ else
+ fpinfo->user = NULL;
+
+ /* Get foreign server */
+ fpinfo->server = fpinfo_o->server;
+
/*
* Since both the joining relations come from the same server, the server
* level options should have same value for both the relations. Pick from
***************
*** 4312,4337 **** postgresGetForeignJoinPaths(PlannerInfo *root,
cost_qual_eval(&fpinfo->local_conds_cost, fpinfo->local_conds, root);
/*
! * If we are going to estimate the costs using EXPLAIN, we will need
! * connection information. Fill it here.
*/
! if (fpinfo->use_remote_estimate)
! fpinfo->user = GetUserMappingById(joinrel->umid);
! else
! {
! fpinfo->user = NULL;
!
! /*
! * If we are going to estimate costs locally, estimate the join clause
! * selectivity here while we have special join info.
! */
fpinfo->joinclause_sel = clauselist_selectivity(root, fpinfo->joinclauses,
0, fpinfo->jointype,
extra->sjinfo);
- }
- fpinfo->server = GetForeignServer(joinrel->serverid);
-
/* Estimate costs for bare join relation */
estimate_path_cost_size(root, joinrel, NIL, NIL, &rows,
&width, &startup_cost, &total_cost);
--- 4310,4323 ----
cost_qual_eval(&fpinfo->local_conds_cost, fpinfo->local_conds, root);
/*
! * If we are going to estimate costs locally, estimate the join clause
! * selectivity here while we have special join info.
*/
! if (!fpinfo->use_remote_estimate)
fpinfo->joinclause_sel = clauselist_selectivity(root, fpinfo->joinclauses,
0, fpinfo->jointype,
extra->sjinfo);
/* Estimate costs for bare join relation */
estimate_path_cost_size(root, joinrel, NIL, NIL, &rows,
&width, &startup_cost, &total_cost);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 485,532 **** EXPLAIN (COSTS false, VERBOSE)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
- -- create another user for permission, user mapping, effective user tests
- CREATE USER view_owner;
- -- grant privileges on ft4 and ft5 to view_owner
- GRANT ALL ON ft4 TO view_owner;
- GRANT ALL ON ft5 TO view_owner;
- -- prepare statement with current session user
- PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
- EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
- EXECUTE join_stmt;
- -- change the session user to view_owner and execute the statement. Because of
- -- change in session user, the plan should get invalidated and created again.
- -- The join will not be pushed down since the joining relations do not have a
- -- valid user mapping.
- SET SESSION ROLE view_owner;
- EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
- RESET ROLE;
- DEALLOCATE join_stmt;
-
- CREATE VIEW v_ft5 AS SELECT * FROM ft5;
- -- change owner of v_ft5 to view_owner so that the effective user for scan on
- -- ft5 is view_owner and not the current user.
- ALTER VIEW v_ft5 OWNER TO view_owner;
- -- create a public user mapping for loopback server
- -- drop user mapping for current_user.
- DROP USER MAPPING FOR CURRENT_USER SERVER loopback;
- CREATE USER MAPPING FOR PUBLIC SERVER loopback;
- -- different effective user for permission check, but same user mapping for the
- -- joining sides, join pushed down, no result expected.
- PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
- EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
- EXECUTE join_stmt;
- -- create user mapping for view_owner and execute the prepared statement
- -- the join should not be pushed down since joining relations now use two
- -- different user mappings
- CREATE USER MAPPING FOR view_owner SERVER loopback;
- EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
- EXECUTE join_stmt;
-
- -- If a sub-join can't be pushed down, upper level join shouldn't be either.
- EXPLAIN (COSTS false, VERBOSE)
- SELECT t1.c1, t2.c1 FROM (ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1)) left join (ft5 t3 JOIN v_ft5 t4 ON (t3.c1 = t4.c1)) ON (t1.c1 = t3.c1);
-
-- non-Var items in targelist of the nullable rel of a join preventing
-- push-down in some cases
-- unable to push {ft1, ft2}
--- 485,490 ----
***************
*** 539,553 **** EXPLAIN (COSTS false, VERBOSE)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
- -- recreate the dropped user mapping for further tests
- CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
- DROP USER MAPPING FOR PUBLIC SERVER loopback;
-
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN VERBOSE SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
-- ===================================================================
-- parameterized queries
-- ===================================================================
--- 497,547 ----
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN VERBOSE SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
+ -- join encapsulated in a view
+ DROP USER MAPPING FOR CURRENT_USER SERVER loopback;
+ CREATE USER MAPPING FOR PUBLIC SERVER loopback;
+ CREATE USER view_owner;
+ GRANT SELECT ON ft4 TO view_owner;
+ GRANT SELECT ON ft5 TO view_owner;
+ CREATE VIEW join_view AS SELECT t1.c1, t2.c2 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ ALTER VIEW join_view OWNER TO view_owner;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT * FROM join_view; -- can be pushed down
+ SELECT * FROM join_view;
+
+ -- join between two views
+ CREATE VIEW v4 AS SELECT * FROM ft4;
+ CREATE VIEW v5 AS SELECT * FROM ft5;
+ ALTER VIEW v5 OWNER TO view_owner;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ ALTER VIEW v4 OWNER TO view_owner;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+
+ -- join between a view and a foreign table
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ ALTER VIEW v4 OWNER TO CURRENT_USER;
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down
+ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
+ ALTER VIEW v4 OWNER TO view_owner;
+
+ -- cleanup
+ DROP OWNED BY view_owner;
+ DROP USER view_owner;
+ DROP USER MAPPING FOR PUBLIC SERVER loopback;
+ CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
+
-- ===================================================================
-- parameterized queries
-- ===================================================================
***************
*** 1360,1366 **** WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=60000'];
ROLLBACK;
-
- -- Cleanup
- DROP OWNED BY view_owner;
- DROP USER view_owner;
--- 1354,1356 ----
*** a/src/backend/executor/execParallel.c
--- b/src/backend/executor/execParallel.c
***************
*** 160,166 **** ExecSerializePlan(Plan *plan, EState *estate)
pstmt->relationOids = NIL;
pstmt->invalItems = NIL; /* workers can't replan anyway... */
pstmt->hasRowSecurity = false;
- pstmt->hasForeignJoin = false;
/* Return serialized copy of our dummy PlannedStmt. */
return nodeToString(pstmt);
--- 160,165 ----
*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
***************
*** 31,37 ****
extern Datum pg_options_to_table(PG_FUNCTION_ARGS);
extern Datum postgresql_fdw_validator(PG_FUNCTION_ARGS);
! static HeapTuple find_user_mapping(Oid userid, Oid serverid, bool missing_ok);
/*
* GetForeignDataWrapper - look up the foreign-data wrapper by OID.
--- 31,37 ----
extern Datum pg_options_to_table(PG_FUNCTION_ARGS);
extern Datum postgresql_fdw_validator(PG_FUNCTION_ARGS);
! static HeapTuple find_user_mapping(Oid userid, Oid serverid);
/*
* GetForeignDataWrapper - look up the foreign-data wrapper by OID.
***************
*** 223,229 **** GetUserMapping(Oid userid, Oid serverid)
bool isnull;
UserMapping *um;
! tp = find_user_mapping(userid, serverid, false);
um = (UserMapping *) palloc(sizeof(UserMapping));
um->umid = HeapTupleGetOid(tp);
--- 223,229 ----
bool isnull;
UserMapping *um;
! tp = find_user_mapping(userid, serverid);
um = (UserMapping *) palloc(sizeof(UserMapping));
um->umid = HeapTupleGetOid(tp);
***************
*** 250,272 **** GetUserMapping(Oid userid, Oid serverid)
*
* If no mapping is found for the supplied user, we also look for
* PUBLIC mappings (userid == InvalidOid).
- *
- * If missing_ok is true, the function returns InvalidOid when it does not find
- * required user mapping. Otherwise, find_user_mapping() throws error if it
- * does not find required user mapping.
*/
Oid
! GetUserMappingId(Oid userid, Oid serverid, bool missing_ok)
{
HeapTuple tp;
Oid umid;
! tp = find_user_mapping(userid, serverid, missing_ok);
!
! Assert(missing_ok || tp);
!
! if (!tp && missing_ok)
! return InvalidOid;
/* Extract the Oid */
umid = HeapTupleGetOid(tp);
--- 250,263 ----
*
* If no mapping is found for the supplied user, we also look for
* PUBLIC mappings (userid == InvalidOid).
*/
Oid
! GetUserMappingId(Oid userid, Oid serverid)
{
HeapTuple tp;
Oid umid;
! tp = find_user_mapping(userid, serverid);
/* Extract the Oid */
umid = HeapTupleGetOid(tp);
***************
*** 282,294 **** GetUserMappingId(Oid userid, Oid serverid, bool missing_ok)
*
* If no mapping is found for the supplied user, we also look for
* PUBLIC mappings (userid == InvalidOid).
- *
- * If missing_ok is true, the function returns NULL, if it does not find
- * the required user mapping. Otherwise, it throws error if it does not
- * find the required user mapping.
*/
static HeapTuple
! find_user_mapping(Oid userid, Oid serverid, bool missing_ok)
{
HeapTuple tp;
--- 273,281 ----
*
* If no mapping is found for the supplied user, we also look for
* PUBLIC mappings (userid == InvalidOid).
*/
static HeapTuple
! find_user_mapping(Oid userid, Oid serverid)
{
HeapTuple tp;
***************
*** 305,319 **** find_user_mapping(Oid userid, Oid serverid, bool missing_ok)
ObjectIdGetDatum(serverid));
if (!HeapTupleIsValid(tp))
! {
! if (missing_ok)
! return NULL;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("user mapping not found for \"%s\"",
! MappingUserName(userid))));
! }
return tp;
}
--- 292,301 ----
ObjectIdGetDatum(serverid));
if (!HeapTupleIsValid(tp))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("user mapping not found for \"%s\"",
! MappingUserName(userid))));
return tp;
}
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 97,103 **** _copyPlannedStmt(const PlannedStmt *from)
COPY_SCALAR_FIELD(nParamExec);
COPY_SCALAR_FIELD(hasRowSecurity);
COPY_SCALAR_FIELD(parallelModeNeeded);
- COPY_SCALAR_FIELD(hasForeignJoin);
return newnode;
}
--- 97,102 ----
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 273,279 **** _outPlannedStmt(StringInfo str, const PlannedStmt *node)
WRITE_INT_FIELD(nParamExec);
WRITE_BOOL_FIELD(hasRowSecurity);
WRITE_BOOL_FIELD(parallelModeNeeded);
- WRITE_BOOL_FIELD(hasForeignJoin);
}
/*
--- 273,278 ----
***************
*** 2018,2024 **** _outPlannerGlobal(StringInfo str, const PlannerGlobal *node)
WRITE_BOOL_FIELD(hasRowSecurity);
WRITE_BOOL_FIELD(parallelModeOK);
WRITE_BOOL_FIELD(parallelModeNeeded);
- WRITE_BOOL_FIELD(hasForeignJoin);
}
static void
--- 2017,2022 ----
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 1402,1408 **** _readPlannedStmt(void)
READ_INT_FIELD(nParamExec);
READ_BOOL_FIELD(hasRowSecurity);
READ_BOOL_FIELD(parallelModeNeeded);
- READ_BOOL_FIELD(hasForeignJoin);
READ_DONE();
}
--- 1402,1407 ----
*** a/src/backend/optimizer/path/joinpath.c
--- b/src/backend/optimizer/path/joinpath.c
***************
*** 213,220 **** add_paths_to_joinrel(PlannerInfo *root,
/*
* 5. If inner and outer relations are foreign tables (or joins) belonging
! * to the same server and using the same user mapping, give the FDW a
! * chance to push down joins.
*/
if (joinrel->fdwroutine &&
joinrel->fdwroutine->GetForeignJoinPaths)
--- 213,220 ----
/*
* 5. If inner and outer relations are foreign tables (or joins) belonging
! * to the same server and assigned to the same user to check access
! * permissions as, give the FDW a chance to push down joins.
*/
if (joinrel->fdwroutine &&
joinrel->fdwroutine->GetForeignJoinPaths)
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3247,3261 **** create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
scan_plan->fs_relids = best_path->path.parent->relids;
/*
- * If a join between foreign relations was pushed down, remember it. The
- * push-down safety of the join depends upon the server and user mapping
- * being same. That can change between planning and execution time, in
- * which case the plan should be invalidated.
- */
- if (scan_relid == 0)
- root->glob->hasForeignJoin = true;
-
- /*
* Replace any outer-relation variables with nestloop params in the qual,
* fdw_exprs and fdw_recheck_quals expressions. We do this last so that
* the FDW doesn't have to be involved. (Note that parts of fdw_exprs or
--- 3247,3252 ----
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 220,226 **** standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
glob->lastPlanNodeId = 0;
glob->transientPlan = false;
glob->hasRowSecurity = false;
- glob->hasForeignJoin = false;
/*
* Assess whether it's feasible to use parallel mode for this query. We
--- 220,225 ----
***************
*** 417,423 **** standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
result->nParamExec = glob->nParamExec;
result->hasRowSecurity = glob->hasRowSecurity;
result->parallelModeNeeded = glob->parallelModeNeeded;
- result->hasForeignJoin = glob->hasForeignJoin;
return result;
}
--- 416,421 ----
***************
*** 1960,1966 **** grouping_planner(PlannerInfo *root, bool inheritance_update,
* If the current_rel belongs to a single FDW, so does the final_rel.
*/
final_rel->serverid = current_rel->serverid;
! final_rel->umid = current_rel->umid;
final_rel->fdwroutine = current_rel->fdwroutine;
/*
--- 1958,1964 ----
* If the current_rel belongs to a single FDW, so does the final_rel.
*/
final_rel->serverid = current_rel->serverid;
! final_rel->userid = current_rel->userid;
final_rel->fdwroutine = current_rel->fdwroutine;
/*
***************
*** 3337,3343 **** create_grouping_paths(PlannerInfo *root,
* If the input rel belongs to a single FDW, so does the grouped rel.
*/
grouped_rel->serverid = input_rel->serverid;
! grouped_rel->umid = input_rel->umid;
grouped_rel->fdwroutine = input_rel->fdwroutine;
/*
--- 3335,3341 ----
* If the input rel belongs to a single FDW, so does the grouped rel.
*/
grouped_rel->serverid = input_rel->serverid;
! grouped_rel->userid = input_rel->userid;
grouped_rel->fdwroutine = input_rel->fdwroutine;
/*
***************
*** 3891,3897 **** create_window_paths(PlannerInfo *root,
* If the input rel belongs to a single FDW, so does the window rel.
*/
window_rel->serverid = input_rel->serverid;
! window_rel->umid = input_rel->umid;
window_rel->fdwroutine = input_rel->fdwroutine;
/*
--- 3889,3895 ----
* If the input rel belongs to a single FDW, so does the window rel.
*/
window_rel->serverid = input_rel->serverid;
! window_rel->userid = input_rel->userid;
window_rel->fdwroutine = input_rel->fdwroutine;
/*
***************
*** 4071,4077 **** create_distinct_paths(PlannerInfo *root,
* If the input rel belongs to a single FDW, so does the distinct_rel.
*/
distinct_rel->serverid = input_rel->serverid;
! distinct_rel->umid = input_rel->umid;
distinct_rel->fdwroutine = input_rel->fdwroutine;
/* Estimate number of distinct rows there will be */
--- 4069,4075 ----
* If the input rel belongs to a single FDW, so does the distinct_rel.
*/
distinct_rel->serverid = input_rel->serverid;
! distinct_rel->userid = input_rel->userid;
distinct_rel->fdwroutine = input_rel->fdwroutine;
/* Estimate number of distinct rows there will be */
***************
*** 4279,4285 **** create_ordered_paths(PlannerInfo *root,
* If the input rel belongs to a single FDW, so does the ordered_rel.
*/
ordered_rel->serverid = input_rel->serverid;
! ordered_rel->umid = input_rel->umid;
ordered_rel->fdwroutine = input_rel->fdwroutine;
foreach(lc, input_rel->pathlist)
--- 4277,4283 ----
* If the input rel belongs to a single FDW, so does the ordered_rel.
*/
ordered_rel->serverid = input_rel->serverid;
! ordered_rel->userid = input_rel->userid;
ordered_rel->fdwroutine = input_rel->fdwroutine;
foreach(lc, input_rel->pathlist)
*** a/src/backend/optimizer/util/relnode.c
--- b/src/backend/optimizer/util/relnode.c
***************
*** 14,22 ****
*/
#include "postgres.h"
- #include "miscadmin.h"
- #include "catalog/pg_class.h"
- #include "foreign/foreign.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/pathnode.h"
--- 14,19 ----
***************
*** 130,136 **** build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
rel->subroot = NULL;
rel->subplan_params = NIL;
rel->serverid = InvalidOid;
! rel->umid = InvalidOid;
rel->fdwroutine = NULL;
rel->fdw_private = NULL;
rel->baserestrictinfo = NIL;
--- 127,133 ----
rel->subroot = NULL;
rel->subplan_params = NIL;
rel->serverid = InvalidOid;
! rel->userid = rte->checkAsUser;
rel->fdwroutine = NULL;
rel->fdw_private = NULL;
rel->baserestrictinfo = NIL;
***************
*** 170,199 **** build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
break;
}
- /* For foreign tables get the user mapping */
- if (rte->relkind == RELKIND_FOREIGN_TABLE)
- {
- /*
- * This should match what ExecCheckRTEPerms() does.
- *
- * Note that if the plan ends up depending on the user OID in any way
- * - e.g. if it depends on the computed user mapping OID - we must
- * ensure that it gets invalidated in the case of a user OID change.
- * See RevalidateCachedQuery and more generally the hasForeignJoin
- * flags in PlannerGlobal and PlannedStmt.
- *
- * It's possible, and not necessarily an error, for rel->umid to be
- * InvalidOid even though rel->serverid is set. That just means there
- * is a server with no user mapping.
- */
- Oid userid;
-
- userid = OidIsValid(rte->checkAsUser) ? rte->checkAsUser : GetUserId();
- rel->umid = GetUserMappingId(userid, rel->serverid, true);
- }
- else
- rel->umid = InvalidOid;
-
/* Save the finished struct in the query's simple_rel_array */
root->simple_rel_array[relid] = rel;
--- 167,172 ----
***************
*** 424,430 **** build_join_rel(PlannerInfo *root,
joinrel->subroot = NULL;
joinrel->subplan_params = NIL;
joinrel->serverid = InvalidOid;
! joinrel->umid = InvalidOid;
joinrel->fdwroutine = NULL;
joinrel->fdw_private = NULL;
joinrel->baserestrictinfo = NIL;
--- 397,403 ----
joinrel->subroot = NULL;
joinrel->subplan_params = NIL;
joinrel->serverid = InvalidOid;
! joinrel->userid = InvalidOid;
joinrel->fdwroutine = NULL;
joinrel->fdw_private = NULL;
joinrel->baserestrictinfo = NIL;
***************
*** 435,457 **** build_join_rel(PlannerInfo *root,
/*
* Set up foreign-join fields if outer and inner relation are foreign
! * tables (or joins) belonging to the same server and using the same user
! * mapping.
*
* Otherwise those fields are left invalid, so FDW API will not be called
* for the join relation.
- *
- * For FDWs like file_fdw, which ignore user mapping, the user mapping id
- * associated with the joining relation may be invalid. A valid serverid
- * distinguishes between a pushed down join with no user mapping and a
- * join which can not be pushed down because of user mapping mismatch.
*/
if (OidIsValid(outer_rel->serverid) &&
inner_rel->serverid == outer_rel->serverid &&
! inner_rel->umid == outer_rel->umid)
{
joinrel->serverid = outer_rel->serverid;
! joinrel->umid = outer_rel->umid;
joinrel->fdwroutine = outer_rel->fdwroutine;
}
--- 408,425 ----
/*
* Set up foreign-join fields if outer and inner relation are foreign
! * tables (or joins) belonging to the same server and assigned to the same
! * user to check access permissions as.
*
* Otherwise those fields are left invalid, so FDW API will not be called
* for the join relation.
*/
if (OidIsValid(outer_rel->serverid) &&
inner_rel->serverid == outer_rel->serverid &&
! inner_rel->userid == outer_rel->userid)
{
joinrel->serverid = outer_rel->serverid;
! joinrel->userid = outer_rel->userid;
joinrel->fdwroutine = outer_rel->fdwroutine;
}
*** a/src/backend/utils/cache/plancache.c
--- b/src/backend/utils/cache/plancache.c
***************
*** 105,112 **** static TupleDesc PlanCacheComputeResultDesc(List *stmt_list);
static void PlanCacheRelCallback(Datum arg, Oid relid);
static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
! static void PlanCacheUserMappingCallback(Datum arg, int cacheid,
! uint32 hashvalue);
/*
--- 105,111 ----
static void PlanCacheRelCallback(Datum arg, Oid relid);
static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
!
/*
***************
*** 122,129 **** InitPlanCache(void)
CacheRegisterSyscacheCallback(NAMESPACEOID, PlanCacheSysCallback, (Datum) 0);
CacheRegisterSyscacheCallback(OPEROID, PlanCacheSysCallback, (Datum) 0);
CacheRegisterSyscacheCallback(AMOPOPID, PlanCacheSysCallback, (Datum) 0);
- /* User mapping change may invalidate plans with pushed down foreign join */
- CacheRegisterSyscacheCallback(USERMAPPINGOID, PlanCacheUserMappingCallback, (Datum) 0);
}
/*
--- 121,126 ----
***************
*** 610,628 **** RevalidateCachedQuery(CachedPlanSource *plansource)
plansource->is_valid = false;
/*
- * If we have a join pushed down to the foreign server and the current
- * user is different from the one for which the plan was created,
- * invalidate the generic plan since user mapping for the new user might
- * make the join unsafe to push down, or change which user mapping is
- * used.
- */
- if (plansource->is_valid &&
- plansource->gplan &&
- plansource->gplan->has_foreign_join &&
- plansource->planUserId != GetUserId())
- plansource->gplan->is_valid = false;
-
- /*
* If the query is currently valid, acquire locks on the referenced
* objects; then check again. We need to do it this way to cover the race
* condition that an invalidation message arrives before we get the locks.
--- 607,612 ----
***************
*** 902,908 **** BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
bool spi_pushed;
MemoryContext plan_context;
MemoryContext oldcxt = CurrentMemoryContext;
- ListCell *lc;
/*
* Normally the querytree should be valid already, but if it's not,
--- 886,891 ----
***************
*** 1010,1029 **** BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
plan->is_saved = false;
plan->is_valid = true;
- /*
- * Walk through the plist and set hasForeignJoin if any of the plans have
- * it set.
- */
- plan->has_foreign_join = false;
- foreach(lc, plist)
- {
- PlannedStmt *plan_stmt = (PlannedStmt *) lfirst(lc);
-
- if (IsA(plan_stmt, PlannedStmt))
- plan->has_foreign_join =
- plan->has_foreign_join || plan_stmt->hasForeignJoin;
- }
-
/* assign generation number to new plan */
plan->generation = ++(plansource->generation);
--- 993,998 ----
***************
*** 1888,1927 **** PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue)
}
/*
- * PlanCacheUserMappingCallback
- * Syscache inval callback function for user mapping cache invalidation.
- *
- * Invalidates plans which have pushed down foreign joins.
- */
- static void
- PlanCacheUserMappingCallback(Datum arg, int cacheid, uint32 hashvalue)
- {
- CachedPlanSource *plansource;
-
- for (plansource = first_saved_plan; plansource; plansource = plansource->next_saved)
- {
- Assert(plansource->magic == CACHEDPLANSOURCE_MAGIC);
-
- /* No work if it's already invalidated */
- if (!plansource->is_valid)
- continue;
-
- /* Never invalidate transaction control commands */
- if (IsTransactionStmtPlan(plansource))
- continue;
-
- /*
- * If the plan has pushed down foreign joins, those join may become
- * unsafe to push down because of user mapping changes. Invalidate
- * only the generic plan, since changes to user mapping do not
- * invalidate the parse tree.
- */
- if (plansource->gplan && plansource->gplan->has_foreign_join)
- plansource->gplan->is_valid = false;
- }
- }
-
- /*
* ResetPlanCache: invalidate all cached plans.
*/
void
--- 1857,1862 ----
*** a/src/include/foreign/foreign.h
--- b/src/include/foreign/foreign.h
***************
*** 72,78 **** typedef struct ForeignTable
extern ForeignServer *GetForeignServer(Oid serverid);
extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok);
extern UserMapping *GetUserMapping(Oid userid, Oid serverid);
! extern Oid GetUserMappingId(Oid userid, Oid serverid, bool missing_ok);
extern UserMapping *GetUserMappingById(Oid umid);
extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
--- 72,78 ----
extern ForeignServer *GetForeignServer(Oid serverid);
extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok);
extern UserMapping *GetUserMapping(Oid userid, Oid serverid);
! extern Oid GetUserMappingId(Oid userid, Oid serverid);
extern UserMapping *GetUserMappingById(Oid umid);
extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 73,79 **** typedef struct PlannedStmt
bool hasRowSecurity; /* row security applied? */
bool parallelModeNeeded; /* parallel mode required to execute? */
- bool hasForeignJoin; /* Plan has a pushed down foreign join */
} PlannedStmt;
/* macro for fetching the Plan associated with a SubPlan node */
--- 73,78 ----
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 126,133 **** typedef struct PlannerGlobal
bool parallelModeOK; /* parallel mode potentially OK? */
bool parallelModeNeeded; /* parallel mode actually required? */
-
- bool hasForeignJoin; /* does have a pushed down foreign join */
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
--- 126,131 ----
***************
*** 426,436 **** typedef struct PlannerInfo
* in just as for a baserel, except we don't bother with lateral_vars.
*
* If the relation is either a foreign table or a join of foreign tables that
! * all belong to the same foreign server and use the same user mapping, these
! * fields will be set:
*
* serverid - OID of foreign server, if foreign table (else InvalidOid)
! * umid - OID of user mapping, if foreign table (else InvalidOid)
* fdwroutine - function hooks for FDW, if foreign table (else NULL)
* fdw_private - private state for FDW, if foreign table (else NULL)
*
--- 424,434 ----
* in just as for a baserel, except we don't bother with lateral_vars.
*
* If the relation is either a foreign table or a join of foreign tables that
! * all belong to the same foreign server and are assigned to the same user to
! * check access permissions as (ie, checkAsUser), these fields will be set:
*
* serverid - OID of foreign server, if foreign table (else InvalidOid)
! * userid - OID of user to check access as
* fdwroutine - function hooks for FDW, if foreign table (else NULL)
* fdw_private - private state for FDW, if foreign table (else NULL)
*
***************
*** 528,535 **** typedef struct RelOptInfo
/* Information about foreign tables and foreign joins */
Oid serverid; /* identifies server for the table or join */
! Oid umid; /* identifies user mapping for the table or
! * join */
/* use "struct FdwRoutine" to avoid including fdwapi.h here */
struct FdwRoutine *fdwroutine;
void *fdw_private;
--- 526,532 ----
/* Information about foreign tables and foreign joins */
Oid serverid; /* identifies server for the table or join */
! Oid userid; /* identifies user to check access as */
/* use "struct FdwRoutine" to avoid including fdwapi.h here */
struct FdwRoutine *fdwroutine;
void *fdw_private;
*** a/src/include/utils/plancache.h
--- b/src/include/utils/plancache.h
***************
*** 135,141 **** typedef struct CachedPlan
* changes from this value */
int generation; /* parent's generation number for this plan */
int refcount; /* count of live references to this struct */
- bool has_foreign_join; /* plan has pushed down a foreign join */
MemoryContext context; /* context containing this CachedPlan */
} CachedPlan;
--- 135,140 ----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
