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 18.0 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-WAIT-EVENTS">F.38.8. Wait Events</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-CONFIGURATION-PARAMETERS">F.38.9. Configuration Parameters</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-EXAMPLES">F.38.10. Examples</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#POSTGRES-FDW-AUTHOR">F.38.11. 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 wrapper
8 <code class="literal">postgres_fdw</code>, which can be used to access data
9 stored in external <span class="productname">PostgreSQL</span> servers.
11 The functionality provided by this module overlaps substantially
12 with the functionality of the older <a class="xref" href="dblink.html" title="F.11. dblink — connect to other PostgreSQL databases">dblink</a> module.
13 But <code class="filename">postgres_fdw</code> provides more transparent and
14 standards-compliant syntax for accessing remote tables, and can give
15 better performance in many cases.
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> and
24 <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>, for
27 each database user you want to allow to access each foreign server.
28 Specify the remote user name and password to use as
29 <code class="literal">user</code> and <code class="literal">password</code> options of the
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 foreign
35 table must match the referenced remote table. You can, however, use
36 table and/or column names different from the remote table's, if you
37 specify the correct remote names as options of the foreign table object.
38 </p></li></ol></div><p>
40 Now you need only <code class="command">SELECT</code> from a foreign table to access
41 the data stored in its underlying remote table. You can also modify
42 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>, or
44 <code class="command">TRUNCATE</code>.
45 (Of course, the remote user you have specified in your user mapping must
46 have privileges to do these things.)
48 Note that the <code class="literal">ONLY</code> option specified in
49 <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.
53 Note that <code class="filename">postgres_fdw</code> currently lacks support for
54 <code class="command">INSERT</code> statements with an <code class="literal">ON CONFLICT DO
55 UPDATE</code> clause. However, the <code class="literal">ON CONFLICT DO NOTHING</code>
56 clause is supported, provided a unique index inference specification
58 Note also that <code class="filename">postgres_fdw</code> supports row movement
59 invoked by <code class="command">UPDATE</code> statements executed on partitioned
60 tables, but it currently does not handle the case where a remote partition
61 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.
64 It is generally recommended that the columns of a foreign table be declared
65 with exactly the same data types, and collations if applicable, as the
66 referenced columns of the remote table. Although <code class="filename">postgres_fdw</code>
67 is currently rather forgiving about performing data type conversions at
68 need, surprising semantic anomalies may arise when types or collations do
69 not match, due to the remote server interpreting query conditions
70 differently from the local server.
72 Note that a foreign table can be declared with fewer columns, or with a
73 different column order, than its underlying remote table has. Matching
74 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 wrapper
77 can have the same options that <span class="application">libpq</span> accepts in
78 connection strings, as described in <a class="xref" href="libpq-connect.html#LIBPQ-PARAMKEYWORDS" title="32.1.2. Parameter Key Words">Section 32.1.2</a>,
79 except that these options are not allowed or have special handling:
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 these
83 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 local
87 </p></li><li class="listitem"><p>
88 <code class="literal">application_name</code> - this may appear in
89 <span class="emphasis"><em>either or both</em></span> a connection and
90 <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> allows
95 <code class="varname">application_name</code> to include
96 <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 to
100 <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 may
103 appear in <span class="emphasis"><em>either or both</em></span> a connection and a user
104 mapping. If both are present, the user mapping setting overrides the
106 </p></li></ul></div><p>
108 Only superusers may create or modify user mappings with the
109 <code class="literal">sslcert</code> or <code class="literal">sslkey</code> settings.
111 Non-superusers may connect to foreign servers using password
112 authentication or with GSSAPI delegated credentials, so specify the
113 <code class="literal">password</code> option for user mappings belonging to
114 non-superusers where password authentication is required.
116 A superuser may override this check on a per-user-mapping basis by setting
117 the user mapping option <code class="literal">password_required 'false'</code>, e.g.,
118 </p><pre class="programlisting">
119 ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
120 OPTIONS (ADD password_required 'false');
122 To prevent unprivileged users from exploiting the authentication rights
123 of the unix user the postgres server is running as to escalate to superuser
124 rights, only the superuser may set this option on a user mapping.
126 Care is required to ensure that this does not allow the mapped
127 user the ability to connect as superuser to the mapped database per
128 CVE-2007-3278 and CVE-2007-6601. Don't set
129 <code class="literal">password_required=false</code>
130 on the <code class="literal">public</code> role. Keep in mind that the mapped
131 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 the
134 system user the postgres server runs as. (For details on how home
135 directories are found, see <a class="xref" href="libpq-pgpass.html" title="32.16. The Password File">Section 32.16</a>.) They can
137 relationship granted by authentication modes like <code class="literal">peer</code>
138 or <code class="literal">ident</code> authentication.
139 </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>
140 These options can be used to control the names used in SQL statements
141 sent to the remote <span class="productname">PostgreSQL</span> server. These
142 options are needed when a foreign table is created with names different
143 from the underlying remote table's names.
144 </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>
145 This option, which can be specified for a foreign table, gives the
146 schema name to use for the foreign table on the remote server. If this
147 option is omitted, the name of the foreign table's schema is used.
148 </p></dd><dt><span class="term"><code class="literal">table_name</code> (<code class="type">string</code>)</span></dt><dd><p>
149 This option, which can be specified for a foreign table, gives the
150 table name to use for the foreign table on the remote server. If this
151 option is omitted, the foreign table's name is used.
152 </p></dd><dt><span class="term"><code class="literal">column_name</code> (<code class="type">string</code>)</span></dt><dd><p>
153 This option, which can be specified for a column of a foreign table,
154 gives the column name to use for the column on the remote server.
155 If this option is omitted, the column's name is used.
156 </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>
157 <code class="filename">postgres_fdw</code> retrieves remote data by executing queries
158 against remote servers, so ideally the estimated cost of scanning a
159 foreign table should be whatever it costs to be done on the remote
160 server, plus some overhead for communication. The most reliable way to
161 get such an estimate is to ask the remote server and then add something
162 for overhead — but for simple queries, it may not be worth the cost
163 of an additional remote query to get a cost estimate.
164 So <code class="filename">postgres_fdw</code> provides the following options to control
165 how cost estimation is done:
166 </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>
167 This option, which can be specified for a foreign table or a foreign
168 server, controls whether <code class="filename">postgres_fdw</code> issues remote
169 <code class="command">EXPLAIN</code> commands to obtain cost estimates.
170 A setting for a foreign table overrides any setting for its server,
171 but only for that table.
172 The default is <code class="literal">false</code>.
173 </p></dd><dt><span class="term"><code class="literal">fdw_startup_cost</code> (<code class="type">floating point</code>)</span></dt><dd><p>
174 This option, which can be specified for a foreign server, is a floating
175 point value that is added to the estimated startup cost of any
176 foreign-table scan on that server. This represents the additional
177 overhead of establishing a connection, parsing and planning the query on
178 the remote side, etc.
179 The default value is <code class="literal">100</code>.
180 </p></dd><dt><span class="term"><code class="literal">fdw_tuple_cost</code> (<code class="type">floating point</code>)</span></dt><dd><p>
181 This option, which can be specified for a foreign server, is a floating
182 point value that is used as extra cost per-tuple for foreign-table
183 scans on that server. This represents the additional overhead of
184 data transfer between servers. You might increase or decrease this
185 number to reflect higher or lower network delay to the remote server.
186 The default value is <code class="literal">0.2</code>.
187 </p></dd></dl></div><p>
188 When <code class="literal">use_remote_estimate</code> is true,
189 <code class="filename">postgres_fdw</code> obtains row count and cost estimates from the
190 remote server and then adds <code class="literal">fdw_startup_cost</code> and
191 <code class="literal">fdw_tuple_cost</code> to the cost estimates. When
192 <code class="literal">use_remote_estimate</code> is false,
193 <code class="filename">postgres_fdw</code> performs local row count and cost estimation
194 and then adds <code class="literal">fdw_startup_cost</code> and
195 <code class="literal">fdw_tuple_cost</code> to the cost estimates. This local
196 estimation is unlikely to be very accurate unless local copies of the
197 remote table's statistics are available. Running
198 <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> on the foreign table is the way to update
199 the local statistics; this will perform a scan of the remote table and
200 then calculate and store statistics just as though the table were local.
201 Keeping local statistics can be a useful way to reduce per-query planning
202 overhead for a remote table — but if the remote table is
203 frequently updated, the local statistics will soon be obsolete.
205 The following option controls how such an <code class="command">ANALYZE</code>
207 </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>
208 This option, which can be specified for a foreign table or a foreign
209 server, determines if <code class="command">ANALYZE</code> on a foreign table
210 samples the data on the remote side, or reads and transfers all data
211 and performs the sampling locally. The supported values
212 are <code class="literal">off</code>, <code class="literal">random</code>,
213 <code class="literal">system</code>, <code class="literal">bernoulli</code>
214 and <code class="literal">auto</code>. <code class="literal">off</code> disables remote
215 sampling, so all data are transferred and sampled locally.
216 <code class="literal">random</code> performs remote sampling using the
217 <code class="literal">random()</code> function to choose returned rows,
218 while <code class="literal">system</code> and <code class="literal">bernoulli</code> rely
219 on the built-in <code class="literal">TABLESAMPLE</code> methods of those
220 names. <code class="literal">random</code> works on all remote server versions,
221 while <code class="literal">TABLESAMPLE</code> is supported only since 9.5.
222 <code class="literal">auto</code> (the default) picks the recommended sampling
223 method automatically; currently it means
224 either <code class="literal">bernoulli</code> or <code class="literal">random</code>
225 depending on the remote server version.
226 </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>
227 By default, only <code class="literal">WHERE</code> clauses using built-in operators and
228 functions will be considered for execution on the remote server. Clauses
229 involving non-built-in functions are checked locally after rows are
230 fetched. If such functions are available on the remote server and can be
231 relied on to produce the same results as they do locally, performance can
232 be improved by sending such <code class="literal">WHERE</code> clauses for remote
233 execution. This behavior can be controlled using the following option:
234 </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>
235 This option is a comma-separated list of names
236 of <span class="productname">PostgreSQL</span> extensions that are installed, in
237 compatible versions, on both the local and remote servers. Functions
238 and operators that are immutable and belong to a listed extension will
239 be considered shippable to the remote server.
240 This option can only be specified for foreign servers, not per-table.
242 When using the <code class="literal">extensions</code> option, <span class="emphasis"><em>it is the
243 user's responsibility</em></span> that the listed extensions exist and behave
244 identically on both the local and remote servers. Otherwise, remote
245 queries may fail or behave unexpectedly.
246 </p></dd><dt><span class="term"><code class="literal">fetch_size</code> (<code class="type">integer</code>)</span></dt><dd><p>
247 This option specifies the number of rows <code class="filename">postgres_fdw</code>
248 should get in each fetch operation. It can be specified for a foreign
249 table or a foreign server. The option specified on a table overrides
250 an option specified for the server.
251 The default is <code class="literal">100</code>.
252 </p></dd><dt><span class="term"><code class="literal">batch_size</code> (<code class="type">integer</code>)</span></dt><dd><p>
253 This option specifies the number of rows <code class="filename">postgres_fdw</code>
254 should insert in each insert operation. It can be specified for a
255 foreign table or a foreign server. The option specified on a table
256 overrides an option specified for the server.
257 The default is <code class="literal">1</code>.
259 Note the actual number of rows <code class="filename">postgres_fdw</code> inserts at
260 once depends on the number of columns and the provided
261 <code class="literal">batch_size</code> value. The batch is executed as a single
262 query, and the libpq protocol (which <code class="filename">postgres_fdw</code>
263 uses to connect to a remote server) limits the number of parameters in a
264 single query to 65535. When the number of columns * <code class="literal">batch_size</code>
265 exceeds the limit, the <code class="literal">batch_size</code> will be adjusted to
268 This option also applies when copying into foreign tables. In that case
269 the actual number of rows <code class="filename">postgres_fdw</code> copies at
270 once is determined in a similar way to the insert case, but it is
271 limited to at most 1000 due to implementation restrictions of the
272 <code class="command">COPY</code> command.
273 </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>
274 <code class="filename">postgres_fdw</code> supports asynchronous execution, which
275 runs multiple parts of an <code class="structname">Append</code> node
276 concurrently rather than serially to improve performance.
277 This execution can be controlled using the following option:
278 </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>
279 This option controls whether <code class="filename">postgres_fdw</code> allows
280 foreign tables to be scanned concurrently for asynchronous execution.
281 It can be specified for a foreign table or a foreign server.
282 A table-level option overrides a server-level option.
283 The default is <code class="literal">false</code>.
285 In order to ensure that the data being returned from a foreign server
286 is consistent, <code class="filename">postgres_fdw</code> will only open one
287 connection for a given foreign server and will run all queries against
288 that server sequentially even if there are multiple foreign tables
289 involved, unless those tables are subject to different user mappings.
290 In such a case, it may be more performant to disable this option to
291 eliminate the overhead associated with running queries asynchronously.
293 Asynchronous execution is applied even when an
294 <code class="structname">Append</code> node contains subplan(s) executed
295 synchronously as well as subplan(s) executed asynchronously.
296 In such a case, if the asynchronous subplans are ones processed using
297 <code class="filename">postgres_fdw</code>, tuples from the asynchronous
298 subplans are not returned until after at least one synchronous subplan
299 returns all tuples, as that subplan is executed while the asynchronous
300 subplans are waiting for the results of asynchronous queries sent to
302 This behavior might change in a future release.
303 </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>
304 As described in the Transaction Management section, in
305 <code class="filename">postgres_fdw</code> transactions are managed by creating
306 corresponding remote transactions, and subtransactions are managed by
307 creating corresponding remote subtransactions. When multiple remote
308 transactions are involved in the current local transaction, by default
309 <code class="filename">postgres_fdw</code> commits or aborts those remote
310 transactions serially when the local transaction is committed or aborted.
311 When multiple remote subtransactions are involved in the current local
312 subtransaction, by default <code class="filename">postgres_fdw</code> commits or
313 aborts those remote subtransactions serially when the local subtransaction
314 is committed or aborted.
315 Performance can be improved with the following options:
316 </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>
317 This option controls whether <code class="filename">postgres_fdw</code> commits,
318 in parallel, remote transactions opened on a foreign server in a local
319 transaction when the local transaction is committed. This setting also
320 applies to remote and local subtransactions. This option can only be
321 specified for foreign servers, not per-table. The default is
322 <code class="literal">false</code>.
323 </p></dd><dt><span class="term"><code class="literal">parallel_abort</code> (<code class="type">boolean</code>)</span></dt><dd><p>
324 This option controls whether <code class="filename">postgres_fdw</code> aborts,
325 in parallel, remote transactions opened on a foreign server in a local
326 transaction when the local transaction is aborted. This setting also
327 applies to remote and local subtransactions. This option can only be
328 specified for foreign servers, not per-table. The default is
329 <code class="literal">false</code>.
330 </p></dd></dl></div><p>
331 If multiple foreign servers with these options enabled are involved in a
332 local transaction, multiple remote transactions on those foreign servers
333 are committed or aborted in parallel across those foreign servers when
334 the local transaction is committed or aborted.
336 When these options are enabled, a foreign server with many remote
337 transactions may see a negative performance impact when the local
338 transaction is committed or aborted.
339 </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>
340 By default all foreign tables using <code class="filename">postgres_fdw</code> are assumed
341 to be updatable. This may be overridden using the following option:
342 </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>
343 This option controls whether <code class="filename">postgres_fdw</code> allows foreign
344 tables to be modified using <code class="command">INSERT</code>, <code class="command">UPDATE</code> and
345 <code class="command">DELETE</code> commands. It can be specified for a foreign table
346 or a foreign server. A table-level option overrides a server-level
348 The default is <code class="literal">true</code>.
350 Of course, if the remote table is not in fact updatable, an error
351 would occur anyway. Use of this option primarily allows the error to
352 be thrown locally without querying the remote server. Note however
353 that the <code class="literal">information_schema</code> views will report a
354 <code class="filename">postgres_fdw</code> foreign table to be updatable (or not)
355 according to the setting of this option, without any check of the
357 </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>
358 By default all foreign tables using <code class="filename">postgres_fdw</code> are assumed
359 to be truncatable. This may be overridden using the following option:
360 </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>
361 This option controls whether <code class="filename">postgres_fdw</code> allows
362 foreign tables to be truncated using the <code class="command">TRUNCATE</code>
363 command. It can be specified for a foreign table or a foreign server.
364 A table-level option overrides a server-level option.
365 The default is <code class="literal">true</code>.
367 Of course, if the remote table is not in fact truncatable, an error
368 would occur anyway. Use of this option primarily allows the error to
369 be thrown locally without querying the remote server.
370 </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>
371 <code class="filename">postgres_fdw</code> is able to import foreign table definitions
372 using <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>. This command creates
373 foreign table definitions on the local server that match tables or
374 views present on the remote server. If the remote tables to be imported
375 have columns of user-defined data types, the local server must have
376 compatible types of the same names.
378 Importing behavior can be customized with the following options
379 (given in the <code class="command">IMPORT FOREIGN SCHEMA</code> command):
380 </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>
381 This option controls whether column <code class="literal">COLLATE</code> options
382 are included in the definitions of foreign tables imported
383 from a foreign server. The default is <code class="literal">true</code>. You might
384 need to turn this off if the remote server has a different set of
385 collation names than the local server does, which is likely to be the
386 case if it's running on a different operating system.
387 If you do so, however, there is a very severe risk that the imported
388 table columns' collations will not match the underlying data, resulting
389 in anomalous query behavior.
391 Even when this parameter is set to <code class="literal">true</code>, importing
392 columns whose collation is the remote server's default can be risky.
393 They will be imported with <code class="literal">COLLATE "default"</code>, which
394 will select the local server's default collation, which could be
396 </p></dd><dt><span class="term"><code class="literal">import_default</code> (<code class="type">boolean</code>)</span></dt><dd><p>
397 This option controls whether column <code class="literal">DEFAULT</code> expressions
398 are included in the definitions of foreign tables imported
399 from a foreign server. The default is <code class="literal">false</code>. If you
400 enable this option, be wary of defaults that might get computed
401 differently on the local server than they would be on the remote
402 server; <code class="function">nextval()</code> is a common source of problems.
403 The <code class="command">IMPORT</code> will fail altogether if an imported default
404 expression uses a function or operator that does not exist locally.
405 </p></dd><dt><span class="term"><code class="literal">import_generated</code> (<code class="type">boolean</code>)</span></dt><dd><p>
406 This option controls whether column <code class="literal">GENERATED</code> expressions
407 are included in the definitions of foreign tables imported
408 from a foreign server. The default is <code class="literal">true</code>.
409 The <code class="command">IMPORT</code> will fail altogether if an imported generated
410 expression uses a function or operator that does not exist locally.
411 </p></dd><dt><span class="term"><code class="literal">import_not_null</code> (<code class="type">boolean</code>)</span></dt><dd><p>
412 This option controls whether column <code class="literal">NOT NULL</code>
413 constraints are included in the definitions of foreign tables imported
414 from a foreign server. The default is <code class="literal">true</code>.
415 </p></dd></dl></div><p>
416 Note that constraints other than <code class="literal">NOT NULL</code> will never be
417 imported from the remote tables. Although <span class="productname">PostgreSQL</span>
418 does support check constraints on foreign tables, there is no
419 provision for importing them automatically, because of the risk that a
420 constraint expression could evaluate differently on the local and remote
421 servers. Any such inconsistency in the behavior of a check
422 constraint could lead to hard-to-detect errors in query optimization.
423 So if you wish to import check constraints, you must do so
424 manually, and you should verify the semantics of each one carefully.
425 For more detail about the treatment of check constraints on
426 foreign tables, see <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.
428 Tables or foreign tables which are partitions of some other table are
429 imported only when they are explicitly specified in
430 <code class="literal">LIMIT TO</code> clause. Otherwise they are automatically
431 excluded from <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</span></a>.
432 Since all data can be accessed through the partitioned table
433 which is the root of the partitioning hierarchy, importing only
434 partitioned tables should allow access to all the data without
435 creating extra objects.
436 </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>
437 By default, all connections that <code class="filename">postgres_fdw</code>
438 establishes to foreign servers are kept open in the local session
440 </p><div class="variablelist"><dl class="variablelist"><dt id="POSTGRES-FDW-OPTION-KEEP-CONNECTIONS"><span class="term"><code class="literal">keep_connections</code> (<code class="type">boolean</code>)</span> <a href="#POSTGRES-FDW-OPTION-KEEP-CONNECTIONS" class="id_link">#</a></dt><dd><p>
441 This option controls whether <code class="filename">postgres_fdw</code> keeps
442 the connections to the foreign server open so that subsequent
443 queries can re-use them. It can only be specified for a foreign server.
444 The default is <code class="literal">on</code>. If set to <code class="literal">off</code>,
445 all connections to this foreign server will be discarded at the end of
447 </p></dd><dt id="POSTGRES-FDW-OPTION-USE-SCRAM-PASSTHROUGH"><span class="term"><code class="literal">use_scram_passthrough</code> (<code class="type">boolean</code>)</span> <a href="#POSTGRES-FDW-OPTION-USE-SCRAM-PASSTHROUGH" class="id_link">#</a></dt><dd><p>
448 This option controls whether <code class="filename">postgres_fdw</code> will
449 use the SCRAM pass-through authentication to connect to the foreign
450 server. With SCRAM pass-through authentication,
451 <code class="filename">postgres_fdw</code> uses SCRAM-hashed secrets instead of
452 plain-text user passwords to connect to the remote server. This
453 avoids storing plain-text user passwords in PostgreSQL system
456 To use SCRAM pass-through authentication:
457 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
458 The remote server must request the <code class="literal">scram-sha-256</code>
459 authentication method; otherwise, the connection will fail.
460 </p></li><li class="listitem"><p>
461 The remote server can be of any PostgreSQL version that supports
462 SCRAM. Support for <code class="literal">use_scram_passthrough</code> is
463 only required on the client side (FDW side).
464 </p></li><li class="listitem"><p>
465 The user mapping password is not used.
466 </p></li><li class="listitem"><p>
467 The server running <code class="filename">postgres_fdw</code> and the remote
468 server must have identical SCRAM secrets (encrypted passwords) for
469 the user being used on <code class="filename">postgres_fdw</code> to
470 authenticate on the foreign server (same salt and iterations, not
471 merely the same password).
473 As a corollary, if FDW connections to multiple hosts are to be
474 made, for example for partitioned foreign tables/sharding, then all
475 hosts must have identical SCRAM secrets for the users involved.
476 </p></li><li class="listitem"><p>
477 The current session on the PostgreSQL instance that makes the
478 outgoing FDW connections also must also use SCRAM authentication
479 for its incoming client connection. (Hence
480 <span class="quote">“<span class="quote">pass-through</span>”</span>: SCRAM must be used going in and out.)
481 This is a technical requirement of the SCRAM protocol.
482 </p></li></ul></div><p>
483 </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(
484 IN check_conn boolean DEFAULT false, OUT server_name text,
485 OUT user_name text, OUT valid boolean, OUT used_in_xact boolean,
486 OUT closed boolean, OUT remote_backend_pid int4)
487 returns setof record</code></span></dt><dd><p>
488 This function returns information about all open connections postgres_fdw
489 has established from the local session to foreign servers. If there are
490 no open connections, no records are returned.
492 If <code class="literal">check_conn</code> is set to <code class="literal">true</code>,
493 the function checks the status of each connection and shows
494 the result in the <code class="literal">closed</code> column.
495 This feature is currently available only on systems that support
496 the non-standard <code class="symbol">POLLRDHUP</code> extension to
497 the <code class="symbol">poll</code> system call, including Linux.
498 This is useful to check if all connections used within
499 a transaction are still open. If any connections are closed,
500 the transaction cannot be committed successfully,
501 so it is better to roll back as soon as a closed connection is detected,
502 rather than continuing to the end. Users can roll back the transaction
503 immediately if the function reports connections where both
504 <code class="literal">used_in_xact</code> and <code class="literal">closed</code> are
505 <code class="literal">true</code>.
507 Example usage of the function:
508 </p><pre class="screen">
509 postgres=# SELECT * FROM postgres_fdw_get_connections(true);
510 server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
511 -------------+-----------+-------+--------------+-----------------------------
512 loopback1 | postgres | t | t | f | 1353340
513 loopback2 | public | t | t | f | 1353120
514 loopback3 | | f | t | f | 1353156
516 The output columns are described in
517 <a class="xref" href="postgres-fdw.html#POSTGRES-FDW-GET-CONNECTIONS-COLUMNS" title="Table F.28. postgres_fdw_get_connections Output Columns">Table F.28</a>.
518 </p><div class="table" id="POSTGRES-FDW-GET-CONNECTIONS-COLUMNS"><p class="title"><strong>Table F.28. <code class="function">postgres_fdw_get_connections</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="postgres_fdw_get_connections Output Columns" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Column</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="structfield">server_name</code></td><td><code class="type">text</code></td><td>
519 The foreign server name of this connection. If the server is
520 dropped but the connection remains open (i.e., marked as
521 invalid), this will be <code class="literal">NULL</code>.
522 </td></tr><tr><td><code class="structfield">user_name</code></td><td><code class="type">text</code></td><td>
523 Name of the local user mapped to the foreign server of this
524 connection, or <code class="literal">public</code> if a public mapping is used.
525 If the user mapping is dropped but the connection remains open
526 (i.e., marked as invalid), this will be <code class="literal">NULL</code>.
527 </td></tr><tr><td><code class="structfield">valid</code></td><td><code class="type">boolean</code></td><td>
528 False if this connection is invalid, meaning it is used in
529 the current transaction, but its foreign server or
530 user mapping has been changed or dropped.
531 The invalid connection will be closed at the end of
532 the transaction. True is returned otherwise.
533 </td></tr><tr><td><code class="structfield">used_in_xact</code></td><td><code class="type">boolean</code></td><td>
534 True if this connection is used in the current transaction.
535 </td></tr><tr><td><code class="structfield">closed</code></td><td><code class="type">boolean</code></td><td>
536 True if this connection is closed, false otherwise.
537 <code class="literal">NULL</code> is returned if <code class="literal">check_conn</code>
538 is set to <code class="literal">false</code> or if the connection status check
539 is not available on this platform.
540 </td></tr><tr><td><code class="structfield">remote_backend_pid</code></td><td><code class="type">int4</code></td><td>
541 Process ID of the remote backend, on the foreign server,
542 handling the connection. If the remote backend is terminated and
543 the connection is closed (with <code class="literal">closed</code> set to
544 <code class="literal">true</code>), this still shows the process ID of
545 the terminated backend.
546 </td></tr></tbody></table></div></div><br class="table-break" /></dd><dt><span class="term"><code class="function">postgres_fdw_disconnect(server_name text) returns boolean</code></span></dt><dd><p>
547 This function discards the open connections that are established by
548 <code class="filename">postgres_fdw</code> from the local session to
549 the foreign server with the given name. Note that there can be
550 multiple connections to the given server using different user mappings.
551 If the connections are used in the current local transaction,
552 they are not disconnected and warning messages are reported.
553 This function returns <code class="literal">true</code> if it disconnects
554 at least one connection, otherwise <code class="literal">false</code>.
555 If no foreign server with the given name is found, an error is reported.
556 Example usage of the function:
557 </p><pre class="screen">
558 postgres=# SELECT postgres_fdw_disconnect('loopback1');
559 postgres_fdw_disconnect
560 -------------------------
563 </p></dd><dt><span class="term"><code class="function">postgres_fdw_disconnect_all() returns boolean</code></span></dt><dd><p>
564 This function discards all the open connections that are established by
565 <code class="filename">postgres_fdw</code> from the local session to
566 foreign servers. If the connections are used in the current local
567 transaction, they are not disconnected and warning messages are reported.
568 This function returns <code class="literal">true</code> if it disconnects
569 at least one connection, otherwise <code class="literal">false</code>.
570 Example usage of the function:
571 </p><pre class="screen">
572 postgres=# SELECT postgres_fdw_disconnect_all();
573 postgres_fdw_disconnect_all
574 -----------------------------
577 </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>
578 <code class="filename">postgres_fdw</code> establishes a connection to a
579 foreign server during the first query that uses a foreign table
580 associated with the foreign server. By default this connection
581 is kept and re-used for subsequent queries in the same session.
582 This behavior can be controlled using
583 <code class="literal">keep_connections</code> option for a foreign server. If
584 multiple user identities (user mappings) are used to access the foreign
585 server, a connection is established for each user mapping.
587 When changing the definition of or removing a foreign server or
588 a user mapping, the associated connections are closed.
589 But note that if any connections are in use in the current local transaction,
590 they are kept until the end of the transaction.
591 Closed connections will be re-established when they are necessary
592 by future queries using a foreign table.
594 Once a connection to a foreign server has been established,
595 it's by default kept until the local or corresponding remote
596 session exits. To disconnect a connection explicitly,
597 <code class="literal">keep_connections</code> option for a foreign server
599 <code class="function">postgres_fdw_disconnect</code> and
600 <code class="function">postgres_fdw_disconnect_all</code> functions
601 may be used. For example, these are useful to close
602 connections that are no longer necessary, thereby releasing
603 connections on the foreign server.
604 </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>
605 During a query that references any remote tables on a foreign server,
606 <code class="filename">postgres_fdw</code> opens a transaction on the
607 remote server if one is not already open corresponding to the current
608 local transaction. The remote transaction is committed or aborted when
609 the local transaction commits or aborts. Savepoints are similarly
610 managed by creating corresponding remote savepoints.
612 The remote transaction uses <code class="literal">SERIALIZABLE</code>
613 isolation level when the local transaction has <code class="literal">SERIALIZABLE</code>
614 isolation level; otherwise it uses <code class="literal">REPEATABLE READ</code>
615 isolation level. This choice ensures that if a query performs multiple
616 table scans on the remote server, it will get snapshot-consistent results
617 for all the scans. A consequence is that successive queries within a
618 single transaction will see the same data from the remote server, even if
619 concurrent updates are occurring on the remote server due to other
620 activities. That behavior would be expected anyway if the local
621 transaction uses <code class="literal">SERIALIZABLE</code> or <code class="literal">REPEATABLE READ</code>
622 isolation level, but it might be surprising for a <code class="literal">READ
623 COMMITTED</code> local transaction. A future
624 <span class="productname">PostgreSQL</span> release might modify these rules.
626 Note that it is currently not supported by
627 <code class="filename">postgres_fdw</code> to prepare the remote transaction for
629 </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>
630 <code class="filename">postgres_fdw</code> attempts to optimize remote queries to reduce
631 the amount of data transferred from foreign servers. This is done by
632 sending query <code class="literal">WHERE</code> clauses to the remote server for
633 execution, and by not retrieving table columns that are not needed for
634 the current query. To reduce the risk of misexecution of queries,
635 <code class="literal">WHERE</code> clauses are not sent to the remote server unless they use
636 only data types, operators, and functions that are built-in or belong to an
637 extension that's listed in the foreign server's <code class="literal">extensions</code>
638 option. Operators and functions in such clauses must
639 be <code class="literal">IMMUTABLE</code> as well.
640 For an <code class="command">UPDATE</code> or <code class="command">DELETE</code> query,
641 <code class="filename">postgres_fdw</code> attempts to optimize the query execution by
642 sending the whole query to the remote server if there are no query
643 <code class="literal">WHERE</code> clauses that cannot be sent to the remote server,
644 no local joins for the query, no row-level local <code class="literal">BEFORE</code> or
645 <code class="literal">AFTER</code> triggers or stored generated columns on the target
646 table, and no <code class="literal">CHECK OPTION</code> constraints from parent
647 views. In <code class="command">UPDATE</code>,
648 expressions to assign to target columns must use only built-in data types,
649 <code class="literal">IMMUTABLE</code> operators, or <code class="literal">IMMUTABLE</code> functions,
650 to reduce the risk of misexecution of the query.
652 When <code class="filename">postgres_fdw</code> encounters a join between foreign tables on
653 the same foreign server, it sends the entire join to the foreign server,
654 unless for some reason it believes that it will be more efficient to fetch
655 rows from each table individually, or unless the table references involved
656 are subject to different user mappings. While sending the <code class="literal">JOIN</code>
657 clauses, it takes the same precautions as mentioned above for the
658 <code class="literal">WHERE</code> clauses.
660 The query that is actually sent to the remote server for execution can
661 be examined using <code class="command">EXPLAIN VERBOSE</code>.
662 </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>
663 In the remote sessions opened by <code class="filename">postgres_fdw</code>,
664 the <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> parameter is set to
665 just <code class="literal">pg_catalog</code>, so that only built-in objects are visible
666 without schema qualification. This is not an issue for queries
667 generated by <code class="filename">postgres_fdw</code> itself, because it always
668 supplies such qualification. However, this can pose a hazard for
669 functions that are executed on the remote server via triggers or rules
670 on remote tables. For example, if a remote table is actually a view,
671 any functions used in that view will be executed with the restricted
672 search path. It is recommended to schema-qualify all names in such
673 functions, or else attach <code class="literal">SET search_path</code> options
674 (see <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>) to such functions
675 to establish their expected search path environment.
677 <code class="filename">postgres_fdw</code> likewise establishes remote session settings
678 for various parameters:
679 </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>
680 <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> is set to <code class="literal">UTC</code>
681 </p></li><li class="listitem"><p>
682 <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> is set to <code class="literal">ISO</code>
683 </p></li><li class="listitem"><p>
684 <a class="xref" href="runtime-config-client.html#GUC-INTERVALSTYLE">IntervalStyle</a> is set to <code class="literal">postgres</code>
685 </p></li><li class="listitem"><p>
686 <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
687 servers 9.0 and newer and is set to <code class="literal">2</code> for older versions
688 </p></li></ul></div><p>
689 These are less likely to be problematic than <code class="varname">search_path</code>, but
690 can be handled with function <code class="literal">SET</code> options if the need arises.
692 It is <span class="emphasis"><em>not</em></span> recommended that you override this behavior by
693 changing the session-level settings of these parameters; that is likely
694 to cause <code class="filename">postgres_fdw</code> to malfunction.
695 </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>
696 <code class="filename">postgres_fdw</code> can be used with remote servers dating back
697 to <span class="productname">PostgreSQL</span> 8.3. Read-only capability is available
700 A limitation however is that <code class="filename">postgres_fdw</code>
701 generally assumes that immutable built-in functions and operators are
702 safe to send to the remote server for execution, if they appear in a
703 <code class="literal">WHERE</code> clause for a foreign table. Thus, a built-in
704 function that was added since the remote server's release might be sent
705 to it for execution, resulting in <span class="quote">“<span class="quote">function does not exist</span>”</span> or
706 a similar error. This type of failure can be worked around by
707 rewriting the query, for example by embedding the foreign table
708 reference in a sub-<code class="literal">SELECT</code> with <code class="literal">OFFSET 0</code> as an
709 optimization fence, and placing the problematic function or operator
710 outside the sub-<code class="literal">SELECT</code>.
712 Another limitation is that when executing <code class="command">INSERT</code>
713 statements with an <code class="literal">ON CONFLICT DO NOTHING</code> clause on
714 a foreign table, the remote server must be running
715 <span class="productname">PostgreSQL</span> 9.5 or later,
716 as earlier versions do not support this feature.
717 </p></div><div class="sect2" id="POSTGRES-FDW-WAIT-EVENTS"><div class="titlepage"><div><div><h3 class="title">F.38.8. Wait Events <a href="#POSTGRES-FDW-WAIT-EVENTS" class="id_link">#</a></h3></div></div></div><p>
718 <code class="filename">postgres_fdw</code> can report the following wait events
719 under the wait event type <code class="literal">Extension</code>:
720 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">PostgresFdwCleanupResult</code></span></dt><dd><p>
721 Waiting for transaction abort on remote server.
722 </p></dd><dt><span class="term"><code class="literal">PostgresFdwConnect</code></span></dt><dd><p>
723 Waiting to establish a connection to a remote server.
724 </p></dd><dt><span class="term"><code class="literal">PostgresFdwGetResult</code></span></dt><dd><p>
725 Waiting to receive the results of a query from a remote server.
726 </p></dd></dl></div></div><div class="sect2" id="POSTGRES-FDW-CONFIGURATION-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">F.38.9. 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">
727 <code class="varname">postgres_fdw.application_name</code> (<code class="type">string</code>)
728 <a id="id-1.11.7.48.19.2.1.1.3" class="indexterm"></a>
729 </span> <a href="#GUC-PGFDW-APPLICATION-NAME" class="id_link">#</a></dt><dd><p>
730 Specifies a value for <a class="xref" href="runtime-config-logging.html#GUC-APPLICATION-NAME">application_name</a>
731 configuration parameter used when <code class="filename">postgres_fdw</code>
732 establishes a connection to a foreign server. This overrides
733 <code class="varname">application_name</code> option of the server object.
734 Note that change of this parameter doesn't affect any existing
735 connections until they are re-established.
737 <code class="varname">postgres_fdw.application_name</code> can be any string
738 of any length and contain even non-ASCII characters. However when
739 it's passed to and used as <code class="varname">application_name</code>
740 in a foreign server, note that it will be truncated to less than
741 <code class="symbol">NAMEDATALEN</code> characters.
742 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>.
743 See <a class="xref" href="runtime-config-logging.html#GUC-APPLICATION-NAME">application_name</a> for details.
745 <code class="literal">%</code> characters begin <span class="quote">“<span class="quote">escape sequences</span>”</span>
746 that are replaced with status information as outlined below.
747 Unrecognized escapes are ignored. Other characters are copied straight
748 to the application name. Note that it's not allowed to specify a
749 plus/minus sign or a numeric literal after the <code class="literal">%</code>
750 and before the option, for alignment and padding.
751 </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>
752 Session ID on local server
753 (see <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> for details)
754 </td></tr><tr><td><code class="literal">%C</code></td><td>
755 Cluster name on local server
756 (see <a class="xref" href="runtime-config-logging.html#GUC-CLUSTER-NAME">cluster_name</a> for details)
757 </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>
758 For example, suppose user <code class="literal">local_user</code> establishes
759 a connection from database <code class="literal">local_db</code> to
760 <code class="literal">foreign_db</code> as user <code class="literal">foreign_user</code>,
761 the setting <code class="literal">'db=%d, user=%u'</code> is replaced with
762 <code class="literal">'db=local_db, user=local_user'</code>.
763 </p></dd></dl></div></div><div class="sect2" id="POSTGRES-FDW-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">F.38.10. Examples <a href="#POSTGRES-FDW-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>
764 Here is an example of creating a foreign table with
765 <code class="literal">postgres_fdw</code>. First install the extension:
766 </p><pre class="programlisting">
767 CREATE EXTENSION postgres_fdw;
769 Then create a foreign server using <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>.
770 In this example we wish to connect to a <span class="productname">PostgreSQL</span> server
771 on host <code class="literal">192.83.123.89</code> listening on
772 port <code class="literal">5432</code>. The database to which the connection is made
773 is named <code class="literal">foreign_db</code> on the remote server:
775 </p><pre class="programlisting">
776 CREATE SERVER foreign_server
777 FOREIGN DATA WRAPPER postgres_fdw
778 OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
781 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
782 needed as well to identify the role that will be used on the remote
785 </p><pre class="programlisting">
786 CREATE USER MAPPING FOR local_user
787 SERVER foreign_server
788 OPTIONS (user 'foreign_user', password 'password');
791 Now it is possible to create a foreign table with
792 <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>. In this example we
793 wish to access the table named <code class="structname">some_schema.some_table</code>
794 on the remote server. The local name for it will
795 be <code class="structname">foreign_table</code>:
797 </p><pre class="programlisting">
798 CREATE FOREIGN TABLE foreign_table (
802 SERVER foreign_server
803 OPTIONS (schema_name 'some_schema', table_name 'some_table');
806 It's essential that the data types and other properties of the columns
807 declared in <code class="command">CREATE FOREIGN TABLE</code> match the actual remote table.
808 Column names must match as well, unless you attach <code class="literal">column_name</code>
809 options to the individual columns to show how they are named in the remote
811 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
812 preferable to constructing foreign table definitions manually.
813 </p></div><div class="sect2" id="POSTGRES-FDW-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.38.11. Author <a href="#POSTGRES-FDW-AUTHOR" class="id_link">#</a></h3></div></div></div><p>
814 Shigeru Hanada <code class="email"><<a class="email" href="mailto:shigeru.hanada@gmail.com">shigeru.hanada@gmail.com</a>></code>
815 </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 18.0 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>
begriffs.com / projects / ai-pg / blob