Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
postgres-fdw.html717 linesDownload Raw Back to html
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>F.38. postgres_fdw — access data stored in external PostgreSQL servers</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="pgwalinspect.html" title="F.37. pg_walinspect — low-level WAL inspection" /><link rel="next" href="seg.html" title="F.39. seg — a datatype for line segments or floating point intervals" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.38. postgres_fdw —3   access data stored in external <span class="productname">PostgreSQL</span>4   servers</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgwalinspect.html" title="F.37. pg_walinspect — low-level WAL inspection">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</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="seg.html" title="F.39. seg — a datatype for line segments or floating point intervals">Next</a></td></tr></table><hr /></div><div class="sect1" id="POSTGRES-FDW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.38. postgres_fdw —5   access data stored in external <span class="productname">PostgreSQL</span>6   servers <a href="#POSTGRES-FDW" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-OPTIONS">F.38.1. FDW Options of postgres_fdw</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-FUNCTIONS">F.38.2. Functions</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-CONNECTION-MANAGEMENT">F.38.3. Connection Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-TRANSACTION-MANAGEMENT">F.38.4. Transaction Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-OPTIMIZATION">F.38.5. Remote Query Optimization</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT">F.38.6. Remote Query Execution Environment</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-CROSS-VERSION-COMPATIBILITY">F.38.7. Cross-Version Compatibility</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-CONFIGURATION-PARAMETERS">F.38.8. Configuration Parameters</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-EXAMPLES">F.38.9. Examples</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-AUTHOR">F.38.10. Author</a></span></dt></dl></div><a id="id-1.11.7.48.2" class="indexterm"></a><p>7  The <code class="filename">postgres_fdw</code> module provides the foreign-data wrapper8  <code class="literal">postgres_fdw</code>, which can be used to access data9  stored in external <span class="productname">PostgreSQL</span> servers.10 </p><p>11  The functionality provided by this module overlaps substantially12  with the functionality of the older <a class="xref" href="dblink.html" title="F.12. dblink — connect to other PostgreSQL databases">dblink</a> module.13  But <code class="filename">postgres_fdw</code> provides more transparent and14  standards-compliant syntax for accessing remote tables, and can give15  better performance in many cases.16 </p><p>17  To prepare for remote access using <code class="filename">postgres_fdw</code>:18  </p><div class="orderedlist"><ol class="orderedlist compact" type="1"><li class="listitem"><p>19     Install the  <code class="filename">postgres_fdw</code> extension using <a class="xref" href="sql-createextension.html" title="CREATE EXTENSION"><span class="refentrytitle">CREATE EXTENSION</span></a>.20    </p></li><li class="listitem"><p>21     Create a foreign server object, using <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>,22     to represent each remote database you want to connect to.23     Specify connection information, except <code class="literal">user</code> and24     <code class="literal">password</code>, as options of the server object.25    </p></li><li class="listitem"><p>26     Create a user mapping, using <a class="xref" href="sql-createusermapping.html" title="CREATE USER MAPPING"><span class="refentrytitle">CREATE USER MAPPING</span></a>, for27     each database user you want to allow to access each foreign server.28     Specify the remote user name and password to use as29     <code class="literal">user</code> and <code class="literal">password</code> options of the30     user mapping.31    </p></li><li class="listitem"><p>32     Create a foreign table, using <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>33     or <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>,34     for each remote table you want to access.  The columns of the foreign35     table must match the referenced remote table.  You can, however, use36     table and/or column names different from the remote table's, if you37     specify the correct remote names as options of the foreign table object.38    </p></li></ol></div><p>39 </p><p>40  Now you need only <code class="command">SELECT</code> from a foreign table to access41  the data stored in its underlying remote table.  You can also modify42  the remote table using <code class="command">INSERT</code>, <code class="command">UPDATE</code>,43  <code class="command">DELETE</code>, <code class="command">COPY</code>, or44  <code class="command">TRUNCATE</code>.45  (Of course, the remote user you have specified in your user mapping must46  have privileges to do these things.)47 </p><p>48  Note that the <code class="literal">ONLY</code> option specified in49  <code class="command">SELECT</code>, <code class="command">UPDATE</code>,50  <code class="command">DELETE</code> or <code class="command">TRUNCATE</code>51  has no effect when accessing or modifying the remote table.52 </p><p>53  Note that <code class="filename">postgres_fdw</code> currently lacks support for54  <code class="command">INSERT</code> statements with an <code class="literal">ON CONFLICT DO55  UPDATE</code> clause.  However, the <code class="literal">ON CONFLICT DO NOTHING</code>56  clause is supported, provided a unique index inference specification57  is omitted.58  Note also that <code class="filename">postgres_fdw</code> supports row movement59  invoked by <code class="command">UPDATE</code> statements executed on partitioned60  tables, but it currently does not handle the case where a remote partition61  chosen to insert a moved row into is also an <code class="command">UPDATE</code>62  target partition that will be updated elsewhere in the same command.63 </p><p>64  It is generally recommended that the columns of a foreign table be declared65  with exactly the same data types, and collations if applicable, as the66  referenced columns of the remote table.  Although <code class="filename">postgres_fdw</code>67  is currently rather forgiving about performing data type conversions at68  need, surprising semantic anomalies may arise when types or collations do69  not match, due to the remote server interpreting query conditions70  differently from the local server.71 </p><p>72  Note that a foreign table can be declared with fewer columns, or with a73  different column order, than its underlying remote table has.  Matching74  of columns to the remote table is by name, not position.75 </p><div class="sect2" id="POSTGRES-FDW-OPTIONS"><div class="titlepage"><div><div><h3 class="title">F.38.1. FDW Options of postgres_fdw <a href="#POSTGRES-FDW-OPTIONS" class="id_link">#</a></h3></div></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-CONNECTION"><div class="titlepage"><div><div><h4 class="title">F.38.1.1. Connection Options <a href="#POSTGRES-FDW-OPTIONS-CONNECTION" class="id_link">#</a></h4></div></div></div><p>76    A foreign server using the <code class="filename">postgres_fdw</code> foreign data wrapper77    can have the same options that <span class="application">libpq</span> accepts in78    connection strings, as described in <a class="xref" href="libpq-connect.html#LIBPQ-PARAMKEYWORDS" title="34.1.2. Parameter Key Words">Section 34.1.2</a>,79    except that these options are not allowed or have special handling:80 81    </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>82       <code class="literal">user</code>, <code class="literal">password</code> and <code class="literal">sslpassword</code> (specify these83       in a user mapping, instead, or use a service file)84      </p></li><li class="listitem"><p>85       <code class="literal">client_encoding</code> (this is automatically set from the local86       server encoding)87      </p></li><li class="listitem"><p>88       <code class="literal">application_name</code> - this may appear in89       <span class="emphasis"><em>either or both</em></span> a connection and90       <a class="xref" href="postgres-fdw.html#GUC-PGFDW-APPLICATION-NAME">postgres_fdw.application_name</a>.91       If both are present, <code class="varname">postgres_fdw.application_name</code>92       overrides the connection setting.93       Unlike <span class="application">libpq</span>,94       <code class="filename">postgres_fdw</code> allows95       <code class="varname">application_name</code> to include96       <span class="quote">“<span class="quote">escape sequences</span>”</span>.97       See <a class="xref" href="postgres-fdw.html#GUC-PGFDW-APPLICATION-NAME">postgres_fdw.application_name</a> for details.98      </p></li><li class="listitem"><p>99       <code class="literal">fallback_application_name</code> (always set to100       <code class="literal">postgres_fdw</code>)101      </p></li><li class="listitem"><p>102       <code class="literal">sslkey</code> and <code class="literal">sslcert</code> - these may103       appear in <span class="emphasis"><em>either or both</em></span> a connection and a user104       mapping. If both are present, the user mapping setting overrides the105       connection setting.106      </p></li></ul></div><p>107   </p><p>108    Only superusers may create or modify user mappings with the109    <code class="literal">sslcert</code> or <code class="literal">sslkey</code> settings.110   </p><p>111    Non-superusers may connect to foreign servers using password112    authentication or with GSSAPI delegated credentials, so specify the113    <code class="literal">password</code> option for user mappings belonging to114    non-superusers where password authentication is required.115   </p><p>116    A superuser may override this check on a per-user-mapping basis by setting117    the user mapping option <code class="literal">password_required 'false'</code>, e.g.,118</p><pre class="programlisting">119ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw120OPTIONS (ADD password_required 'false');121</pre><p>122    To prevent unprivileged users from exploiting the authentication rights123    of the unix user the postgres server is running as to escalate to superuser124    rights, only the superuser may set this option on a user mapping.125    </p><p>126    Care is required to ensure that this does not allow the mapped127    user the ability to connect as superuser to the mapped database per128    CVE-2007-3278 and CVE-2007-6601. Don't set129    <code class="literal">password_required=false</code>130    on the <code class="literal">public</code> role. Keep in mind that the mapped131    user can potentially use any client certificates,132    <code class="filename">.pgpass</code>,133    <code class="filename">.pg_service.conf</code> etc. in the unix home directory of the134    system user the postgres server runs as. They can also use any trust135    relationship granted by authentication modes like <code class="literal">peer</code>136    or <code class="literal">ident</code> authentication.137   </p></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-OBJECT-NAME"><div class="titlepage"><div><div><h4 class="title">F.38.1.2. Object Name Options <a href="#POSTGRES-FDW-OPTIONS-OBJECT-NAME" class="id_link">#</a></h4></div></div></div><p>138    These options can be used to control the names used in SQL statements139    sent to the remote <span class="productname">PostgreSQL</span> server.  These140    options are needed when a foreign table is created with names different141    from the underlying remote table's names.142   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">schema_name</code> (<code class="type">string</code>)</span></dt><dd><p>143       This option, which can be specified for a foreign table, gives the144       schema name to use for the foreign table on the remote server.  If this145       option is omitted, the name of the foreign table's schema is used.146      </p></dd><dt><span class="term"><code class="literal">table_name</code> (<code class="type">string</code>)</span></dt><dd><p>147       This option, which can be specified for a foreign table, gives the148       table name to use for the foreign table on the remote server.  If this149       option is omitted, the foreign table's name is used.150      </p></dd><dt><span class="term"><code class="literal">column_name</code> (<code class="type">string</code>)</span></dt><dd><p>151       This option, which can be specified for a column of a foreign table,152       gives the column name to use for the column on the remote server.153       If this option is omitted, the column's name is used.154      </p></dd></dl></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-COST-ESTIMATION"><div class="titlepage"><div><div><h4 class="title">F.38.1.3. Cost Estimation Options <a href="#POSTGRES-FDW-OPTIONS-COST-ESTIMATION" class="id_link">#</a></h4></div></div></div><p>155    <code class="filename">postgres_fdw</code> retrieves remote data by executing queries156    against remote servers, so ideally the estimated cost of scanning a157    foreign table should be whatever it costs to be done on the remote158    server, plus some overhead for communication.  The most reliable way to159    get such an estimate is to ask the remote server and then add something160    for overhead — but for simple queries, it may not be worth the cost161    of an additional remote query to get a cost estimate.162    So <code class="filename">postgres_fdw</code> provides the following options to control163    how cost estimation is done:164   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">use_remote_estimate</code> (<code class="type">boolean</code>)</span></dt><dd><p>165       This option, which can be specified for a foreign table or a foreign166       server, controls whether <code class="filename">postgres_fdw</code> issues remote167       <code class="command">EXPLAIN</code> commands to obtain cost estimates.168       A setting for a foreign table overrides any setting for its server,169       but only for that table.170       The default is <code class="literal">false</code>.171      </p></dd><dt><span class="term"><code class="literal">fdw_startup_cost</code> (<code class="type">floating point</code>)</span></dt><dd><p>172       This option, which can be specified for a foreign server, is a floating173       point value that is added to the estimated startup cost of any174       foreign-table scan on that server.  This represents the additional175       overhead of establishing a connection, parsing and planning the query on176       the remote side, etc.177       The default value is <code class="literal">100</code>.178      </p></dd><dt><span class="term"><code class="literal">fdw_tuple_cost</code> (<code class="type">floating point</code>)</span></dt><dd><p>179       This option, which can be specified for a foreign server, is a floating180       point value that is used as extra cost per-tuple for foreign-table181       scans on that server.  This represents the additional overhead of182       data transfer between servers.  You might increase or decrease this183       number to reflect higher or lower network delay to the remote server.184       The default value is <code class="literal">0.01</code>.185      </p></dd></dl></div><p>186    When <code class="literal">use_remote_estimate</code> is true,187    <code class="filename">postgres_fdw</code> obtains row count and cost estimates from the188    remote server and then adds <code class="literal">fdw_startup_cost</code> and189    <code class="literal">fdw_tuple_cost</code> to the cost estimates.  When190    <code class="literal">use_remote_estimate</code> is false,191    <code class="filename">postgres_fdw</code> performs local row count and cost estimation192    and then adds <code class="literal">fdw_startup_cost</code> and193    <code class="literal">fdw_tuple_cost</code> to the cost estimates.  This local194    estimation is unlikely to be very accurate unless local copies of the195    remote table's statistics are available.  Running196    <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> on the foreign table is the way to update197    the local statistics; this will perform a scan of the remote table and198    then calculate and store statistics just as though the table were local.199    Keeping local statistics can be a useful way to reduce per-query planning200    overhead for a remote table — but if the remote table is201    frequently updated, the local statistics will soon be obsolete.202   </p><p>203    The following option controls how such an <code class="command">ANALYZE</code>204    operation behaves:205   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">analyze_sampling</code> (<code class="type">string</code>)</span></dt><dd><p>206       This option, which can be specified for a foreign table or a foreign207       server, determines if <code class="command">ANALYZE</code> on a foreign table208       samples the data on the remote side, or reads and transfers all data209       and performs the sampling locally. The supported values210       are <code class="literal">off</code>, <code class="literal">random</code>,211       <code class="literal">system</code>, <code class="literal">bernoulli</code>212       and <code class="literal">auto</code>. <code class="literal">off</code> disables remote213       sampling, so all data are transferred and sampled locally.214       <code class="literal">random</code> performs remote sampling using the215       <code class="literal">random()</code> function to choose returned rows,216       while <code class="literal">system</code> and <code class="literal">bernoulli</code> rely217       on the built-in <code class="literal">TABLESAMPLE</code> methods of those218       names. <code class="literal">random</code> works on all remote server versions,219       while <code class="literal">TABLESAMPLE</code> is supported only since 9.5.220       <code class="literal">auto</code> (the default) picks the recommended sampling221       method automatically; currently it means222       either <code class="literal">bernoulli</code> or <code class="literal">random</code>223       depending on the remote server version.224      </p></dd></dl></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION"><div class="titlepage"><div><div><h4 class="title">F.38.1.4. Remote Execution Options <a href="#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION" class="id_link">#</a></h4></div></div></div><p>225    By default, only <code class="literal">WHERE</code> clauses using built-in operators and226    functions will be considered for execution on the remote server.  Clauses227    involving non-built-in functions are checked locally after rows are228    fetched.  If such functions are available on the remote server and can be229    relied on to produce the same results as they do locally, performance can230    be improved by sending such <code class="literal">WHERE</code> clauses for remote231    execution.  This behavior can be controlled using the following option:232   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">extensions</code> (<code class="type">string</code>)</span></dt><dd><p>233       This option is a comma-separated list of names234       of <span class="productname">PostgreSQL</span> extensions that are installed, in235       compatible versions, on both the local and remote servers.  Functions236       and operators that are immutable and belong to a listed extension will237       be considered shippable to the remote server.238       This option can only be specified for foreign servers, not per-table.239      </p><p>240       When using the <code class="literal">extensions</code> option, <span class="emphasis"><em>it is the241       user's responsibility</em></span> that the listed extensions exist and behave242       identically on both the local and remote servers.  Otherwise, remote243       queries may fail or behave unexpectedly.244      </p></dd><dt><span class="term"><code class="literal">fetch_size</code> (<code class="type">integer</code>)</span></dt><dd><p>245       This option specifies the number of rows <code class="filename">postgres_fdw</code>246       should get in each fetch operation. It can be specified for a foreign247       table or a foreign server. The option specified on a table overrides248       an option specified for the server.249       The default is <code class="literal">100</code>.250      </p></dd><dt><span class="term"><code class="literal">batch_size</code> (<code class="type">integer</code>)</span></dt><dd><p>251       This option specifies the number of rows <code class="filename">postgres_fdw</code>252       should insert in each insert operation. It can be specified for a253       foreign table or a foreign server. The option specified on a table254       overrides an option specified for the server.255       The default is <code class="literal">1</code>.256      </p><p>257       Note the actual number of rows <code class="filename">postgres_fdw</code> inserts at258       once depends on the number of columns and the provided259       <code class="literal">batch_size</code> value. The batch is executed as a single260       query, and the libpq protocol (which <code class="filename">postgres_fdw</code>261       uses to connect to a remote server) limits the number of parameters in a262       single query to 65535. When the number of columns * <code class="literal">batch_size</code>263       exceeds the limit, the <code class="literal">batch_size</code> will be adjusted to264       avoid an error.265      </p><p>266       This option also applies when copying into foreign tables.  In that case267       the actual number of rows <code class="filename">postgres_fdw</code> copies at268       once is determined in a similar way to the insert case, but it is269       limited to at most 1000 due to implementation restrictions of the270       <code class="command">COPY</code> command.271      </p></dd></dl></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-ASYNCHRONOUS-EXECUTION"><div class="titlepage"><div><div><h4 class="title">F.38.1.5. Asynchronous Execution Options <a href="#POSTGRES-FDW-OPTIONS-ASYNCHRONOUS-EXECUTION" class="id_link">#</a></h4></div></div></div><p>272    <code class="filename">postgres_fdw</code> supports asynchronous execution, which273    runs multiple parts of an <code class="structname">Append</code> node274    concurrently rather than serially to improve performance.275    This execution can be controlled using the following option:276   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">async_capable</code> (<code class="type">boolean</code>)</span></dt><dd><p>277       This option controls whether <code class="filename">postgres_fdw</code> allows278       foreign tables to be scanned concurrently for asynchronous execution.279       It can be specified for a foreign table or a foreign server.280       A table-level option overrides a server-level option.281       The default is <code class="literal">false</code>.282      </p><p>283       In order to ensure that the data being returned from a foreign server284       is consistent, <code class="filename">postgres_fdw</code> will only open one285       connection for a given foreign server and will run all queries against286       that server sequentially even if there are multiple foreign tables287       involved, unless those tables are subject to different user mappings.288       In such a case, it may be more performant to disable this option to289       eliminate the overhead associated with running queries asynchronously.290      </p><p>291       Asynchronous execution is applied even when an292       <code class="structname">Append</code> node contains subplan(s) executed293       synchronously as well as subplan(s) executed asynchronously.294       In such a case, if the asynchronous subplans are ones processed using295       <code class="filename">postgres_fdw</code>, tuples from the asynchronous296       subplans are not returned until after at least one synchronous subplan297       returns all tuples, as that subplan is executed while the asynchronous298       subplans are waiting for the results of asynchronous queries sent to299       foreign servers.300       This behavior might change in a future release.301      </p></dd></dl></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-TRANSACTION-MANAGEMENT"><div class="titlepage"><div><div><h4 class="title">F.38.1.6. Transaction Management Options <a href="#POSTGRES-FDW-OPTIONS-TRANSACTION-MANAGEMENT" class="id_link">#</a></h4></div></div></div><p>302    As described in the Transaction Management section, in303    <code class="filename">postgres_fdw</code> transactions are managed by creating304    corresponding remote transactions, and subtransactions are managed by305    creating corresponding remote subtransactions. When multiple remote306    transactions are involved in the current local transaction, by default307    <code class="filename">postgres_fdw</code> commits or aborts those remote308    transactions serially when the local transaction is committed or aborted.309    When multiple remote subtransactions are involved in the current local310    subtransaction, by default <code class="filename">postgres_fdw</code> commits or311    aborts those remote subtransactions serially when the local subtransaction312    is committed or aborted.313    Performance can be improved with the following options:314   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">parallel_commit</code> (<code class="type">boolean</code>)</span></dt><dd><p>315       This option controls whether <code class="filename">postgres_fdw</code> commits,316       in parallel, remote transactions opened on a foreign server in a local317       transaction when the local transaction is committed. This setting also318       applies to remote and local subtransactions. This option can only be319       specified for foreign servers, not per-table. The default is320       <code class="literal">false</code>.321      </p></dd><dt><span class="term"><code class="literal">parallel_abort</code> (<code class="type">boolean</code>)</span></dt><dd><p>322       This option controls whether <code class="filename">postgres_fdw</code> aborts,323       in parallel, remote transactions opened on a foreign server in a local324       transaction when the local transaction is aborted. This setting also325       applies to remote and local subtransactions. This option can only be326       specified for foreign servers, not per-table. The default is327       <code class="literal">false</code>.328      </p></dd></dl></div><p>329    If multiple foreign servers with these options enabled are involved in a330    local transaction, multiple remote transactions on those foreign servers331    are committed or aborted in parallel across those foreign servers when332    the local transaction is committed or aborted.333   </p><p>334    When these options are enabled, a foreign server with many remote335    transactions may see a negative performance impact when the local336    transaction is committed or aborted.337   </p></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-UPDATABILITY"><div class="titlepage"><div><div><h4 class="title">F.38.1.7. Updatability Options <a href="#POSTGRES-FDW-OPTIONS-UPDATABILITY" class="id_link">#</a></h4></div></div></div><p>338    By default all foreign tables using <code class="filename">postgres_fdw</code> are assumed339    to be updatable.  This may be overridden using the following option:340   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">updatable</code> (<code class="type">boolean</code>)</span></dt><dd><p>341       This option controls whether <code class="filename">postgres_fdw</code> allows foreign342       tables to be modified using <code class="command">INSERT</code>, <code class="command">UPDATE</code> and343       <code class="command">DELETE</code> commands.  It can be specified for a foreign table344       or a foreign server.  A table-level option overrides a server-level345       option.346       The default is <code class="literal">true</code>.347      </p><p>348       Of course, if the remote table is not in fact updatable, an error349       would occur anyway.  Use of this option primarily allows the error to350       be thrown locally without querying the remote server.  Note however351       that the <code class="literal">information_schema</code> views will report a352       <code class="filename">postgres_fdw</code> foreign table to be updatable (or not)353       according to the setting of this option, without any check of the354       remote server.355      </p></dd></dl></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-TRUNCATABILITY"><div class="titlepage"><div><div><h4 class="title">F.38.1.8. Truncatability Options <a href="#POSTGRES-FDW-OPTIONS-TRUNCATABILITY" class="id_link">#</a></h4></div></div></div><p>356    By default all foreign tables using <code class="filename">postgres_fdw</code> are assumed357    to be truncatable.  This may be overridden using the following option:358   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">truncatable</code> (<code class="type">boolean</code>)</span></dt><dd><p>359       This option controls whether <code class="filename">postgres_fdw</code> allows360       foreign tables to be truncated using the <code class="command">TRUNCATE</code>361       command. It can be specified for a foreign table or a foreign server.362       A table-level option overrides a server-level option.363       The default is <code class="literal">true</code>.364      </p><p>365       Of course, if the remote table is not in fact truncatable, an error366       would occur anyway.  Use of this option primarily allows the error to367       be thrown locally without querying the remote server.368      </p></dd></dl></div></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-IMPORTING"><div class="titlepage"><div><div><h4 class="title">F.38.1.9. Importing Options <a href="#POSTGRES-FDW-OPTIONS-IMPORTING" class="id_link">#</a></h4></div></div></div><p>369    <code class="filename">postgres_fdw</code> is able to import foreign table definitions370    using <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>.  This command creates371    foreign table definitions on the local server that match tables or372    views present on the remote server.  If the remote tables to be imported373    have columns of user-defined data types, the local server must have374    compatible types of the same names.375   </p><p>376    Importing behavior can be customized with the following options377    (given in the <code class="command">IMPORT FOREIGN SCHEMA</code> command):378   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">import_collate</code> (<code class="type">boolean</code>)</span></dt><dd><p>379       This option controls whether column <code class="literal">COLLATE</code> options380       are included in the definitions of foreign tables imported381       from a foreign server. The default is <code class="literal">true</code>.  You might382       need to turn this off if the remote server has a different set of383       collation names than the local server does, which is likely to be the384       case if it's running on a different operating system.385       If you do so, however, there is a very severe risk that the imported386       table columns' collations will not match the underlying data, resulting387       in anomalous query behavior.388      </p><p>389       Even when this parameter is set to <code class="literal">true</code>, importing390       columns whose collation is the remote server's default can be risky.391       They will be imported with <code class="literal">COLLATE "default"</code>, which392       will select the local server's default collation, which could be393       different.394      </p></dd><dt><span class="term"><code class="literal">import_default</code> (<code class="type">boolean</code>)</span></dt><dd><p>395       This option controls whether column <code class="literal">DEFAULT</code> expressions396       are included in the definitions of foreign tables imported397       from a foreign server. The default is <code class="literal">false</code>.  If you398       enable this option, be wary of defaults that might get computed399       differently on the local server than they would be on the remote400       server; <code class="function">nextval()</code> is a common source of problems.401       The <code class="command">IMPORT</code> will fail altogether if an imported default402       expression uses a function or operator that does not exist locally.403      </p></dd><dt><span class="term"><code class="literal">import_generated</code> (<code class="type">boolean</code>)</span></dt><dd><p>404       This option controls whether column <code class="literal">GENERATED</code> expressions405       are included in the definitions of foreign tables imported406       from a foreign server. The default is <code class="literal">true</code>.407       The <code class="command">IMPORT</code> will fail altogether if an imported generated408       expression uses a function or operator that does not exist locally.409      </p></dd><dt><span class="term"><code class="literal">import_not_null</code> (<code class="type">boolean</code>)</span></dt><dd><p>410       This option controls whether column <code class="literal">NOT NULL</code>411       constraints are included in the definitions of foreign tables imported412       from a foreign server. The default is <code class="literal">true</code>.413      </p></dd></dl></div><p>414    Note that constraints other than <code class="literal">NOT NULL</code> will never be415    imported from the remote tables.  Although <span class="productname">PostgreSQL</span>416    does support check constraints on foreign tables, there is no417    provision for importing them automatically, because of the risk that a418    constraint expression could evaluate differently on the local and remote419    servers.  Any such inconsistency in the behavior of a check420    constraint could lead to hard-to-detect errors in query optimization.421    So if you wish to import check constraints, you must do so422    manually, and you should verify the semantics of each one carefully.423    For more detail about the treatment of check constraints on424    foreign tables, see <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.425   </p><p>426    Tables or foreign tables which are partitions of some other table are427    imported only when they are explicitly specified in428    <code class="literal">LIMIT TO</code> clause.  Otherwise they are automatically429    excluded from <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>.430    Since all data can be accessed through the partitioned table431    which is the root of the partitioning hierarchy, importing only432    partitioned tables should allow access to all the data without433    creating extra objects.434   </p></div><div class="sect3" id="POSTGRES-FDW-OPTIONS-CONNECTION-MANAGEMENT"><div class="titlepage"><div><div><h4 class="title">F.38.1.10. Connection Management Options <a href="#POSTGRES-FDW-OPTIONS-CONNECTION-MANAGEMENT" class="id_link">#</a></h4></div></div></div><p>435     By default, all connections that <code class="filename">postgres_fdw</code>436     establishes to foreign servers are kept open in the local session437     for re-use.438    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">keep_connections</code> (<code class="type">boolean</code>)</span></dt><dd><p>439        This option controls whether <code class="filename">postgres_fdw</code> keeps440        the connections to the foreign server open so that subsequent441        queries can re-use them. It can only be specified for a foreign server.442        The default is <code class="literal">on</code>. If set to <code class="literal">off</code>,443        all connections to this foreign server will be discarded at the end of444        each transaction.445      </p></dd></dl></div></div></div><div class="sect2" id="POSTGRES-FDW-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">F.38.2. Functions <a href="#POSTGRES-FDW-FUNCTIONS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="function">postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record</code></span></dt><dd><p>446      This function returns the foreign server names of all the open447      connections that <code class="filename">postgres_fdw</code> established from448      the local session to the foreign servers. It also returns whether449      each connection is valid or not. <code class="literal">false</code> is returned450      if the foreign server connection is used in the current local451      transaction but its foreign server or user mapping is changed or452      dropped (Note that server name of an invalid connection will be453      <code class="literal">NULL</code> if the server is dropped),454      and then such invalid connection will be closed at455      the end of that transaction. <code class="literal">true</code> is returned456      otherwise. If there are no open connections, no record is returned.457      Example usage of the function:458</p><pre class="screen">459postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;460 server_name | valid461-------------+-------462 loopback1   | t463 loopback2   | f464</pre><p>465     </p></dd><dt><span class="term"><code class="function">postgres_fdw_disconnect(server_name text) returns boolean</code></span></dt><dd><p>466      This function discards the open connections that are established by467      <code class="filename">postgres_fdw</code> from the local session to468      the foreign server with the given name.  Note that there can be469      multiple connections to the given server using different user mappings.470      If the connections are used in the current local transaction,471      they are not disconnected and warning messages are reported.472      This function returns <code class="literal">true</code> if it disconnects473      at least one connection, otherwise <code class="literal">false</code>.474      If no foreign server with the given name is found, an error is reported.475      Example usage of the function:476</p><pre class="screen">477postgres=# SELECT postgres_fdw_disconnect('loopback1');478 postgres_fdw_disconnect479-------------------------480 t481</pre><p>482     </p></dd><dt><span class="term"><code class="function">postgres_fdw_disconnect_all() returns boolean</code></span></dt><dd><p>483      This function discards all the open connections that are established by484      <code class="filename">postgres_fdw</code> from the local session to485      foreign servers.  If the connections are used in the current local486      transaction, they are not disconnected and warning messages are reported.487      This function returns <code class="literal">true</code> if it disconnects488      at least one connection, otherwise <code class="literal">false</code>.489      Example usage of the function:490</p><pre class="screen">491postgres=# SELECT postgres_fdw_disconnect_all();492 postgres_fdw_disconnect_all493-----------------------------494 t495</pre><p>496     </p></dd></dl></div></div><div class="sect2" id="POSTGRES-FDW-CONNECTION-MANAGEMENT"><div class="titlepage"><div><div><h3 class="title">F.38.3. Connection Management <a href="#POSTGRES-FDW-CONNECTION-MANAGEMENT" class="id_link">#</a></h3></div></div></div><p>497   <code class="filename">postgres_fdw</code> establishes a connection to a498   foreign server during the first query that uses a foreign table499   associated with the foreign server.  By default this connection500   is kept and re-used for subsequent queries in the same session.501   This behavior can be controlled using502   <code class="literal">keep_connections</code> option for a foreign server. If503   multiple user identities (user mappings) are used to access the foreign504   server, a connection is established for each user mapping.505  </p><p>506   When changing the definition of or removing a foreign server or507   a user mapping, the associated connections are closed.508   But note that if any connections are in use in the current local transaction,509   they are kept until the end of the transaction.510   Closed connections will be re-established when they are necessary511   by future queries using a foreign table.512  </p><p>513   Once a connection to a foreign server has been established,514   it's by default kept until the local or corresponding remote515   session exits.  To disconnect a connection explicitly,516   <code class="literal">keep_connections</code> option for a foreign server517   may be disabled, or518   <code class="function">postgres_fdw_disconnect</code> and519   <code class="function">postgres_fdw_disconnect_all</code> functions520   may be used.  For example, these are useful to close521   connections that are no longer necessary, thereby releasing522   connections on the foreign server.523  </p></div><div class="sect2" id="POSTGRES-FDW-TRANSACTION-MANAGEMENT"><div class="titlepage"><div><div><h3 class="title">F.38.4. Transaction Management <a href="#POSTGRES-FDW-TRANSACTION-MANAGEMENT" class="id_link">#</a></h3></div></div></div><p>524   During a query that references any remote tables on a foreign server,525   <code class="filename">postgres_fdw</code> opens a transaction on the526   remote server if one is not already open corresponding to the current527   local transaction.  The remote transaction is committed or aborted when528   the local transaction commits or aborts.  Savepoints are similarly529   managed by creating corresponding remote savepoints.530  </p><p>531   The remote transaction uses <code class="literal">SERIALIZABLE</code>532   isolation level when the local transaction has <code class="literal">SERIALIZABLE</code>533   isolation level; otherwise it uses <code class="literal">REPEATABLE READ</code>534   isolation level.  This choice ensures that if a query performs multiple535   table scans on the remote server, it will get snapshot-consistent results536   for all the scans.  A consequence is that successive queries within a537   single transaction will see the same data from the remote server, even if538   concurrent updates are occurring on the remote server due to other539   activities.  That behavior would be expected anyway if the local540   transaction uses <code class="literal">SERIALIZABLE</code> or <code class="literal">REPEATABLE READ</code>541   isolation level, but it might be surprising for a <code class="literal">READ542   COMMITTED</code> local transaction.  A future543   <span class="productname">PostgreSQL</span> release might modify these rules.544  </p><p>545   Note that it is currently not supported by546   <code class="filename">postgres_fdw</code> to prepare the remote transaction for547   two-phase commit.548  </p></div><div class="sect2" id="POSTGRES-FDW-REMOTE-QUERY-OPTIMIZATION"><div class="titlepage"><div><div><h3 class="title">F.38.5. Remote Query Optimization <a href="#POSTGRES-FDW-REMOTE-QUERY-OPTIMIZATION" class="id_link">#</a></h3></div></div></div><p>549   <code class="filename">postgres_fdw</code> attempts to optimize remote queries to reduce550   the amount of data transferred from foreign servers.  This is done by551   sending query <code class="literal">WHERE</code> clauses to the remote server for552   execution, and by not retrieving table columns that are not needed for553   the current query.  To reduce the risk of misexecution of queries,554   <code class="literal">WHERE</code> clauses are not sent to the remote server unless they use555   only data types, operators, and functions that are built-in or belong to an556   extension that's listed in the foreign server's <code class="literal">extensions</code>557   option.  Operators and functions in such clauses must558   be <code class="literal">IMMUTABLE</code> as well.559   For an <code class="command">UPDATE</code> or <code class="command">DELETE</code> query,560   <code class="filename">postgres_fdw</code> attempts to optimize the query execution by561   sending the whole query to the remote server if there are no query562   <code class="literal">WHERE</code> clauses that cannot be sent to the remote server,563   no local joins for the query, no row-level local <code class="literal">BEFORE</code> or564   <code class="literal">AFTER</code> triggers or stored generated columns on the target565   table, and no <code class="literal">CHECK OPTION</code> constraints from parent566   views.  In <code class="command">UPDATE</code>,567   expressions to assign to target columns must use only built-in data types,568   <code class="literal">IMMUTABLE</code> operators, or <code class="literal">IMMUTABLE</code> functions,569   to reduce the risk of misexecution of the query.570  </p><p>571   When <code class="filename">postgres_fdw</code> encounters a join between foreign tables on572   the same foreign server, it sends the entire join to the foreign server,573   unless for some reason it believes that it will be more efficient to fetch574   rows from each table individually, or unless the table references involved575   are subject to different user mappings.  While sending the <code class="literal">JOIN</code>576   clauses, it takes the same precautions as mentioned above for the577   <code class="literal">WHERE</code> clauses.578  </p><p>579   The query that is actually sent to the remote server for execution can580   be examined using <code class="command">EXPLAIN VERBOSE</code>.581  </p></div><div class="sect2" id="POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT"><div class="titlepage"><div><div><h3 class="title">F.38.6. Remote Query Execution Environment <a href="#POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT" class="id_link">#</a></h3></div></div></div><p>582   In the remote sessions opened by <code class="filename">postgres_fdw</code>,583   the <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> parameter is set to584   just <code class="literal">pg_catalog</code>, so that only built-in objects are visible585   without schema qualification.  This is not an issue for queries586   generated by <code class="filename">postgres_fdw</code> itself, because it always587   supplies such qualification.  However, this can pose a hazard for588   functions that are executed on the remote server via triggers or rules589   on remote tables.  For example, if a remote table is actually a view,590   any functions used in that view will be executed with the restricted591   search path.  It is recommended to schema-qualify all names in such592   functions, or else attach <code class="literal">SET search_path</code> options593   (see <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>) to such functions594   to establish their expected search path environment.595  </p><p>596   <code class="filename">postgres_fdw</code> likewise establishes remote session settings597   for various parameters:598   </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>599      <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> is set to <code class="literal">UTC</code>600     </p></li><li class="listitem"><p>601      <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> is set to <code class="literal">ISO</code>602     </p></li><li class="listitem"><p>603      <a class="xref" href="runtime-config-client.html#GUC-INTERVALSTYLE">IntervalStyle</a> is set to <code class="literal">postgres</code>604     </p></li><li class="listitem"><p>605      <a class="xref" href="runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS">extra_float_digits</a> is set to <code class="literal">3</code> for remote606      servers 9.0 and newer and is set to <code class="literal">2</code> for older versions607     </p></li></ul></div><p>608   These are less likely to be problematic than <code class="varname">search_path</code>, but609   can be handled with function <code class="literal">SET</code> options if the need arises.610  </p><p>611   It is <span class="emphasis"><em>not</em></span> recommended that you override this behavior by612   changing the session-level settings of these parameters; that is likely613   to cause <code class="filename">postgres_fdw</code> to malfunction.614  </p></div><div class="sect2" id="POSTGRES-FDW-CROSS-VERSION-COMPATIBILITY"><div class="titlepage"><div><div><h3 class="title">F.38.7. Cross-Version Compatibility <a href="#POSTGRES-FDW-CROSS-VERSION-COMPATIBILITY" class="id_link">#</a></h3></div></div></div><p>615   <code class="filename">postgres_fdw</code> can be used with remote servers dating back616   to <span class="productname">PostgreSQL</span> 8.3.  Read-only capability is available617   back to 8.1.  A limitation however is that <code class="filename">postgres_fdw</code>618   generally assumes that immutable built-in functions and operators are619   safe to send to the remote server for execution, if they appear in a620   <code class="literal">WHERE</code> clause for a foreign table.  Thus, a built-in621   function that was added since the remote server's release might be sent622   to it for execution, resulting in <span class="quote">“<span class="quote">function does not exist</span>”</span> or623   a similar error.  This type of failure can be worked around by624   rewriting the query, for example by embedding the foreign table625   reference in a sub-<code class="literal">SELECT</code> with <code class="literal">OFFSET 0</code> as an626   optimization fence, and placing the problematic function or operator627   outside the sub-<code class="literal">SELECT</code>.628  </p></div><div class="sect2" id="POSTGRES-FDW-CONFIGURATION-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">F.38.8. Configuration Parameters <a href="#POSTGRES-FDW-CONFIGURATION-PARAMETERS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-PGFDW-APPLICATION-NAME"><span class="term">629     <code class="varname">postgres_fdw.application_name</code> (<code class="type">string</code>)630     <a id="id-1.11.7.48.18.2.1.1.3" class="indexterm"></a>631    </span> <a href="#GUC-PGFDW-APPLICATION-NAME" class="id_link">#</a></dt><dd><p>632      Specifies a value for <a class="xref" href="runtime-config-logging.html#GUC-APPLICATION-NAME">application_name</a>633      configuration parameter used when <code class="filename">postgres_fdw</code>634      establishes a connection to a foreign server.  This overrides635      <code class="varname">application_name</code> option of the server object.636      Note that change of this parameter doesn't affect any existing637      connections until they are re-established.638     </p><p>639      <code class="varname">postgres_fdw.application_name</code> can be any string640      of any length and contain even non-ASCII characters.  However when641      it's passed to and used as <code class="varname">application_name</code>642      in a foreign server, note that it will be truncated to less than643      <code class="symbol">NAMEDATALEN</code> characters.644      Anything other than printable ASCII characters are replaced with <a class="link" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE" title="4.1.2.2. String Constants with C-Style Escapes">C-style hexadecimal escapes</a>.645      See <a class="xref" href="runtime-config-logging.html#GUC-APPLICATION-NAME">application_name</a> for details.646     </p><p>647      <code class="literal">%</code> characters begin <span class="quote">“<span class="quote">escape sequences</span>”</span>648      that are replaced with status information as outlined below.649      Unrecognized escapes are ignored. Other characters are copied straight650      to the application name. Note that it's not allowed to specify a651      plus/minus sign or a numeric literal after the <code class="literal">%</code>652      and before the option, for alignment and padding.653     </p><div class="informaltable"><table class="informaltable" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Escape</th><th>Effect</th></tr></thead><tbody><tr><td><code class="literal">%a</code></td><td>Application name on local server</td></tr><tr><td><code class="literal">%c</code></td><td>654          Session ID on local server655          (see <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> for details)656         </td></tr><tr><td><code class="literal">%C</code></td><td>657          Cluster name on local server658          (see <a class="xref" href="runtime-config-logging.html#GUC-CLUSTER-NAME">cluster_name</a> for details)659         </td></tr><tr><td><code class="literal">%u</code></td><td>User name on local server</td></tr><tr><td><code class="literal">%d</code></td><td>Database name on local server</td></tr><tr><td><code class="literal">%p</code></td><td>Process ID of backend on local server</td></tr><tr><td><code class="literal">%%</code></td><td>Literal %</td></tr></tbody></table></div><p>660      For example, suppose user <code class="literal">local_user</code> establishes661      a connection from database <code class="literal">local_db</code> to662      <code class="literal">foreign_db</code> as user <code class="literal">foreign_user</code>,663      the setting <code class="literal">'db=%d, user=%u'</code> is replaced with664      <code class="literal">'db=local_db, user=local_user'</code>.665     </p></dd></dl></div></div><div class="sect2" id="POSTGRES-FDW-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">F.38.9. Examples <a href="#POSTGRES-FDW-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>666   Here is an example of creating a foreign table with667   <code class="literal">postgres_fdw</code>. First install the extension:668  </p><pre class="programlisting">669CREATE EXTENSION postgres_fdw;670</pre><p>671   Then create a foreign server using <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>.672   In this example we wish to connect to a <span class="productname">PostgreSQL</span> server673   on host <code class="literal">192.83.123.89</code> listening on674   port <code class="literal">5432</code>.  The database to which the connection is made675   is named <code class="literal">foreign_db</code> on the remote server:676 677</p><pre class="programlisting">678CREATE SERVER foreign_server679        FOREIGN DATA WRAPPER postgres_fdw680        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');681</pre><p>682  </p><p>683   A user mapping, defined with <a class="xref" href="sql-createusermapping.html" title="CREATE USER MAPPING"><span class="refentrytitle">CREATE USER MAPPING</span></a>, is684   needed as well to identify the role that will be used on the remote685   server:686 687</p><pre class="programlisting">688CREATE USER MAPPING FOR local_user689        SERVER foreign_server690        OPTIONS (user 'foreign_user', password 'password');691</pre><p>692  </p><p>693   Now it is possible to create a foreign table with694   <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.  In this example we695   wish to access the table named <code class="structname">some_schema.some_table</code>696   on the remote server.  The local name for it will697   be <code class="structname">foreign_table</code>:698 699</p><pre class="programlisting">700CREATE FOREIGN TABLE foreign_table (701        id integer NOT NULL,702        data text703)704        SERVER foreign_server705        OPTIONS (schema_name 'some_schema', table_name 'some_table');706</pre><p>707 708   It's essential that the data types and other properties of the columns709   declared in <code class="command">CREATE FOREIGN TABLE</code> match the actual remote table.710   Column names must match as well, unless you attach <code class="literal">column_name</code>711   options to the individual columns to show how they are named in the remote712   table.713   In many cases, use of <a class="link" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><code class="command">IMPORT FOREIGN SCHEMA</code></a> is714   preferable to constructing foreign table definitions manually.715  </p></div><div class="sect2" id="POSTGRES-FDW-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.38.10. Author <a href="#POSTGRES-FDW-AUTHOR" class="id_link">#</a></h3></div></div></div><p>716   Shigeru Hanada <code class="email">&lt;<a class="email" href="mailto:shigeru.hanada@gmail.com">shigeru.hanada@gmail.com</a>&gt;</code>717  </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgwalinspect.html" title="F.37. pg_walinspect — low-level WAL inspection">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="seg.html" title="F.39. seg — a datatype for line segments or floating point intervals">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.37. pg_walinspect — low-level WAL inspection </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"> F.39. seg — a datatype for line segments or floating point intervals</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai