postgres_fdwforeign data wrapper can have the same options that libpq accepts in connection strings, as described in Section 33.1.2, except that these options are not allowed or have special handling:
sslpassword(specify these in a user mapping, instead, or use a service file)
client_encoding(this is automatically set from the local server encoding)
fallback_application_name(always set to
sslcert- these may appear in either or both a connection and a user mapping. If both are present, the user mapping setting overrides the connection setting.
passwordoption for user mappings belonging to non-superusers.
password_required 'false', e.g.,
publicrole. Keep in mind that the mapped user can potentially use any client certificates,
.pg_service.confetc 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
postgres_fdwretrieves 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
postgres_fdwprovides the following options to control how cost estimation is done:
EXPLAINcommands to obtain cost estimates. A setting for a foreign table overrides any setting for its server, but only for that table. The default is
postgres_fdwobtains row count and cost estimates from the remote server and then adds
fdw_tuple_costto the cost estimates. When
postgres_fdwperforms local row count and cost estimation and then adds
fdw_tuple_costto the cost estimates. This local estimation is unlikely to be very accurate unless local copies of the remote table's statistics are available. Running ANALYZE 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.
WHEREclauses 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
WHEREclauses for remote execution. This behavior can be controlled using the following option:
extensionsoption, it is the user's responsibility that the listed extensions exist and behave identically on both the local and remote servers. Otherwise, remote queries may fail or behave unexpectedly.
postgres_fdwshould 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
postgres_fdware assumed to be updatable. This may be overridden using the following option:
postgres_fdwallows foreign tables to be modified using
DELETEcommands. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is
information_schemaviews will report a
postgres_fdwforeign table to be updatable (or not) according to the setting of this option, without any check of the remote server.
postgres_fdwis able to import foreign table definitions using IMPORT FOREIGN SCHEMA. 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.
IMPORT FOREIGN SCHEMAcommand):
COLLATEoptions are included in the definitions of foreign tables imported from a foreign server. The default is
true. 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.
DEFAULTexpressions are included in the definitions of foreign tables imported from a foreign server. The default is
false. 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;
nextval()is a common source of problems. The
IMPORTwill fail altogether if an imported default expression uses a function or operator that does not exist locally.
NOT NULLconstraints are included in the definitions of foreign tables imported from a foreign server. The default is
NOT NULLwill never be imported from the remote tables. Although PostgreSQL does support
CHECKconstraints 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
CHECKconstraint could lead to hard-to-detect errors in query optimization. So if you wish to import
CHECKconstraints, you must do so manually, and you should verify the semantics of each one carefully. For more detail about the treatment of
CHECKconstraints on foreign tables, see CREATE FOREIGN TABLE.
postgres_fdwestablishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept and re-used for subsequent queries in the same session. However, if multiple user identities (user mappings) are used to access the foreign server, a connection is established for each user mapping.
postgres_fdwopens 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.
SERIALIZABLEisolation level when the local transaction has
SERIALIZABLEisolation level; otherwise it uses
REPEATABLE READisolation 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
REPEATABLE READisolation level, but it might be surprising for a
READ COMMITTEDlocal transaction. A future PostgreSQL release might modify these rules.
postgres_fdwto prepare the remote transaction for two-phase commit.
postgres_fdwattempts to optimize remote queries to reduce the amount of data transferred from foreign servers. This is done by sending query
WHEREclauses 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,
WHEREclauses 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
extensionsoption. Operators and functions in such clauses must be
IMMUTABLEas well. For an
postgres_fdwattempts to optimize the query execution by sending the whole query to the remote server if there are no query
WHEREclauses that cannot be sent to the remote server, no local joins for the query, no row-level local
AFTERtriggers or stored generated columns on the target table, and no
CHECK OPTIONconstraints from parent views. In
UPDATE, expressions to assign to target columns must use only built-in data types,
IMMUTABLEfunctions, to reduce the risk of misexecution of the query.
postgres_fdwencounters 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
JOINclauses, it takes the same precautions as mentioned above for the
postgres_fdw, the search_path parameter is set to just
pg_catalog, so that only built-in objects are visible without schema qualification. This is not an issue for queries generated by
postgres_fdwitself, 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
SET search_pathoptions (see CREATE FUNCTION) to such functions to establish their expected search path environment.
postgres_fdwlikewise establishes remote session settings for various parameters:
search_path, but can be handled with function
SEToptions if the need arises.
postgres_fdwcan be used with remote servers dating back to PostgreSQL 8.3. Read-only capability is available back to 8.1. A limitation however is that
postgres_fdwgenerally assumes that immutable built-in functions and operators are safe to send to the remote server for execution, if they appear in a
WHEREclause 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 “function does not exist” 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-
OFFSET 0as an optimization fence, and placing the problematic function or operator outside the sub-