codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>59.4. Foreign Data Wrapper Query Planning</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="fdw-helpers.html" title="59.3. Foreign Data Wrapper Helper Functions" /><link rel="next" href="fdw-row-locking.html" title="59.5. Row Locking in Foreign Data Wrappers" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">59.4. Foreign Data Wrapper Query Planning</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="fdw-helpers.html" title="59.3. Foreign Data Wrapper Helper Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="fdwhandler.html" title="Chapter 59. Writing a Foreign Data Wrapper">Up</a></td><th width="60%" align="center">Chapter 59. Writing a Foreign Data Wrapper</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="fdw-row-locking.html" title="59.5. Row Locking in Foreign Data Wrappers">Next</a></td></tr></table><hr /></div><div class="sect1" id="FDW-PLANNING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">59.4. Foreign Data Wrapper Query Planning <a href="#FDW-PLANNING" class="id_link">#</a></h2></div></div></div><p>3 The FDW callback functions <code class="function">GetForeignRelSize</code>,4 <code class="function">GetForeignPaths</code>, <code class="function">GetForeignPlan</code>,5 <code class="function">PlanForeignModify</code>, <code class="function">GetForeignJoinPaths</code>,6 <code class="function">GetForeignUpperPaths</code>, and <code class="function">PlanDirectModify</code>7 must fit into the workings of the <span class="productname">PostgreSQL</span> planner.8 Here are some notes about what they must do.9 </p><p>10 The information in <code class="literal">root</code> and <code class="literal">baserel</code> can be used11 to reduce the amount of information that has to be fetched from the12 foreign table (and therefore reduce the cost).13 <code class="literal">baserel->baserestrictinfo</code> is particularly interesting, as14 it contains restriction quals (<code class="literal">WHERE</code> clauses) that should be15 used to filter the rows to be fetched. (The FDW itself is not required16 to enforce these quals, as the core executor can check them instead.)17 <code class="literal">baserel->reltarget->exprs</code> can be used to determine which18 columns need to be fetched; but note that it only lists columns that19 have to be emitted by the <code class="structname">ForeignScan</code> plan node, not20 columns that are used in qual evaluation but not output by the query.21 </p><p>22 Various private fields are available for the FDW planning functions to23 keep information in. Generally, whatever you store in FDW private fields24 should be palloc'd, so that it will be reclaimed at the end of planning.25 </p><p>26 <code class="literal">baserel->fdw_private</code> is a <code class="type">void</code> pointer that is27 available for FDW planning functions to store information relevant to28 the particular foreign table. The core planner does not touch it except29 to initialize it to NULL when the <code class="literal">RelOptInfo</code> node is created.30 It is useful for passing information forward from31 <code class="function">GetForeignRelSize</code> to <code class="function">GetForeignPaths</code> and/or32 <code class="function">GetForeignPaths</code> to <code class="function">GetForeignPlan</code>, thereby33 avoiding recalculation.34 </p><p>35 <code class="function">GetForeignPaths</code> can identify the meaning of different36 access paths by storing private information in the37 <code class="structfield">fdw_private</code> field of <code class="structname">ForeignPath</code> nodes.38 <code class="structfield">fdw_private</code> is declared as a <code class="type">List</code> pointer, but39 could actually contain anything since the core planner does not touch40 it. However, best practice is to use a representation that's dumpable41 by <code class="function">nodeToString</code>, for use with debugging support available42 in the backend.43 </p><p>44 <code class="function">GetForeignPlan</code> can examine the <code class="structfield">fdw_private</code>45 field of the selected <code class="structname">ForeignPath</code> node, and can generate46 <code class="structfield">fdw_exprs</code> and <code class="structfield">fdw_private</code> lists to be47 placed in the <code class="structname">ForeignScan</code> plan node, where they will be48 available at execution time. Both of these lists must be49 represented in a form that <code class="function">copyObject</code> knows how to copy.50 The <code class="structfield">fdw_private</code> list has no other restrictions and is51 not interpreted by the core backend in any way. The52 <code class="structfield">fdw_exprs</code> list, if not NIL, is expected to contain53 expression trees that are intended to be executed at run time. These54 trees will undergo post-processing by the planner to make them fully55 executable.56 </p><p>57 In <code class="function">GetForeignPlan</code>, generally the passed-in target list can58 be copied into the plan node as-is. The passed <code class="literal">scan_clauses</code> list59 contains the same clauses as <code class="literal">baserel->baserestrictinfo</code>,60 but may be re-ordered for better execution efficiency. In simple cases61 the FDW can just strip <code class="structname">RestrictInfo</code> nodes from the62 <code class="literal">scan_clauses</code> list (using <code class="function">extract_actual_clauses</code>) and put63 all the clauses into the plan node's qual list, which means that all the64 clauses will be checked by the executor at run time. More complex FDWs65 may be able to check some of the clauses internally, in which case those66 clauses can be removed from the plan node's qual list so that the67 executor doesn't waste time rechecking them.68 </p><p>69 As an example, the FDW might identify some restriction clauses of the70 form <em class="replaceable"><code>foreign_variable</code></em> <code class="literal">=</code>71 <em class="replaceable"><code>sub_expression</code></em>, which it determines can be executed on72 the remote server given the locally-evaluated value of the73 <em class="replaceable"><code>sub_expression</code></em>. The actual identification of such a74 clause should happen during <code class="function">GetForeignPaths</code>, since it would75 affect the cost estimate for the path. The path's76 <code class="structfield">fdw_private</code> field would probably include a pointer to77 the identified clause's <code class="structname">RestrictInfo</code> node. Then78 <code class="function">GetForeignPlan</code> would remove that clause from <code class="literal">scan_clauses</code>,79 but add the <em class="replaceable"><code>sub_expression</code></em> to <code class="structfield">fdw_exprs</code>80 to ensure that it gets massaged into executable form. It would probably81 also put control information into the plan node's82 <code class="structfield">fdw_private</code> field to tell the execution functions what83 to do at run time. The query transmitted to the remote server would84 involve something like <code class="literal">WHERE <em class="replaceable"><code>foreign_variable</code></em> =85 $1</code>, with the parameter value obtained at run time from86 evaluation of the <code class="structfield">fdw_exprs</code> expression tree.87 </p><p>88 Any clauses removed from the plan node's qual list must instead be added89 to <code class="literal">fdw_recheck_quals</code> or rechecked by90 <code class="literal">RecheckForeignScan</code> in order to ensure correct behavior91 at the <code class="literal">READ COMMITTED</code> isolation level. When a concurrent92 update occurs for some other table involved in the query, the executor93 may need to verify that all of the original quals are still satisfied for94 the tuple, possibly against a different set of parameter values. Using95 <code class="literal">fdw_recheck_quals</code> is typically easier than implementing checks96 inside <code class="literal">RecheckForeignScan</code>, but this method will be97 insufficient when outer joins have been pushed down, since the join tuples98 in that case might have some fields go to NULL without rejecting the99 tuple entirely.100 </p><p>101 Another <code class="structname">ForeignScan</code> field that can be filled by FDWs102 is <code class="structfield">fdw_scan_tlist</code>, which describes the tuples returned by103 the FDW for this plan node. For simple foreign table scans this can be104 set to <code class="literal">NIL</code>, implying that the returned tuples have the105 row type declared for the foreign table. A non-<code class="symbol">NIL</code> value must be a106 target list (list of <code class="structname">TargetEntry</code>s) containing Vars and/or107 expressions representing the returned columns. This might be used, for108 example, to show that the FDW has omitted some columns that it noticed109 won't be needed for the query. Also, if the FDW can compute expressions110 used by the query more cheaply than can be done locally, it could add111 those expressions to <code class="structfield">fdw_scan_tlist</code>. Note that join112 plans (created from paths made by <code class="function">GetForeignJoinPaths</code>) must113 always supply <code class="structfield">fdw_scan_tlist</code> to describe the set of114 columns they will return.115 </p><p>116 The FDW should always construct at least one path that depends only on117 the table's restriction clauses. In join queries, it might also choose118 to construct path(s) that depend on join clauses, for example119 <em class="replaceable"><code>foreign_variable</code></em> <code class="literal">=</code>120 <em class="replaceable"><code>local_variable</code></em>. Such clauses will not be found in121 <code class="literal">baserel->baserestrictinfo</code> but must be sought in the122 relation's join lists. A path using such a clause is called a123 <span class="quote">“<span class="quote">parameterized path</span>”</span>. It must identify the other relations124 used in the selected join clause(s) with a suitable value of125 <code class="literal">param_info</code>; use <code class="function">get_baserel_parampathinfo</code>126 to compute that value. In <code class="function">GetForeignPlan</code>, the127 <em class="replaceable"><code>local_variable</code></em> portion of the join clause would be added128 to <code class="structfield">fdw_exprs</code>, and then at run time the case works the129 same as for an ordinary restriction clause.130 </p><p>131 If an FDW supports remote joins, <code class="function">GetForeignJoinPaths</code> should132 produce <code class="structname">ForeignPath</code>s for potential remote joins in much133 the same way as <code class="function">GetForeignPaths</code> works for base tables.134 Information about the intended join can be passed forward135 to <code class="function">GetForeignPlan</code> in the same ways described above.136 However, <code class="structfield">baserestrictinfo</code> is not relevant for join137 relations; instead, the relevant join clauses for a particular join are138 passed to <code class="function">GetForeignJoinPaths</code> as a separate parameter139 (<code class="literal">extra->restrictlist</code>).140 </p><p>141 An FDW might additionally support direct execution of some plan actions142 that are above the level of scans and joins, such as grouping or143 aggregation. To offer such options, the FDW should generate paths and144 insert them into the appropriate <em class="firstterm">upper relation</em>. For145 example, a path representing remote aggregation should be inserted into146 the <code class="literal">UPPERREL_GROUP_AGG</code> relation, using <code class="function">add_path</code>.147 This path will be compared on a cost basis with local aggregation148 performed by reading a simple scan path for the foreign relation (note149 that such a path must also be supplied, else there will be an error at150 plan time). If the remote-aggregation path wins, which it usually would,151 it will be converted into a plan in the usual way, by152 calling <code class="function">GetForeignPlan</code>. The recommended place to generate153 such paths is in the <code class="function">GetForeignUpperPaths</code>154 callback function, which is called for each upper relation (i.e., each155 post-scan/join processing step), if all the base relations of the query156 come from the same FDW.157 </p><p>158 <code class="function">PlanForeignModify</code> and the other callbacks described in159 <a class="xref" href="fdw-callbacks.html#FDW-CALLBACKS-UPDATE" title="59.2.4. FDW Routines for Updating Foreign Tables">Section 59.2.4</a> are designed around the assumption160 that the foreign relation will be scanned in the usual way and then161 individual row updates will be driven by a local <code class="literal">ModifyTable</code>162 plan node. This approach is necessary for the general case where an163 update requires reading local tables as well as foreign tables.164 However, if the operation could be executed entirely by the foreign165 server, the FDW could generate a path representing that and insert it166 into the <code class="literal">UPPERREL_FINAL</code> upper relation, where it would167 compete against the <code class="literal">ModifyTable</code> approach. This approach168 could also be used to implement remote <code class="literal">SELECT FOR UPDATE</code>,169 rather than using the row locking callbacks described in170 <a class="xref" href="fdw-callbacks.html#FDW-CALLBACKS-ROW-LOCKING" title="59.2.6. FDW Routines for Row Locking">Section 59.2.6</a>. Keep in mind that a path171 inserted into <code class="literal">UPPERREL_FINAL</code> is responsible for172 implementing <span class="emphasis"><em>all</em></span> behavior of the query.173 </p><p>174 When planning an <code class="command">UPDATE</code> or <code class="command">DELETE</code>,175 <code class="function">PlanForeignModify</code> and <code class="function">PlanDirectModify</code>176 can look up the <code class="structname">RelOptInfo</code>177 struct for the foreign table and make use of the178 <code class="literal">baserel->fdw_private</code> data previously created by the179 scan-planning functions. However, in <code class="command">INSERT</code> the target180 table is not scanned so there is no <code class="structname">RelOptInfo</code> for it.181 The <code class="structname">List</code> returned by <code class="function">PlanForeignModify</code> has182 the same restrictions as the <code class="structfield">fdw_private</code> list of a183 <code class="structname">ForeignScan</code> plan node, that is it must contain only184 structures that <code class="function">copyObject</code> knows how to copy.185 </p><p>186 <code class="command">INSERT</code> with an <code class="literal">ON CONFLICT</code> clause does not187 support specifying the conflict target, as unique constraints or188 exclusion constraints on remote tables are not locally known. This189 in turn implies that <code class="literal">ON CONFLICT DO UPDATE</code> is not supported,190 since the specification is mandatory there.191 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="fdw-helpers.html" title="59.3. Foreign Data Wrapper Helper Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="fdwhandler.html" title="Chapter 59. Writing a Foreign Data Wrapper">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="fdw-row-locking.html" title="59.5. Row Locking in Foreign Data Wrappers">Next</a></td></tr><tr><td width="40%" align="left" valign="top">59.3. Foreign Data Wrapper Helper Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 59.5. Row Locking in Foreign Data Wrappers</td></tr></table></div></body></html>