Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard


			
				
					
						
						
							
							<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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.33. postgres_fdw</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 V1.79.1" /><link rel="prev" href="pgvisibility.html" title="F.32. pg_visibility" /><link rel="next" href="seg.html" title="F.34. seg" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.33. postgres_fdw</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgvisibility.html" title="F.32. pg_visibility">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="seg.html" title="F.34. seg">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="POSTGRES-FDW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.33. postgres_fdw</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.10">F.33.1. FDW Options of postgres_fdw</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.11">F.33.2. Connection Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.12">F.33.3. Transaction Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.13">F.33.4. Remote Query Optimization</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.14">F.33.5. Remote Query Execution Environment</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.15">F.33.6. Cross-Version Compatibility</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.16">F.33.7. Examples</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.42.17">F.33.8. Author</a></span></dt></dl></div><a id="id-1.11.7.42.2" class="indexterm"></a><p>
  The <code class="filename">postgres_fdw</code> module provides the foreign-data wrapper
  <code class="literal">postgres_fdw</code>, which can be used to access data
  stored in external <span class="productname">PostgreSQL</span> servers.
 </p><p>
  The functionality provided by this module overlaps substantially
  with the functionality of the older <a class="xref" href="dblink.html" title="F.10. dblink">dblink</a> module.
  But <code class="filename">postgres_fdw</code> provides more transparent and
  standards-compliant syntax for accessing remote tables, and can give
  better performance in many cases.
 </p><p>
  To prepare for remote access using <code class="filename">postgres_fdw</code>:
  </p><div class="orderedlist"><ol class="orderedlist compact" type="1"><li class="listitem"><p>
     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>.
    </p></li><li class="listitem"><p>
     Create a foreign server object, using <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>,
     to represent each remote database you want to connect to.
     Specify connection information, except <code class="literal">user</code> and
     <code class="literal">password</code>, as options of the server object.
    </p></li><li class="listitem"><p>
     Create a user mapping, using <a class="xref" href="sql-createusermapping.html" title="CREATE USER MAPPING"><span class="refentrytitle">CREATE USER MAPPING</span></a>, for
     each database user you want to allow to access each foreign server.
     Specify the remote user name and password to use as
     <code class="literal">user</code> and <code class="literal">password</code> options of the
     user mapping.
    </p></li><li class="listitem"><p>
     Create a foreign table, using <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>
     or <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>,
     for each remote table you want to access.  The columns of the foreign
     table must match the referenced remote table.  You can, however, use
     table and/or column names different from the remote table's, if you
     specify the correct remote names as options of the foreign table object.
    </p></li></ol></div><p>
 </p><p>
  Now you need only <code class="command">SELECT</code> from a foreign table to access
  the data stored in its underlying remote table.  You can also modify
  the remote table using <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or
  <code class="command">DELETE</code>.  (Of course, the remote user you have specified
  in your user mapping must have privileges to do these things.)
 </p><p>
  Note that <code class="filename">postgres_fdw</code> currently lacks support for
  <code class="command">INSERT</code> statements with an <code class="literal">ON CONFLICT DO
  UPDATE</code> clause.  However, the <code class="literal">ON CONFLICT DO NOTHING</code>
  clause is supported, provided a unique index inference specification
  is omitted.
  Note also that <code class="filename">postgres_fdw</code> supports row movement
  invoked by <code class="command">UPDATE</code> statements executed on partitioned
  tables, but it currently does not handle the case where a remote partition
  chosen to insert a moved row into is also an <code class="command">UPDATE</code>
  target partition that will be updated later.
 </p><p>
  It is generally recommended that the columns of a foreign table be declared
  with exactly the same data types, and collations if applicable, as the
  referenced columns of the remote table.  Although <code class="filename">postgres_fdw</code>
  is currently rather forgiving about performing data type conversions at
  need, surprising semantic anomalies may arise when types or collations do
  not match, due to the remote server interpreting <code class="literal">WHERE</code> clauses
  slightly differently from the local server.
 </p><p>
  Note that a foreign table can be declared with fewer columns, or with a
  different column order, than its underlying remote table has.  Matching
  of columns to the remote table is by name, not position.
 </p><div class="sect2" id="id-1.11.7.42.10"><div class="titlepage"><div><div><h3 class="title">F.33.1. FDW Options of postgres_fdw</h3></div></div></div><div class="sect3" id="id-1.11.7.42.10.2"><div class="titlepage"><div><div><h4 class="title">F.33.1.1. Connection Options</h4></div></div></div><p>
    A foreign server using the <code class="filename">postgres_fdw</code> foreign data wrapper
    can have the same options that <span class="application">libpq</span> accepts in
    connection strings, as described in <a class="xref" href="libpq-connect.html#LIBPQ-PARAMKEYWORDS" title="33.1.2. Parameter Key Words">Section 33.1.2</a>,
    except that these options are not allowed:

    </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>
       <code class="literal">user</code> and <code class="literal">password</code> (specify these
       in a user mapping, instead)
      </p></li><li class="listitem"><p>
       <code class="literal">client_encoding</code> (this is automatically set from the local
       server encoding)
      </p></li><li class="listitem"><p>
       <code class="literal">fallback_application_name</code> (always set to
       <code class="literal">postgres_fdw</code>)
      </p></li></ul></div><p>
   </p><p>
    Only superusers may connect to foreign servers without password
    authentication, so always specify the <code class="literal">password</code> option
    for user mappings belonging to non-superusers.
   </p></div><div class="sect3" id="id-1.11.7.42.10.3"><div class="titlepage"><div><div><h4 class="title">F.33.1.2. Object Name Options</h4></div></div></div><p>
    These options can be used to control the names used in SQL statements
    sent to the remote <span class="productname">PostgreSQL</span> server.  These
    options are needed when a foreign table is created with names different
    from the underlying remote table's names.
   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">schema_name</code></span></dt><dd><p>
       This option, which can be specified for a foreign table, gives the
       schema name to use for the foreign table on the remote server.  If this
       option is omitted, the name of the foreign table's schema is used.
      </p></dd><dt><span class="term"><code class="literal">table_name</code></span></dt><dd><p>
       This option, which can be specified for a foreign table, gives the
       table name to use for the foreign table on the remote server.  If this
       option is omitted, the foreign table's name is used.
      </p></dd><dt><span class="term"><code class="literal">column_name</code></span></dt><dd><p>
       This option, which can be specified for a column of a foreign table,
       gives the column name to use for the column on the remote server.
       If this option is omitted, the column's name is used.
      </p></dd></dl></div></div><div class="sect3" id="id-1.11.7.42.10.4"><div class="titlepage"><div><div><h4 class="title">F.33.1.3. Cost Estimation Options</h4></div></div></div><p>
    <code class="filename">postgres_fdw</code> retrieves remote data by executing queries
    against remote servers, so ideally the estimated cost of scanning a
    foreign table should be whatever it costs to be done on the remote
    server, plus some overhead for communication.  The most reliable way to
    get such an estimate is to ask the remote server and then add something
    for overhead — but for simple queries, it may not be worth the cost
    of an additional remote query to get a cost estimate.
    So <code class="filename">postgres_fdw</code> provides the following options to control
    how cost estimation is done:
   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">use_remote_estimate</code></span></dt><dd><p>
       This option, which can be specified for a foreign table or a foreign
       server, controls whether <code class="filename">postgres_fdw</code> issues remote
       <code class="command">EXPLAIN</code> commands to obtain cost estimates.
       A setting for a foreign table overrides any setting for its server,
       but only for that table.
       The default is <code class="literal">false</code>.
      </p></dd><dt><span class="term"><code class="literal">fdw_startup_cost</code></span></dt><dd><p>
       This option, which can be specified for a foreign server, is a numeric
       value that is added to the estimated startup cost of any foreign-table
       scan on that server.  This represents the additional overhead of
       establishing a connection, parsing and planning the query on the
       remote side, etc.
       The default value is <code class="literal">100</code>.
      </p></dd><dt><span class="term"><code class="literal">fdw_tuple_cost</code></span></dt><dd><p>
       This option, which can be specified for a foreign server, is a numeric
       value that is used as extra cost per-tuple for foreign-table
       scans on that server.  This represents the additional overhead of
       data transfer between servers.  You might increase or decrease this
       number to reflect higher or lower network delay to the remote server.
       The default value is <code class="literal">0.01</code>.
      </p></dd></dl></div><p>
    When <code class="literal">use_remote_estimate</code> is true,
    <code class="filename">postgres_fdw</code> obtains row count and cost estimates from the
    remote server and then adds <code class="literal">fdw_startup_cost</code> and
    <code class="literal">fdw_tuple_cost</code> to the cost estimates.  When
    <code class="literal">use_remote_estimate</code> is false,
    <code class="filename">postgres_fdw</code> performs local row count and cost estimation
    and then adds <code class="literal">fdw_startup_cost</code> and
    <code class="literal">fdw_tuple_cost</code> to the cost estimates.  This local
    estimation is unlikely to be very accurate unless local copies of the
    remote table's statistics are available.  Running
    <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> on the foreign table is the way to update
    the local statistics; this will perform a scan of the remote table and
    then calculate and store statistics just as though the table were local.
    Keeping local statistics can be a useful way to reduce per-query planning
    overhead for a remote table — but if the remote table is
    frequently updated, the local statistics will soon be obsolete.
   </p></div><div class="sect3" id="id-1.11.7.42.10.5"><div class="titlepage"><div><div><h4 class="title">F.33.1.4. Remote Execution Options</h4></div></div></div><p>
    By default, only <code class="literal">WHERE</code> clauses using built-in operators and
    functions will be considered for execution on the remote server.  Clauses
    involving non-built-in functions are checked locally after rows are
    fetched.  If such functions are available on the remote server and can be
    relied on to produce the same results as they do locally, performance can
    be improved by sending such <code class="literal">WHERE</code> clauses for remote
    execution.  This behavior can be controlled using the following option:
   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">extensions</code></span></dt><dd><p>
       This option is a comma-separated list of names
       of <span class="productname">PostgreSQL</span> extensions that are installed, in
       compatible versions, on both the local and remote servers.  Functions
       and operators that are immutable and belong to a listed extension will
       be considered shippable to the remote server.
       This option can only be specified for foreign servers, not per-table.
      </p><p>
       When using the <code class="literal">extensions</code> option, <span class="emphasis"><em>it is the
       user's responsibility</em></span> that the listed extensions exist and behave
       identically on both the local and remote servers.  Otherwise, remote
       queries may fail or behave unexpectedly.
      </p></dd><dt><span class="term"><code class="literal">fetch_size</code></span></dt><dd><p>
       This option specifies the number of rows <code class="filename">postgres_fdw</code>
       should get in each fetch operation. It can be specified for a foreign
       table or a foreign server. The option specified on a table overrides
       an option specified for the server.
       The default is <code class="literal">100</code>.
      </p></dd></dl></div></div><div class="sect3" id="id-1.11.7.42.10.6"><div class="titlepage"><div><div><h4 class="title">F.33.1.5. Updatability Options</h4></div></div></div><p>
    By default all foreign tables using <code class="filename">postgres_fdw</code> are assumed
    to be updatable.  This may be overridden using the following option:
   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">updatable</code></span></dt><dd><p>
       This option controls whether <code class="filename">postgres_fdw</code> allows foreign
       tables to be modified using <code class="command">INSERT</code>, <code class="command">UPDATE</code> and
       <code class="command">DELETE</code> commands.  It can be specified for a foreign table
       or a foreign server.  A table-level option overrides a server-level
       option.
       The default is <code class="literal">true</code>.
      </p><p>
       Of course, if the remote table is not in fact updatable, an error
       would occur anyway.  Use of this option primarily allows the error to
       be thrown locally without querying the remote server.  Note however
       that the <code class="literal">information_schema</code> views will report a
       <code class="filename">postgres_fdw</code> foreign table to be updatable (or not)
       according to the setting of this option, without any check of the
       remote server.
      </p></dd></dl></div></div><div class="sect3" id="id-1.11.7.42.10.7"><div class="titlepage"><div><div><h4 class="title">F.33.1.6. Importing Options</h4></div></div></div><p>
    <code class="filename">postgres_fdw</code> is able to import foreign table definitions
    using <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>.  This command creates
    foreign table definitions on the local server that match tables or
    views present on the remote server.  If the remote tables to be imported
    have columns of user-defined data types, the local server must have
    compatible types of the same names.
   </p><p>
    Importing behavior can be customized with the following options
    (given in the <code class="command">IMPORT FOREIGN SCHEMA</code> command):
   </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">import_collate</code></span></dt><dd><p>
       This option controls whether column <code class="literal">COLLATE</code> options
       are included in the definitions of foreign tables imported
       from a foreign server. The default is <code class="literal">true</code>.  You might
       need to turn this off if the remote server has a different set of
       collation names than the local server does, which is likely to be the
       case if it's running on a different operating system.
      </p></dd><dt><span class="term"><code class="literal">import_default</code></span></dt><dd><p>
       This option controls whether column <code class="literal">DEFAULT</code> expressions
       are included in the definitions of foreign tables imported
       from a foreign server. The default is <code class="literal">false</code>.  If you
       enable this option, be wary of defaults that might get computed
       differently on the local server than they would be on the remote
       server; <code class="function">nextval()</code> is a common source of problems.
       The <code class="command">IMPORT</code> will fail altogether if an imported default
       expression uses a function or operator that does not exist locally.
      </p></dd><dt><span class="term"><code class="literal">import_not_null</code></span></dt><dd><p>
       This option controls whether column <code class="literal">NOT NULL</code>
       constraints are included in the definitions of foreign tables imported
       from a foreign server. The default is <code class="literal">true</code>.
      </p></dd></dl></div><p>
    Note that constraints other than <code class="literal">NOT NULL</code> will never be
    imported from the remote tables.  Although <span class="productname">PostgreSQL</span>
    does support <code class="literal">CHECK</code> constraints on foreign tables, there is no
    provision for importing them automatically, because of the risk that a
    constraint expression could evaluate differently on the local and remote
    servers.  Any such inconsistency in the behavior of a <code class="literal">CHECK</code>
    constraint could lead to hard-to-detect errors in query optimization.
    So if you wish to import <code class="literal">CHECK</code> constraints, you must do so
    manually, and you should verify the semantics of each one carefully.
    For more detail about the treatment of <code class="literal">CHECK</code> constraints on
    foreign tables, see <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.
   </p><p>
    Tables or foreign tables which are partitions of some other table are
    automatically excluded.  Partitioned tables are imported, unless they
    are a partition of some other table.  Since all data can be accessed
    through the partitioned table which is the root of the partitioning
    hierarchy, this approach should allow access to all the data without
    creating extra objects.
   </p></div></div><div class="sect2" id="id-1.11.7.42.11"><div class="titlepage"><div><div><h3 class="title">F.33.2. Connection Management</h3></div></div></div><p>
   <code class="filename">postgres_fdw</code> establishes a connection to a
   foreign server during the first query that uses a foreign table
   associated with the foreign server.  This connection is kept and
   re-used for subsequent queries in the same session.  However, if
   multiple user identities (user mappings) are used to access the foreign
   server, a connection is established for each user mapping.
  </p></div><div class="sect2" id="id-1.11.7.42.12"><div class="titlepage"><div><div><h3 class="title">F.33.3. Transaction Management</h3></div></div></div><p>
   During a query that references any remote tables on a foreign server,
   <code class="filename">postgres_fdw</code> opens a transaction on the
   remote server if one is not already open corresponding to the current
   local transaction.  The remote transaction is committed or aborted when
   the local transaction commits or aborts.  Savepoints are similarly
   managed by creating corresponding remote savepoints.
  </p><p>
   The remote transaction uses <code class="literal">SERIALIZABLE</code>
   isolation level when the local transaction has <code class="literal">SERIALIZABLE</code>
   isolation level; otherwise it uses <code class="literal">REPEATABLE READ</code>
   isolation level.  This choice ensures that if a query performs multiple
   table scans on the remote server, it will get snapshot-consistent results
   for all the scans.  A consequence is that successive queries within a
   single transaction will see the same data from the remote server, even if
   concurrent updates are occurring on the remote server due to other
   activities.  That behavior would be expected anyway if the local
   transaction uses <code class="literal">SERIALIZABLE</code> or <code class="literal">REPEATABLE READ</code>
   isolation level, but it might be surprising for a <code class="literal">READ
   COMMITTED</code> local transaction.  A future
   <span class="productname">PostgreSQL</span> release might modify these rules.
  </p><p>
   Note that it is currently not supported by
   <code class="filename">postgres_fdw</code> to prepare the remote transaction for
   two-phase commit.
  </p></div><div class="sect2" id="id-1.11.7.42.13"><div class="titlepage"><div><div><h3 class="title">F.33.4. Remote Query Optimization</h3></div></div></div><p>
   <code class="filename">postgres_fdw</code> attempts to optimize remote queries to reduce
   the amount of data transferred from foreign servers.  This is done by
   sending query <code class="literal">WHERE</code> clauses to the remote server for
   execution, and by not retrieving table columns that are not needed for
   the current query.  To reduce the risk of misexecution of queries,
   <code class="literal">WHERE</code> clauses are not sent to the remote server unless they use
   only data types, operators, and functions that are built-in or belong to an
   extension that's listed in the foreign server's <code class="literal">extensions</code>
   option.  Operators and functions in such clauses must
   be <code class="literal">IMMUTABLE</code> as well.
   For an <code class="command">UPDATE</code> or <code class="command">DELETE</code> query,
   <code class="filename">postgres_fdw</code> attempts to optimize the query execution by
   sending the whole query to the remote server if there are no query
   <code class="literal">WHERE</code> clauses that cannot be sent to the remote server,
   no local joins for the query, no row-level local <code class="literal">BEFORE</code> or
   <code class="literal">AFTER</code> triggers or stored generated columns on the target
   table, and no <code class="literal">CHECK OPTION</code> constraints from parent
   views.  In <code class="command">UPDATE</code>,
   expressions to assign to target columns must use only built-in data types,
   <code class="literal">IMMUTABLE</code> operators, or <code class="literal">IMMUTABLE</code> functions,
   to reduce the risk of misexecution of the query.
  </p><p>
   When <code class="filename">postgres_fdw</code> encounters a join between foreign tables on
   the same foreign server, it sends the entire join to the foreign server,
   unless for some reason it believes that it will be more efficient to fetch
   rows from each table individually, or unless the table references involved
   are subject to different user mappings.  While sending the <code class="literal">JOIN</code>
   clauses, it takes the same precautions as mentioned above for the
   <code class="literal">WHERE</code> clauses.
  </p><p>
   The query that is actually sent to the remote server for execution can
   be examined using <code class="command">EXPLAIN VERBOSE</code>.
  </p></div><div class="sect2" id="id-1.11.7.42.14"><div class="titlepage"><div><div><h3 class="title">F.33.5. Remote Query Execution Environment</h3></div></div></div><p>
   In the remote sessions opened by <code class="filename">postgres_fdw</code>,
   the <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> parameter is set to
   just <code class="literal">pg_catalog</code>, so that only built-in objects are visible
   without schema qualification.  This is not an issue for queries
   generated by <code class="filename">postgres_fdw</code> itself, because it always
   supplies such qualification.  However, this can pose a hazard for
   functions that are executed on the remote server via triggers or rules
   on remote tables.  For example, if a remote table is actually a view,
   any functions used in that view will be executed with the restricted
   search path.  It is recommended to schema-qualify all names in such
   functions, or else attach <code class="literal">SET search_path</code> options
   (see <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>) to such functions
   to establish their expected search path environment.
  </p><p>
   <code class="filename">postgres_fdw</code> likewise establishes remote session settings
   for various parameters:
   </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>
      <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> is set to <code class="literal">UTC</code>
     </p></li><li class="listitem"><p>
      <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> is set to <code class="literal">ISO</code>
     </p></li><li class="listitem"><p>
      <a class="xref" href="runtime-config-client.html#GUC-INTERVALSTYLE">IntervalStyle</a> is set to <code class="literal">postgres</code>
     </p></li><li class="listitem"><p>
      <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 remote
      servers 9.0 and newer and is set to <code class="literal">2</code> for older versions
     </p></li></ul></div><p>
   These are less likely to be problematic than <code class="varname">search_path</code>, but
   can be handled with function <code class="literal">SET</code> options if the need arises.
  </p><p>
   It is <span class="emphasis"><em>not</em></span> recommended that you override this behavior by
   changing the session-level settings of these parameters; that is likely
   to cause <code class="filename">postgres_fdw</code> to malfunction.
  </p></div><div class="sect2" id="id-1.11.7.42.15"><div class="titlepage"><div><div><h3 class="title">F.33.6. Cross-Version Compatibility</h3></div></div></div><p>
   <code class="filename">postgres_fdw</code> can be used with remote servers dating back
   to <span class="productname">PostgreSQL</span> 8.3.  Read-only capability is available
   back to 8.1.  A limitation however is that <code class="filename">postgres_fdw</code>
   generally assumes that immutable built-in functions and operators are
   safe to send to the remote server for execution, if they appear in a
   <code class="literal">WHERE</code> clause for a foreign table.  Thus, a built-in
   function that was added since the remote server's release might be sent
   to it for execution, resulting in <span class="quote">“<span class="quote">function does not exist</span>”</span> or
   a similar error.  This type of failure can be worked around by
   rewriting the query, for example by embedding the foreign table
   reference in a sub-<code class="literal">SELECT</code> with <code class="literal">OFFSET 0</code> as an
   optimization fence, and placing the problematic function or operator
   outside the sub-<code class="literal">SELECT</code>.
  </p></div><div class="sect2" id="id-1.11.7.42.16"><div class="titlepage"><div><div><h3 class="title">F.33.7. Examples</h3></div></div></div><p>
   Here is an example of creating a foreign table with
   <code class="literal">postgres_fdw</code>. First install the extension:
  </p><pre class="programlisting">
CREATE EXTENSION postgres_fdw;
</pre><p>
   Then create a foreign server using <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>.
   In this example we wish to connect to a <span class="productname">PostgreSQL</span> server
   on host <code class="literal">192.83.123.89</code> listening on
   port <code class="literal">5432</code>.  The database to which the connection is made
   is named <code class="literal">foreign_db</code> on the remote server:

</p><pre class="programlisting">
CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
</pre><p>
  </p><p>
   A user mapping, defined with <a class="xref" href="sql-createusermapping.html" title="CREATE USER MAPPING"><span class="refentrytitle">CREATE USER MAPPING</span></a>, is
   needed as well to identify the role that will be used on the remote
   server:

</p><pre class="programlisting">
CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');
</pre><p>
  </p><p>
   Now it is possible to create a foreign table with
   <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.  In this example we
   wish to access the table named <code class="structname">some_schema.some_table</code>
   on the remote server.  The local name for it will
   be <code class="structname">foreign_table</code>:

</p><pre class="programlisting">
CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');
</pre><p>

   It's essential that the data types and other properties of the columns
   declared in <code class="command">CREATE FOREIGN TABLE</code> match the actual remote table.
   Column names must match as well, unless you attach <code class="literal">column_name</code>
   options to the individual columns to show how they are named in the remote
   table.
   In many cases, use of <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a> is
   preferable to constructing foreign table definitions manually.
  </p></div><div class="sect2" id="id-1.11.7.42.17"><div class="titlepage"><div><div><h3 class="title">F.33.8. Author</h3></div></div></div><p>
   Shigeru Hanada <code class="email">&lt;<a class="email" href="mailto:shigeru.hanada@gmail.com">shigeru.hanada@gmail.com</a>&gt;</code>
  </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgvisibility.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="seg.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.32. pg_visibility </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> F.34. seg</td></tr></table></div></body></html>