diff options
Diffstat (limited to 'doc/src/sgml/html/postgres-fdw.html')
| -rw-r--r-- | doc/src/sgml/html/postgres-fdw.html | 600 |
1 files changed, 600 insertions, 0 deletions
diff --git a/doc/src/sgml/html/postgres-fdw.html b/doc/src/sgml/html/postgres-fdw.html new file mode 100644 index 0000000..7b1b960 --- /dev/null +++ b/doc/src/sgml/html/postgres-fdw.html @@ -0,0 +1,600 @@ +<?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.35. 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 Vsnapshot" /><link rel="prev" href="pgvisibility.html" title="F.34. pg_visibility" /><link rel="next" href="seg.html" title="F.36. seg" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.35. postgres_fdw</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgvisibility.html" title="F.34. 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 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="seg.html" title="F.36. 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.35. 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.44.11">F.35.1. FDW Options of postgres_fdw</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.12">F.35.2. Functions</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.13">F.35.3. Connection Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.14">F.35.4. Transaction Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.15">F.35.5. Remote Query Optimization</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.16">F.35.6. Remote Query Execution Environment</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.17">F.35.7. Cross-Version Compatibility</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.18">F.35.8. Examples</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.44.19">F.35.9. Author</a></span></dt></dl></div><a id="id-1.11.7.44.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>, + <code class="command">DELETE</code>, <code class="command">COPY</code>, or + <code class="command">TRUNCATE</code>. + (Of course, the remote user you have specified in your user mapping must + have privileges to do these things.) + </p><p> + Note that the <code class="literal">ONLY</code> option specified in + <code class="command">SELECT</code>, <code class="command">UPDATE</code>, + <code class="command">DELETE</code> or <code class="command">TRUNCATE</code> + has no effect when accessing or modifying the remote table. + </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 elsewhere in the same command. + </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 query conditions + 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.44.11"><div class="titlepage"><div><div><h3 class="title">F.35.1. FDW Options of postgres_fdw</h3></div></div></div><div class="sect3" id="id-1.11.7.44.11.2"><div class="titlepage"><div><div><h4 class="title">F.35.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="34.1.2. Parameter Key Words">Section 34.1.2</a>, + except that these options are not allowed or have special handling: + + </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p> + <code class="literal">user</code>, <code class="literal">password</code> and <code class="literal">sslpassword</code> (specify these + in a user mapping, instead, or use a service file) + </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><li class="listitem"><p> + <code class="literal">sslkey</code> and <code class="literal">sslcert</code> - these may + appear in <span class="emphasis"><em>either or both</em></span> a connection and a user + mapping. If both are present, the user mapping setting overrides the + connection setting. + </p></li></ul></div><p> + </p><p> + Only superusers may create or modify user mappings with the + <code class="literal">sslcert</code> or <code class="literal">sslkey</code> settings. + </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><p> + A superuser may override this check on a per-user-mapping basis by setting + the user mapping option <code class="literal">password_required 'false'</code>, e.g., +</p><pre class="programlisting"> +ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw +OPTIONS (ADD password_required 'false'); +</pre><p> + To prevent unprivileged users from exploiting the authentication rights + of the unix user the postgres server is running as to escalate to superuser + rights, only the superuser may set this option on a user mapping. + </p><p> + Care is required to ensure that this does not allow the mapped + user the ability to connect as superuser to the mapped database per + CVE-2007-3278 and CVE-2007-6601. Don't set + <code class="literal">password_required=false</code> + on the <code class="literal">public</code> role. Keep in mind that the mapped + user can potentially use any client certificates, + <code class="filename">.pgpass</code>, + <code class="filename">.pg_service.conf</code> etc in the unix home directory of the + system user the postgres server runs as. They can also use any trust + relationship granted by authentication modes like <code class="literal">peer</code> + or <code class="literal">ident</code> authentication. + </p></div><div class="sect3" id="id-1.11.7.44.11.3"><div class="titlepage"><div><div><h4 class="title">F.35.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.44.11.4"><div class="titlepage"><div><div><h4 class="title">F.35.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 floating + point 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 floating + point 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.44.11.5"><div class="titlepage"><div><div><h4 class="title">F.35.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><dt><span class="term"><code class="literal">batch_size</code></span></dt><dd><p> + This option specifies the number of rows <code class="filename">postgres_fdw</code> + should insert in each insert 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">1</code>. + </p><p> + Note the actual number of rows <code class="filename">postgres_fdw</code> inserts at + once depends on the number of columns and the provided + <code class="literal">batch_size</code> value. The batch is executed as a single + query, and the libpq protocol (which <code class="filename">postgres_fdw</code> + uses to connect to a remote server) limits the number of parameters in a + single query to 65535. When the number of columns * <code class="literal">batch_size</code> + exceeds the limit, the <code class="literal">batch_size</code> will be adjusted to + avoid an error. + </p></dd></dl></div></div><div class="sect3" id="id-1.11.7.44.11.6"><div class="titlepage"><div><div><h4 class="title">F.35.1.5. Asynchronous Execution Options</h4></div></div></div><p> + <code class="filename">postgres_fdw</code> supports asynchronous execution, which + runs multiple parts of an <code class="structname">Append</code> node + concurrently rather than serially to improve performance. + This execution can be controlled using the following option: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">async_capable</code></span></dt><dd><p> + This option controls whether <code class="filename">postgres_fdw</code> allows + foreign tables to be scanned concurrently for asynchronous execution. + 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">false</code>. + </p><p> + In order to ensure that the data being returned from a foreign server + is consistent, <code class="filename">postgres_fdw</code> will only open one + connection for a given foreign server and will run all queries against + that server sequentially even if there are multiple foreign tables + involved, unless those tables are subject to different user mappings. + In such a case, it may be more performant to disable this option to + eliminate the overhead associated with running queries asynchronously. + </p><p> + Asynchronous execution is applied even when an + <code class="structname">Append</code> node contains subplan(s) executed + synchronously as well as subplan(s) executed asynchronously. + In such a case, if the asynchronous subplans are ones processed using + <code class="filename">postgres_fdw</code>, tuples from the asynchronous + subplans are not returned until after at least one synchronous subplan + returns all tuples, as that subplan is executed while the asynchronous + subplans are waiting for the results of asynchronous queries sent to + foreign servers. + This behavior might change in a future release. + </p></dd></dl></div></div><div class="sect3" id="id-1.11.7.44.11.7"><div class="titlepage"><div><div><h4 class="title">F.35.1.6. 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.44.11.8"><div class="titlepage"><div><div><h4 class="title">F.35.1.7. Truncatability Options</h4></div></div></div><p> + By default all foreign tables using <code class="filename">postgres_fdw</code> are assumed + to be truncatable. This may be overridden using the following option: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">truncatable</code></span></dt><dd><p> + This option controls whether <code class="filename">postgres_fdw</code> allows + foreign tables to be truncated using the <code class="command">TRUNCATE</code> + command. 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 truncatable, an error + would occur anyway. Use of this option primarily allows the error to + be thrown locally without querying the remote server. + </p></dd></dl></div></div><div class="sect3" id="id-1.11.7.44.11.9"><div class="titlepage"><div><div><h4 class="title">F.35.1.8. 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. + If you do so, however, there is a very severe risk that the imported + table columns' collations will not match the underlying data, resulting + in anomalous query behavior. + </p><p> + Even when this parameter is set to <code class="literal">true</code>, importing + columns whose collation is the remote server's default can be risky. + They will be imported with <code class="literal">COLLATE "default"</code>, which + will select the local server's default collation, which could be + different. + </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_generated</code></span></dt><dd><p> + This option controls whether column <code class="literal">GENERATED</code> expressions + are included in the definitions of foreign tables imported + from a foreign server. The default is <code class="literal">true</code>. + The <code class="command">IMPORT</code> will fail altogether if an imported generated + 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 check 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 check + constraint could lead to hard-to-detect errors in query optimization. + So if you wish to import check constraints, you must do so + manually, and you should verify the semantics of each one carefully. + For more detail about the treatment of check 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 + imported only when they are explicitly specified in + <code class="literal">LIMIT TO</code> clause. Otherwise they are automatically + excluded from <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>. + Since all data can be accessed through the partitioned table + which is the root of the partitioning hierarchy, importing only + partitioned tables should allow access to all the data without + creating extra objects. + </p></div><div class="sect3" id="id-1.11.7.44.11.10"><div class="titlepage"><div><div><h4 class="title">F.35.1.9. Connection Management Options</h4></div></div></div><p> + By default, all connections that <code class="filename">postgres_fdw</code> + establishes to foreign servers are kept open in the local session + for re-use. + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">keep_connections</code></span></dt><dd><p> + This option controls whether <code class="filename">postgres_fdw</code> keeps + the connections to the foreign server open so that subsequent + queries can re-use them. It can only be specified for a foreign server. + The default is <code class="literal">on</code>. If set to <code class="literal">off</code>, + all connections to this foreign server will be discarded at the end of + each transaction. + </p></dd></dl></div></div></div><div class="sect2" id="id-1.11.7.44.12"><div class="titlepage"><div><div><h3 class="title">F.35.2. Functions</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> + This function returns the foreign server names of all the open + connections that <code class="filename">postgres_fdw</code> established from + the local session to the foreign servers. It also returns whether + each connection is valid or not. <code class="literal">false</code> is returned + if the foreign server connection is used in the current local + transaction but its foreign server or user mapping is changed or + dropped (Note that server name of an invalid connection will be + <code class="literal">NULL</code> if the server is dropped), + and then such invalid connection will be closed at + the end of that transaction. <code class="literal">true</code> is returned + otherwise. If there are no open connections, no record is returned. + Example usage of the function: +</p><pre class="screen"> +postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; + server_name | valid +-------------+------- + loopback1 | t + loopback2 | f +</pre><p> + </p></dd><dt><span class="term"><code class="function">postgres_fdw_disconnect(server_name text) returns boolean</code></span></dt><dd><p> + This function discards the open connections that are established by + <code class="filename">postgres_fdw</code> from the local session to + the foreign server with the given name. Note that there can be + multiple connections to the given server using different user mappings. + If the connections are used in the current local transaction, + they are not disconnected and warning messages are reported. + This function returns <code class="literal">true</code> if it disconnects + at least one connection, otherwise <code class="literal">false</code>. + If no foreign server with the given name is found, an error is reported. + Example usage of the function: +</p><pre class="screen"> +postgres=# SELECT postgres_fdw_disconnect('loopback1'); + postgres_fdw_disconnect +------------------------- + t +</pre><p> + </p></dd><dt><span class="term"><code class="function">postgres_fdw_disconnect_all() returns boolean</code></span></dt><dd><p> + This function discards all the open connections that are established by + <code class="filename">postgres_fdw</code> from the local session to + foreign servers. If the connections are used in the current local + transaction, they are not disconnected and warning messages are reported. + This function returns <code class="literal">true</code> if it disconnects + at least one connection, otherwise <code class="literal">false</code>. + Example usage of the function: +</p><pre class="screen"> +postgres=# SELECT postgres_fdw_disconnect_all(); + postgres_fdw_disconnect_all +----------------------------- + t +</pre><p> + </p></dd></dl></div></div><div class="sect2" id="id-1.11.7.44.13"><div class="titlepage"><div><div><h3 class="title">F.35.3. 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. By default this connection + is kept and re-used for subsequent queries in the same session. + This behavior can be controlled using + <code class="literal">keep_connections</code> option for a foreign server. If + multiple user identities (user mappings) are used to access the foreign + server, a connection is established for each user mapping. + </p><p> + When changing the definition of or removing a foreign server or + a user mapping, the associated connections are closed. + But note that if any connections are in use in the current local transaction, + they are kept until the end of the transaction. + Closed connections will be re-established when they are necessary + by future queries using a foreign table. + </p><p> + Once a connection to a foreign server has been established, + it's by default kept until the local or corresponding remote + session exits. To disconnect a connection explicitly, + <code class="literal">keep_connections</code> option for a foreign server + may be disabled, or + <code class="function">postgres_fdw_disconnect</code> and + <code class="function">postgres_fdw_disconnect_all</code> functions + may be used. For example, these are useful to close + connections that are no longer necessary, thereby releasing + connections on the foreign server. + </p></div><div class="sect2" id="id-1.11.7.44.14"><div class="titlepage"><div><div><h3 class="title">F.35.4. 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.44.15"><div class="titlepage"><div><div><h3 class="title">F.35.5. 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.44.16"><div class="titlepage"><div><div><h3 class="title">F.35.6. 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.44.17"><div class="titlepage"><div><div><h3 class="title">F.35.7. 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.44.18"><div class="titlepage"><div><div><h3 class="title">F.35.8. 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="link" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><code class="command">IMPORT FOREIGN SCHEMA</code></a> is + preferable to constructing foreign table definitions manually. + </p></div><div class="sect2" id="id-1.11.7.44.19"><div class="titlepage"><div><div><h3 class="title">F.35.9. Author</h3></div></div></div><p> + Shigeru Hanada <code class="email"><<a class="email" href="mailto:shigeru.hanada@gmail.com">shigeru.hanada@gmail.com</a>></code> + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgvisibility.html" title="F.34. pg_visibility">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="seg.html" title="F.36. seg">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.34. pg_visibility </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.36. seg</td></tr></table></div></body></html>
\ No newline at end of file |