From d0d75c402217421b691050857eb3d7af82d0c770 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 21 Feb 2013 05:26:23 -0500 Subject: Add postgres_fdw contrib module. There's still a lot of room for improvement, but it basically works, and we need this to be present before we can do anything much with the writable-foreign-tables patch. So let's commit it and get on with testing. Shigeru Hanada, reviewed by KaiGai Kohei and Tom Lane --- doc/src/sgml/client-auth.sgml | 2 +- doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/dblink.sgml | 26 +- doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/libpq.sgml | 2 +- doc/src/sgml/postgres-fdw.sgml | 325 ++++++++++++++++++++++ doc/src/sgml/queries.sgml | 2 +- doc/src/sgml/recovery-config.sgml | 3 +- doc/src/sgml/ref/create_foreign_data_wrapper.sgml | 8 - doc/src/sgml/ref/create_foreign_table.sgml | 12 +- doc/src/sgml/ref/create_server.sgml | 22 +- doc/src/sgml/ref/grant.sgml | 7 +- doc/src/sgml/ref/pg_isready.sgml | 3 +- doc/src/sgml/ref/psql-ref.sgml | 9 +- doc/src/sgml/runtime.sgml | 2 +- 15 files changed, 372 insertions(+), 53 deletions(-) create mode 100644 doc/src/sgml/postgres-fdw.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml index d9d14955f0b..9fc583ce574 100644 --- a/doc/src/sgml/client-auth.sgml +++ b/doc/src/sgml/client-auth.sgml @@ -1098,7 +1098,7 @@ omicron bryanh guest1 servicename can be set on the server side using the configuration parameter, and on the client side using the krbsrvname connection parameter. (See - also .) The installation default can be + also .) The installation default can be changed from the default postgres at build time using ./configure --with-krb-srvnam=whatever. In most environments, diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index 6b13a0abefb..39e9827fca7 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -132,6 +132,7 @@ CREATE EXTENSION module_name FROM unpackaged; &pgstatstatements; &pgstattuple; &pgtrgm; + &postgres-fdw; &seg; &sepgsql; &contrib-spi; diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml index 186ab865865..4bf65c67b1a 100644 --- a/doc/src/sgml/dblink.sgml +++ b/doc/src/sgml/dblink.sgml @@ -8,11 +8,16 @@ - dblink is a module which supports connections to + dblink is a module that supports connections to other PostgreSQL databases from within a database session. + + See also , which provides roughly the same + functionality using a more modern and standards-compliant infrastructure. + + dblink_connect @@ -47,12 +52,10 @@ dblink_connect(text connname, text connstr) returns text The connection string may also be the name of an existing foreign server. It is recommended to use the foreign-data wrapper - dblink_fdw when defining the corresponding foreign - server. See the example below, as well as the following: - - - - + dblink_fdw when defining the foreign + server. See the example below, as well as + and + . @@ -77,8 +80,8 @@ dblink_connect(text connname, text connstr) returns text libpq-style connection info string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd. - For details see PQconnectdb in - . + For details see . + Alternatively, the name of a foreign server. @@ -133,9 +136,10 @@ SELECT dblink_connect('myconn', 'dbname=postgres'); -- ERROR: password is required -- DETAIL: Non-superuser cannot connect if the server does not request a password. -- HINT: Target server's authentication method must be changed. -CREATE USER dblink_regression_test WITH PASSWORD 'secret'; + CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression'); +CREATE USER dblink_regression_test WITH PASSWORD 'secret'; CREATE USER MAPPING FOR dblink_regression_test SERVER fdtest OPTIONS (user 'dblink_regression_test', password 'secret'); GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test; GRANT SELECT ON TABLE foo TO dblink_regression_test; @@ -166,7 +170,7 @@ SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) \c - :ORIGINAL_USER REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test; -REVOKE SELECT ON TABLE foo FROM dblink_regression_test; +REVOKE SELECT ON TABLE foo FROM dblink_regression_test; DROP USER MAPPING FOR dblink_regression_test SERVER fdtest; DROP USER dblink_regression_test; DROP SERVER fdtest; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 368f9321c88..5d55ef357b0 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -134,6 +134,7 @@ + diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index aa2ec2ab7bb..775d25054f8 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -6941,7 +6941,7 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) The file uses an INI file format where the section name is the service name and the parameters are connection - parameters; see for a list. For + parameters; see for a list. For example: # comment diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml new file mode 100644 index 00000000000..61b77774aee --- /dev/null +++ b/doc/src/sgml/postgres-fdw.sgml @@ -0,0 +1,325 @@ + + + + postgres_fdw + + + postgres_fdw + + + + The postgres_fdw module provides the foreign-data wrapper + postgres_fdw, which can be used to access data + stored in external PostgreSQL servers. + + + + The functionality provided by this module overlaps substantially + with the functionality of the older module. + But postgres_fdw provides more transparent and + standards-compliant syntax for accessing remote tables, and can give + better performance in many cases. + + + + To prepare for remote access using postgres_fdw: + + + + Install the postgres_fdw extension using . + + + + + Create a foreign server object, using , + to represent each remote database you want to connect to. + Specify connection information, except user and + password, as options of the server object. + + + + + Create a user mapping, using , for + each database user you want to allow to access each foreign server. + Specify the remote user name and password to use as + user and password options of the + user mapping. + + + + + Create a foreign table, using , + 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. + + + + + + + Now you need only SELECT from a foreign table to access + the data stored in its underlying remote table. + + + + 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 postgres_fdw + 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 WHERE clauses + slightly differently from the local server. + + + + 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. + + + + FDW Options of postgres_fdw + + + Connection Options + + + A foreign server using the postgres_fdw foreign data wrapper + can have the same options that libpq accepts in + connection strings, as described in , + except that these options are not allowed: + + + + + user and password (specify these + for a user mapping, instead) + + + + + client_encoding (this is automatically set from the local + server encoding) + + + + + fallback_application_name (always set to + postgres_fdw) + + + + + + + Only superusers may connect to foreign servers without password + authentication, so always specify the password option + for user mappings belonging to non-superusers. + + + + + Object Name Options + + + These options can be used to control the names used in SQL statements + sent to the remote PostgreSQL server. These + options are needed when a foreign table is created with names different + from the underlying remote table's names. + + + + + + schema_name + + + 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. + + + + + + table_name + + + 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. + + + + + + column_name + + + 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. + + + + + + + + + + Cost Estimation Options + + + postgres_fdw 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 postgres_fdw provides the following options to control + how cost estimation is done: + + + + + + use_remote_estimate + + + This option, which can be specified for a foreign table or a foreign + server, controls whether postgres_fdw issues remote + EXPLAIN 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 false. + + + + + + fdw_startup_cost + + + This option, which can be specified for a foreign server, is a numeric + value that is added to the estimated startup cost of any foreign-table + scan on that server. This represents the additional overhead of + establishing a connection, parsing and planning the query on the + remote side, etc. + The default value is 100. + + + + + + fdw_tuple_cost + + + This option, which can be specified for a foreign server, is a numeric + value that is used as extra cost per-tuple for foreign-table + scans on that server. This represents the additional overhead of + data transfer between servers. You might increase or decrease this + number to reflect higher or lower network delay to the remote server. + The default value is 0.01. + + + + + + + + When use_remote_estimate is true, + postgres_fdw obtains rowcount and cost estimates from the + remote server and then adds fdw_startup_cost and + fdw_tuple_cost to the cost estimates. When + use_remote_estimate is false, + postgres_fdw performs local rowcount and cost estimation + and then adds fdw_startup_cost and + fdw_tuple_cost 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 + 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. + + + + + + + Connection Management + + + postgres_fdw establishes a connection to a + foreign server during the first query that uses a foreign table + associated with the foreign server. This connection is kept and + re-used for subsequent queries in the same session. However, if + multiple user identities (user mappings) are used to access the foreign + server, a connection is established for each user mapping. + + + + + Transaction Management + + + During a query that references any remote tables on a foreign server, + postgres_fdw 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. + + + + The remote transaction uses SERIALIZABLE + isolation level when the local transaction has SERIALIZABLE + isolation level; otherwise it uses REPEATABLE READ + 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 SERIALIZABLE or REPEATABLE READ + isolation level, but it might be surprising for a READ + COMMITTED local transaction. A future + PostgreSQL release might modify these rules. + + + + + Remote Query Optimization + + + postgres_fdw attempts to optimize remote queries to reduce + the amount of data transferred from foreign servers. This is done by + sending query WHERE 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, + WHERE clauses are not sent to the remote server unless they use + only built-in data types, operators, and functions. Operators and + functions in the clauses must be IMMUTABLE as well. + + + + The query that is actually sent to the remote server for execution can + be examined using EXPLAIN VERBOSE. + + + + + Author + + Shigeru Hanada shigeru.hanada@gmail.com + + + + diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index caa9f1b3389..d7b0d731b9d 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -699,7 +699,7 @@ SELECT * WHERE proname LIKE 'bytea%'; The function - (part of the module>) executes + (part of the module) executes a remote query. It is declared to return record since it might be used for any kind of query. The actual column set must be specified in the calling query so diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 7e39c0db75a..c0c543e7a4b 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -314,8 +314,7 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows Specifies a connection string to be used for the standby server to connect with the primary. This string is in the format - accepted by the libpq PQconnectdb function, - described in . If any option is + described in . If any option is unspecified in this string, then the corresponding environment variable (see ) is checked. If the environment variable is not set either, then diff --git a/doc/src/sgml/ref/create_foreign_data_wrapper.sgml b/doc/src/sgml/ref/create_foreign_data_wrapper.sgml index 804fb47c68f..d9936e81659 100644 --- a/doc/src/sgml/ref/create_foreign_data_wrapper.sgml +++ b/doc/src/sgml/ref/create_foreign_data_wrapper.sgml @@ -121,14 +121,6 @@ CREATE FOREIGN DATA WRAPPER name There is no support for updating a foreign table, and optimization of queries is primitive (and mostly left to the wrapper, too). - - - There is one built-in foreign-data wrapper validator function - provided: - postgresql_fdw_validator, which accepts - options corresponding to libpq connection - parameters. - diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index d0b96db055e..0a6ac29d4d9 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -32,7 +32,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name Description - CREATE FOREIGN TABLE will create a new foreign table + CREATE FOREIGN TABLE creates a new foreign table in the current database. The table will be owned by the user issuing the command. @@ -54,8 +54,9 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name - To be able to create a table, you must have USAGE - privilege on all column types. + To be able to create a foreign table, you must have USAGE + privilege on the foreign server, as well as USAGE + privilege on all column types used in the table. @@ -134,7 +135,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name server_name - The name of an existing server for the foreign table. + The name of an existing foreign server to use for the foreign table. For details on defining a server, see . @@ -164,7 +165,8 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name Examples - Create foreign table films with film_server: + Create foreign table films, which will be accessed through + the server film_server: CREATE FOREIGN TABLE films ( diff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgml index 60744b2d463..3e6127a3898 100644 --- a/doc/src/sgml/ref/create_server.sgml +++ b/doc/src/sgml/ref/create_server.sgml @@ -110,11 +110,10 @@ CREATE SERVER server_name [ TYPE '< Notes - When using the dblink module - (see ), the foreign server name can be used + When using the module, + a foreign server's name can be used as an argument of the - function to indicate the connection parameters. See also there for - more examples. It is necessary to have + function to indicate the connection parameters. It is necessary to have the USAGE privilege on the foreign server to be able to use it in this way. @@ -124,19 +123,13 @@ CREATE SERVER server_name [ TYPE '< Examples - Create a server foo that uses the built-in foreign-data - wrapper default: + Create a server myserver that uses the + foreign-data wrapper postgres_fdw: -CREATE SERVER foo FOREIGN DATA WRAPPER "default"; +CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432'); + See for more details. - - - Create a server myserver that uses the - foreign-data wrapper pgsql: - -CREATE SERVER myserver FOREIGN DATA WRAPPER pgsql OPTIONS (host 'foo', dbname 'foodb', port '5432'); - @@ -154,6 +147,7 @@ CREATE SERVER myserver FOREIGN DATA WRAPPER pgsql OPTIONS (host 'foo', dbname 'f + diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index fb81af425da..f42d6595187 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -357,10 +357,9 @@ GRANT role_name [, ...] TO - For servers, this privilege enables the grantee to create, - alter, and drop his own user's user mappings associated with - that server. Also, it enables the grantee to query the options - of the server and associated user mappings. + For servers, this privilege enables the grantee to create foreign + tables using the server, and also to create, alter, or drop his own + user's user mappings associated with that server. diff --git a/doc/src/sgml/ref/pg_isready.sgml b/doc/src/sgml/ref/pg_isready.sgml index ff80a78305e..407d73ba5bd 100644 --- a/doc/src/sgml/ref/pg_isready.sgml +++ b/doc/src/sgml/ref/pg_isready.sgml @@ -54,7 +54,8 @@ PostgreSQL documentation with a valid URI prefix (postgresql:// or postgres://), it is treated as a - conninfo string. See for more information. + conninfo string. See for more information. diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 233f747163e..465d3a1882d 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -120,7 +120,8 @@ PostgreSQL documentation with a valid URI prefix (postgresql:// or postgres://), it is treated as a - conninfo string. See for more information. + conninfo string. See for more information. @@ -608,9 +609,9 @@ PostgreSQL documentation $ psql "service=myservice sslmode=require" $ psql postgresql://dbmaster:5433/mydb?sslmode=require - This way you can also use LDAP for connection parameter lookup as - described in . - See for more information on all the + This way you can also use LDAP for connection + parameter lookup as described in . + See for more information on all the available connection options. diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index d76e1c57105..7dada6b67f9 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1751,7 +1751,7 @@ pg_dumpall -p 5432 | psql -d postgres -p 5433 (). The TCP client must connect using sslmode=verify-ca or verify-full and have the appropriate root certificate - file installed (). + file installed (). -- cgit v1.2.3