1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
|
<?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.38. 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="pgwalinspect.html" title="F.37. pg_walinspect" /><link rel="next" href="seg.html" title="F.39. seg" /></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</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgwalinspect.html" title="F.37. pg_walinspect">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 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="seg.html" title="F.39. seg">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</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.11">F.38.1. FDW Options of postgres_fdw</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.12">F.38.2. Functions</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.13">F.38.3. Connection Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.14">F.38.4. Transaction Management</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.15">F.38.5. Remote Query Optimization</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.16">F.38.6. Remote Query Execution Environment</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.17">F.38.7. Cross-Version Compatibility</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.18">F.38.8. Configuration Parameters</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.19">F.38.9. Examples</a></span></dt><dt><span class="sect2"><a href="postgres-fdw.html#id-1.11.7.47.20">F.38.10. Author</a></span></dt></dl></div><a id="id-1.11.7.47.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.12. 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.47.11"><div class="titlepage"><div><div><h3 class="title">F.38.1. FDW Options of postgres_fdw</h3></div></div></div><div class="sect3" id="id-1.11.7.47.11.2"><div class="titlepage"><div><div><h4 class="title">F.38.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">application_name</code> - this may appear in
<span class="emphasis"><em>either or both</em></span> a connection and
<a class="xref" href="postgres-fdw.html#GUC-PGFDW-APPLICATION-NAME">postgres_fdw.application_name</a>.
If both are present, <code class="varname">postgres_fdw.application_name</code>
overrides the connection setting.
Unlike <span class="application">libpq</span>,
<code class="filename">postgres_fdw</code> allows
<code class="varname">application_name</code> to include
<span class="quote">“<span class="quote">escape sequences</span>”</span>.
See <a class="xref" href="postgres-fdw.html#GUC-PGFDW-APPLICATION-NAME">postgres_fdw.application_name</a> for details.
</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.47.11.3"><div class="titlepage"><div><div><h4 class="title">F.38.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> (<code class="type">string</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> (<code class="type">string</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> (<code class="type">string</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.47.11.4"><div class="titlepage"><div><div><h4 class="title">F.38.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> (<code class="type">boolean</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> (<code class="type">floating point</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> (<code class="type">floating point</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.47.11.5"><div class="titlepage"><div><div><h4 class="title">F.38.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> (<code class="type">string</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> (<code class="type">integer</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> (<code class="type">integer</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.47.11.6"><div class="titlepage"><div><div><h4 class="title">F.38.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> (<code class="type">boolean</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.47.11.7"><div class="titlepage"><div><div><h4 class="title">F.38.1.6. Transaction Management Options</h4></div></div></div><p>
As described in the Transaction Management section, in
<code class="filename">postgres_fdw</code> transactions are managed by creating
corresponding remote transactions, and subtransactions are managed by
creating corresponding remote subtransactions. When multiple remote
transactions are involved in the current local transaction, by default
<code class="filename">postgres_fdw</code> commits those remote transactions
serially when the local transaction is committed. When multiple remote
subtransactions are involved in the current local subtransaction, by
default <code class="filename">postgres_fdw</code> commits those remote
subtransactions serially when the local subtransaction is committed.
Performance can be improved with the following option:
</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>
This option controls whether <code class="filename">postgres_fdw</code> commits
in parallel remote transactions opened on a foreign server in a local
transaction when the local transaction is committed. This setting also
applies to remote and local subtransactions. This option can only be
specified for foreign servers, not per-table. The default is
<code class="literal">false</code>.
</p><p>
If multiple foreign servers with this option enabled are involved in a
local transaction, multiple remote transactions on those foreign
servers are committed in parallel across those foreign servers when
the local transaction is committed.
</p><p>
When this option is enabled, a foreign server with many remote
transactions may see a negative performance impact when the local
transaction is committed.
</p></dd></dl></div></div><div class="sect3" id="id-1.11.7.47.11.8"><div class="titlepage"><div><div><h4 class="title">F.38.1.7. 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> (<code class="type">boolean</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.47.11.9"><div class="titlepage"><div><div><h4 class="title">F.38.1.8. 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> (<code class="type">boolean</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.47.11.10"><div class="titlepage"><div><div><h4 class="title">F.38.1.9. 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> (<code class="type">boolean</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> (<code class="type">boolean</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> (<code class="type">boolean</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> (<code class="type">boolean</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.47.11.11"><div class="titlepage"><div><div><h4 class="title">F.38.1.10. 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> (<code class="type">boolean</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.47.12"><div class="titlepage"><div><div><h3 class="title">F.38.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.47.13"><div class="titlepage"><div><div><h3 class="title">F.38.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.47.14"><div class="titlepage"><div><div><h3 class="title">F.38.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.47.15"><div class="titlepage"><div><div><h3 class="title">F.38.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.47.16"><div class="titlepage"><div><div><h3 class="title">F.38.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.47.17"><div class="titlepage"><div><div><h3 class="title">F.38.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.47.18"><div class="titlepage"><div><div><h3 class="title">F.38.8. Configuration Parameters</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-PGFDW-APPLICATION-NAME"><span class="term">
<code class="varname">postgres_fdw.application_name</code> (<code class="type">string</code>)
<a id="id-1.11.7.47.18.2.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Specifies a value for <a class="xref" href="runtime-config-logging.html#GUC-APPLICATION-NAME">application_name</a>
configuration parameter used when <code class="filename">postgres_fdw</code>
establishes a connection to a foreign server. This overrides
<code class="varname">application_name</code> option of the server object.
Note that change of this parameter doesn't affect any existing
connections until they are re-established.
</p><p>
<code class="varname">postgres_fdw.application_name</code> can be any string
of any length and contain even non-ASCII characters. However when
it's passed to and used as <code class="varname">application_name</code>
in a foreign server, note that it will be truncated to less than
<code class="symbol">NAMEDATALEN</code> characters and anything other than
printable ASCII characters will be replaced with question
marks (<code class="literal">?</code>).
See <a class="xref" href="runtime-config-logging.html#GUC-APPLICATION-NAME">application_name</a> for details.
</p><p>
<code class="literal">%</code> characters begin <span class="quote">“<span class="quote">escape sequences</span>”</span>
that are replaced with status information as outlined below.
Unrecognized escapes are ignored. Other characters are copied straight
to the application name. Note that it's not allowed to specify a
plus/minus sign or a numeric literal after the <code class="literal">%</code>
and before the option, for alignment and padding.
</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>
Session ID on local server
(see <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> for details)
</td></tr><tr><td><code class="literal">%C</code></td><td>
Cluster name on local server
(see <a class="xref" href="runtime-config-logging.html#GUC-CLUSTER-NAME">cluster_name</a> for details)
</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>
For example, suppose user <code class="literal">local_user</code> establishes
a connection from database <code class="literal">local_db</code> to
<code class="literal">foreign_db</code> as user <code class="literal">foreign_user</code>,
the setting <code class="literal">'db=%d, user=%u'</code> is replaced with
<code class="literal">'db=local_db, user=local_user'</code>.
</p></dd></dl></div></div><div class="sect2" id="id-1.11.7.47.19"><div class="titlepage"><div><div><h3 class="title">F.38.9. 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.47.20"><div class="titlepage"><div><div><h3 class="title">F.38.10. 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 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">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.39. seg">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.37. pg_walinspect </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.39. seg</td></tr></table></div></body></html>
|
