Postgres Pro Enterprise : Документация: 15: F.65. postgres

F.65. postgres_fdw
Пред.	Наверх	Приложение F. Дополнительные модули и расширения, поставляемые в `postgrespro-ent-15-contrib`	Начало	След.

F.65. postgres_fdw

F.65.1. Параметры обёртки для postgres_fdw
F.65.2. Функции
F.65.3. Управление соединением
F.65.4. Управление транзакциями
F.65.5. Оптимизация удалённых запросов
F.65.6. Окружение удалённого выполнения запросов
F.65.7. Совместимость с разными версиями
F.65.8. Параметры конфигурации
F.65.9. Примеры
F.65.10. Автор

Модуль postgres_fdw предоставляет обёртку сторонних данных postgres_fdw, используя которую можно обращаться к данным, находящимся на внешних серверах Postgres Pro.

Функциональность этого модуля во многом пересекается с функциональностью старого модуля dblink. Однако postgres_fdw предоставляет более прозрачный и стандартизированный синтаксис для обращения к удалённым таблицам и во многих случаях даёт лучшую производительность.

Чтобы подготовиться к обращению к удалённым данным через postgres_fdw:

Установите расширение postgres_fdw с помощью команды CREATE EXTENSION.
Создайте объект стороннего сервера, используя CREATE SERVER, который будет представлять удалённую базу данных, к которой вы хотите подключаться. Укажите свойства подключения, кроме user и password, в параметрах объекта сервера.
Создайте сопоставление пользователей, используя CREATE USER MAPPING, для каждого пользователя базы, которому нужен доступ к удалённому серверу. Укажите имя и пароль удалённого пользователя в параметрах user и password сопоставления.
Создайте стороннюю таблицу, используя CREATE FOREIGN TABLE или IMPORT FOREIGN SCHEMA, для каждой удалённой таблицы, к которой вы хотите обращаться. Столбцы сторонней таблицы должны соответствовать столбцам целевой удалённой таблицы. Однако вы можете использовать локально имена таблиц и/или столбцов, отличные от удалённых, если укажете корректные удалённые имена в параметрах объекта сторонней таблицы.

После этого для обращения к данным, хранящимся в нижележащей удалённой таблице, вам нужно только выполнять SELECT. Вы также можете изменять данные в удалённой таблице, выполняя INSERT, UPDATE, DELETE, COPY или TRUNCATE. (Разумеется, удалённый пользователь, указанный в сопоставлении, должен иметь необходимые права для этого.)

Обратите внимание, что в запросах на чтение или изменение данных в удалённых таблицах указание ONLY в командах SELECT, UPDATE, DELETE и TRUNCATE не действует.

Заметьте, что в настоящее время в postgres_fdw не поддерживаются операторы INSERT с предложением ON CONFLICT DO UPDATE. Однако предложение ON CONFLICT DO NOTHING поддерживается, при отсутствии указания для выбора уникального индекса. Заметьте также, что postgres_fdw поддерживает перемещение строк, вызванное командами UPDATE, выполняемыми для секционированных таблиц. Однако в настоящее время невозможно выполнить изменение, при котором удалённая секция, выбранная для добавления перемещаемой строки, также является целевой секцией для UPDATE и должна модифицироваться в другой части той же команды.

Обычно рекомендуется объявлять столбцы сторонней таблицы точно с теми же типами данных и правилами сортировки, если они применимы, как у целевых столбцов удалённой таблицы. Хотя postgres_fdw в настоящее время довольно лоялен к преобразованиям типов данных при необходимости, но когда типы или правила сортировки не совпадают, возможны неожиданные семантические аномалии, вследствие того, что удалённый сервер будет обрабатывать условия запроса не так, как локальный сервер.

Заметьте, что сторонняя таблица может быть объявлена с меньшим количеством или с другим порядком столбцов, чем в нижележащей удалённой таблице. Сопоставление столбцов удалённой таблицы осуществляется по имени, а не по позиции.

F.65.1. Параметры обёртки для postgres_fdw

F.65.1.1. Параметры подключения

Для стороннего сервера, настраиваемого с использованием обёртки сторонних данных postgres_fdw, можно задать те же параметры, что принимает libpq в строках подключения, как описано в Подразделе 36.1.2, за исключением следующих параметров, которые не допускаются или обрабатывается по-другому:

user, password и sslpassword (их следует задавать в сопоставлениях пользователей или в файле описания служб)
client_encoding (автоматически принимается равной локальной кодировке сервера)
application_name может задаваться как параметр подключения и/или значение postgres_fdw.application_name. Если задаётся и там, и там, postgres_fdw.application_name переопределяет значение параметра подключения. В отличие от libpq, postgres_fdw допускает использование спецпоследовательностей в application_name. За дополнительной информацией обратитесь к postgres_fdw.application_name.
fallback_application_name (всегда postgres_fdw)
sslkey и sslcert могут задаваться в свойствах соединения и/или сопоставления пользователя. Если они задаются и там, и там, приоритет имеет свойство сопоставления пользователя.

Создавать и изменять сопоставления пользователей, устанавливающие параметр sslcert или sslkey, могут только суперпользователи.

Подключаться к сторонним серверам без аутентификации по паролю могут только суперпользователи, поэтому в сопоставлениях для обычных пользователей всегда нужно задавать пароль (password).

Суперпользователь может отключить проверку пароля на уровне сопоставления пользователя, установив параметр password_required 'false', например:

ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

Чтобы недоверенные пользователи не могли злоупотреблять правами пользователя, от имени которого работает сервер postgres, и повысить свои привилегии, изменять это свойство в сопоставлении пользователя разрешено только суперпользователю.

Позаботьтесь о том, чтобы через установленное сопоставление пользователь не мог подключиться к другой базе с правами суперпользователя, как в случаях CVE-2007-3278 и CVE-2007-6601. Не задавайте параметр password_required=false для роли public. Помните, что сопоставляемый пользователь имеет возможность использовать любые клиентские сертификаты, а также записи в файлах .pgpass, .pg_service.conf и т. п. в домашнем каталоге системного пользователя, от имени которого работает сервер postgres. Они также могут использовать отношения доверия, установленные режимами аутентификации, например peer или ident.

F.65.1.2. Параметры имени объекта

Эти параметры позволяют управлять тем, как на удалённый сервер Postgres Pro будут передаваться имена, фигурирующие в операторах SQL. Данные параметры нужны, когда сторонняя таблица создаётся с именами, отличными от имён удалённой таблицы.

schema_name (string): Этот параметр, который может задаваться для сторонней таблицы, указывает имя схемы для обращения к этой таблице на удалённом сервере. Если данный параметр опускается, применяется схема сторонней таблицы.
table_name (string): Этот параметр, который может задаваться для сторонней таблицы, указывает имя таблицы для обращения к этой таблице на удалённом сервере. Если данный параметр опускается, применяется имя сторонней таблицы.
column_name (string): Этот параметр, который может задаваться для столбца сторонней таблицы, указывает имя столбца для обращения к этому столбцу на удалённом сервере. Если данный параметр опускается, применяется исходное имя столбца.

F.65.1.3. Параметры оценки стоимости

Модуль postgres_fdw получает удалённые данные, выполняя запросы на удалённых серверах, поэтому в идеале ожидаемая стоимость сканирования сторонней таблицы должна равняться стоимости выполнения на удалённом сервере плюс издержки сетевого взаимодействия. Самый надёжный способ получить такие оценки — узнать стоимость у удалённого сервера и добавить некоторую надбавку — но для простых запросов может быть невыгодно передавать дополнительный запрос, только чтобы получить оценку стоимости. Поэтому postgres_fdw предоставляет следующие параметры, позволяющие управлять вычислением оценки стоимости:

use_remote_estimate (boolean): Этот параметр, который может задаваться для сторонней таблицы или для стороннего сервера, определяет, будет ли postgres_fdw выполнять удалённо команды EXPLAIN для получения оценок стоимости. Параметр, заданный для сторонней таблицы, переопределяет параметр сервера, но только для данной таблицы. Значение по умолчанию — false (выкл.).
fdw_startup_cost (floating point): Этот параметр, который может задаваться для стороннего сервера, устанавливает значение с плавающей точкой, добавляемое к оценке стоимости запуска для любого сканирования сторонней таблицы на этом сервере. Он выражает дополнительные издержки на установление подключения, разбор и планирование запроса на удалённой стороне и т. д. Значение по умолчанию — 100.
fdw_tuple_cost (floating point): Этот параметр, который может задаваться для стороннего сервера, устанавливает значение с плавающей точкой, выражающее дополнительную цену чтения одного кортежа из сторонней таблицы на этом сервере. Это число можно увеличить или уменьшить, отражая меньшую или большую фактическую скорость сетевого взаимодействия с удалённым сервером. Значение по умолчанию — 0.01.

Когда поведение use_remote_estimate включено, postgres_fdw получает количество строк и оценку стоимости с удалённого сервера, а затем добавляет к оценке стоимости fdw_startup_cost и fdw_tuple_cost. Когда поведение use_remote_estimate отключено, postgres_fdw рассчитывает число строк и оценку стоимости локально, а затем так же добавляет к этой оценке fdw_startup_cost и fdw_tuple_cost. Локальная оценка может быть точной только при условии наличия локальной копии статистики удалённых таблиц. Обновить эту статистику для сторонней таблицы можно с помощью команды ANALYZE; при этом удалённая таблица будет просканирована, и по её содержимому будут вычислена и сохранена статистика как для локальной таблицы. Локальное хранение статистики может быть полезно для сокращения издержек планирования для удалённой таблицы — но если удалённая таблица меняется часто, локальная статистика будет быстро устаревать.

F.65.1.4. Параметры удалённого выполнения

По умолчанию ограничения WHERE, содержащие встроенные операторы и функции, обрабатываются на удалённом сервере, а ограничения, содержащие вызовы не встроенных функций, проверяются локально после получения строк. Если же расширенные функции доступны на удалённом сервере и можно рассчитывать, что они дадут те же результаты, что и локально, производительность можно увеличить, передавая и такие предложения WHERE для удалённого выполнения. Этим поведением позволяет управлять следующий параметр:

extensions (string)

В этом параметре задаётся список имён расширений Postgres Pro через запятую, которые установлены и имеют совместимые версии и на локальном, и на удалённом сервере. Относящиеся к перечисленным расширениям и при этом постоянные (immutable) функции и операторы могут передаваться на выполнение удалённому серверу. Этот параметр можно задать только для стороннего сервера, но не для таблицы.

При использовании параметра extensions пользователь сам отвечает за то, чтобы перечисленные расширения существовали и их поведение было одинаковым на локальном и удалённом сервере. В противном случае удалённые запросы могут выдавать ошибки или неожиданные результаты.

fetch_size (integer)

Этот параметр определяет, сколько строк должна получать postgres_fdw в одной операции выборки. Его можно задать для сторонней таблицы или стороннего сервера. Значение по умолчанию — 100 строк.

batch_size (integer)

Этот параметр определяет, сколько строк должна вставлять postgres_fdw в одной операции вставки. Его можно задать для сторонней таблицы или стороннего сервера. Значение по умолчанию — 1 строка.

Обратите внимание, что фактическое количество строк, вставляемых postgres_fdw в одной порции, зависит от количества столбцов и значения batch_size. Порция передаётся в одном запросе, а протокол libpq, используя который postgres_fdw подключается к удалённому серверу, ограничивает количество параметров одного запроса числом 65535. Когда «число столбцов» * batch_size превышает этот предел, значение batch_size корректируется во избежание ошибки.

F.65.1.5. Параметры асинхронного выполнения

Обёртка postgres_fdw поддерживает асинхронное выполнение, когда для улучшения производительности несколько частей узла Append выполняются одновременно, а не последовательно. Асинхронным выполнением управляет следующий параметр:

async_capable (boolean)

Этот параметр определяет, будет ли postgres_fdw допускать одновременное сканирование сторонних таблиц для асинхронного выполнения. Его можно задать для сторонней таблицы или для стороннего сервера. Параметр, определённый на уровне таблицы, переопределяет параметр уровня сервера. Значение по умолчанию — false.

Чтобы получаемые со стороннего сервера данные были согласованными, postgres_fdw открывает только одно соединение для каждого стороннего сервера и выполняет все запросы к нему последовательно, даже если в запросе задействуются несколько сторонних таблиц (несколько подключений будет установлено, только если для этих таблиц используются разные сопоставления пользователей). Поэтому возможно, что производительность увеличится, если отключить этот параметр и тем самым исключить издержки, связанные с асинхронным выполнением запросов.

Асинхронное выполнение используется, даже когда узел Append содержит как подпланы, выполняемые синхронно, так и подпланы, выполняемые асинхронно. В таких случаях, если postgres_fdw обрабатывает асинхронные подпланы, кортежи этих подпланов не возвращаются до тех пор, пока хотя бы один синхронный подплан не вернёт все кортежи, поскольку этот подплан выполняется, пока асинхронные подпланы ожидают результатов асинхронных запросов к сторонним серверам. Такое поведение может измениться в будущих версиях.

F.65.1.6. Параметры управления транзакциями

Как описано в разделе «Управление транзакциями», для управления транзакциями и подтранзакциями postgres_fdw создаёт соответствующие удалённые транзакции и подтранзакции. Если текущей локальной транзакции соответствует несколько удалённых транзакций, по умолчанию postgres_fdw фиксирует эти удалённые транзакции последовательно, когда фиксируется локальная транзакция. Удалённые подтранзакции postgres_fdw по умолчанию фиксирует так же последовательно. Ускорить эту процедуру позволяет следующий параметр:

parallel_commit (boolean)

Этот параметр определяет, будет ли postgres_fdw при фиксировании локальной транзакции фиксировать все соответствующие ей удалённые транзакции одновременно. Действие этого параметра также распространяется на подтранзакции. Его можно определить только для удалённого сервера, а не на уровне таблицы. Значение по умолчанию — false.

Если текущая локальная транзакция задействует несколько сторонних серверов, для которых этот параметр включён, удалённые транзакции на них фиксируются одновременно, когда фиксируется локальная транзакция.

Включение этого параметра может негативно повлиять на производительность стороннего сервера с большим количеством удалённых транзакций при фиксации локальной транзакции.

F.65.1.7. Параметры изменения данных

По умолчанию все сторонние таблицы, доступные через postgres_fdw, считаются допускающими изменения. Это можно переопределить с помощью следующего параметра:

updatable (boolean)

Этот параметр определяет, будет ли postgres_fdw допускать изменения в сторонних таблицах посредством команд INSERT, UPDATE и DELETE. Его можно задать для сторонней таблицы или для стороннего сервера. Параметр, определённый на уровне таблицы, переопределяет параметр уровня сервера. Значение по умолчанию — true (изменения разрешены).

Конечно, если удалённая таблица на самом деле не допускает изменения, всё равно произойдёт ошибка. Использование этого параметра прежде всего позволяет выдать ошибку локально, не обращаясь к удалённому серверу. Заметьте, однако, что представление information_schema будет показывать, что определённая сторонняя таблица postgres_fdw является изменяемой (или нет), согласно значению данного параметра, не проверяя это на удалённом сервере.

F.65.1.8. Параметры операции опустошения таблиц

По умолчанию все сторонние таблицы, доступные через postgres_fdw, считаются допускающими опустошение. Это можно переопределить с помощью следующего параметра:

truncatable (boolean)

Этот параметр определяет, будет ли postgres_fdw допускать опустошение сторонних таблиц посредством команды TRUNCATE. Его можно задать для сторонней таблицы или для стороннего сервера. Параметр, определённый на уровне таблицы, переопределяет параметр уровня сервера. Значение по умолчанию — true (опустошение разрешено).

Конечно, если удалённая таблица на самом деле не допускает опустошение, всё равно произойдёт ошибка. Использование этого параметра прежде всего позволяет выдать ошибку локально, не обращаясь к удалённому серверу.

F.65.1.9. Параметры импорта

Обёртка postgres_fdw позволяет импортировать определения сторонних таблиц с применением команды IMPORT FOREIGN SCHEMA. Эта команда создаёт на локальном сервере определения сторонних таблиц, соответствующие таблицам или представлениям, существующим на удалённом сервере. Если импортируемые удалённые таблицы содержат столбцы пользовательских типов данных, на локальном сервере должны быть совместимые типы с теми же именами.

Поведение процедуры импорта можно настроить следующими параметрами (задаваемыми в команде IMPORT FOREIGN SCHEMA):

import_collate (boolean)

Этот параметр устанавливает, будут ли в определения сторонних таблиц, импортируемых с внешнего сервера, включаться характеристики столбцов COLLATE. По умолчанию они включаются. Вам может потребоваться отключить его, если на удалённом сервере набор имён правил сортировки отличается от локального, что скорее всего будет иметь место, если серверы работают в разных операционных системах. Тем не менее отключение чревато тем, что правила сортировки в столбцах импортируемых таблиц не совпадут с правилами для нижележащих данных, что приведёт к аномальному поведению запросов.

Даже когда этот параметр равен true, импортировать столбцы с правилом сортировки, выбираемым на удалённом сервере по умолчанию, рискованно. Столбцы импортируются со свойством COLLATE "default", а в результате будет применяться правило сортировки, выбираемое по умолчанию на локальном сервере, которое может отличаться от выбираемого на удалённом.

import_default (boolean)

Этот параметр устанавливает, будут ли в определения сторонних таблиц, импортируемых с внешнего сервера, включаться заданные для столбцов выражения DEFAULT. По умолчанию они не включаются. Если вы включите этот параметр, остерегайтесь выражений по умолчанию, которые могут вычисляться на локальном сервере не так, как на удалённом; например, частый источник проблем — nextval(). Если в импортируемом выражении используются функции или операторы, несуществующие локально, команда IMPORT в целом выдаст сбой.

import_generated (boolean)

Этот параметр определяет, включаются ли выражения генерируемых столбцов (GENERATED) в определения сторонних таблиц, импортируемых со стороннего сервера. Значение по умолчанию — true. Выполнение IMPORT завершится ошибкой, если в импортируемом генерирующем выражении используются функции или операторы, не существующие локально.

import_not_null (boolean)

Этот параметр устанавливает, будут ли в определения сторонних таблиц, импортируемых с внешнего сервера, включаться ограничения столбцов NOT NULL. По умолчанию они включаются.

Заметьте, что никакие другие ограничения, кроме NOT NULL, из удалённых таблиц импортироваться не будут. Хотя Postgres Pro поддерживает ограничения-проверки для сторонних таблиц, никаких средств для автоматического импорта их нет из-за риска различного вычисления выражения ограничения на локальном и удалённом серверах. Любая такая несогласованность в поведении ограничений-проверок могла бы привести к сложно выявляемым ошибкам в оптимизации запросов. Поэтому, если вы хотите импортировать ограничения-проверки, вы должны сделать это вручную и должны внимательно проверить семантику каждого. Более подробно интерпретация ограничений-проверок для сторонних таблиц описана в CREATE FOREIGN TABLE.

Таблицы или сторонние таблицы, являющиеся секциями некоторой другой таблицы, импортируются, только когда они явным образом указаны в предложении LIMIT TO. В противном случае команда IMPORT FOREIGN SCHEMA автоматически исключает их. Так как все данные могут быть доступны через корневую таблицу в иерархии секционирования, должно быть достаточно импортировать только секционированные таблицы, чтобы получить доступ ко всем данным, не создавая дополнительные объекты.

F.65.1.10. Параметры управления соединением

По умолчанию все соединения, устанавливаемые postgres_fdw со сторонними серверами, остаются открытыми в текущем сеансе для повторного использования.

keep_connections (boolean): Этот параметр определяет, будет ли postgres_fdw оставлять открытыми соединения со сторонними серверами, чтобы последующие запросы также могли использовать эти соединения. Этот параметр можно задать только для стороннего сервера. Значение по умолчанию — on. Если задано значение off, все соединения с этим сторонним сервером будут закрываться в конце каждой транзакции.

F.65.2. Функции

postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record

Эта функция возвращает имена всех сторонних серверов, с которыми postgres_fdw установил соединение в локальном сеансе. Она также возвращает признак действительного соединения. Этот признак — false, если это соединение со сторонним сервером использовалось в текущей локальной транзакции, но объект стороннего сервера или сопоставление пользователя были изменены или удалены (обратите внимание, что если объект стороннего сервера удалён, вместо имени сервера в записи этого соединения будет NULL). Такое недействующее соединение будет закрыто в конце транзакции. Для действительных соединений этот признак установлен. Если открытых соединений нет, эта функция не возвращает никакие записи. Пример использования функции:

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
 server_name | valid
-------------+-------
 loopback1   | t
 loopback2   | f

postgres_fdw_disconnect(server_name text) returns boolean

Эта функция закрывает открытые соединения, установленные postgres_fdw с указанным сторонним сервером в локальном сеансе. Обратите внимание, что с одним сервером может быть установлено несколько соединений, использующих различные сопоставления пользователей. Если какие-либо соединения используются в текущей локальной транзакции, они не будут разорваны, о чём сообщат соответствующие предупреждения. Если функция закрывает хотя бы одно соединение, она возвращает true; в противном случае false. Если сторонний сервер с указанным именем не найден, выводится сообщение об ошибке. Пример использования функции:

postgres=# SELECT postgres_fdw_disconnect('loopback1');
 postgres_fdw_disconnect
-------------------------
 t

postgres_fdw_disconnect_all() returns boolean

Эта функция закрывает все открытые соединения, установленные postgres_fdw со сторонними серверами в локальном сеансе. Если какие-либо соединения используются в текущей локальной транзакции, они не будут разорваны, о чем сообщат соответствующие предупреждения. Если функция закрывает хотя бы одно соединение, она возвращает true; в противном случае false. Пример использования функции:

postgres=# SELECT postgres_fdw_disconnect_all();
 postgres_fdw_disconnect_all
-----------------------------
 t

F.65.3. Управление соединением

Модуль postgres_fdw устанавливает соединение со сторонним сервером при первом запросе, в котором участвует сторонняя таблица, связанная со сторонним сервером. По умолчанию это соединение сохраняется и повторно используется для последующих запросов в том же сеансе. Этим поведением управляет параметр стороннего сервера keep_connections. Если для обращения к стороннему серверу задействуются разные пользователи (сопоставления пользователей), отдельное соединение устанавливается для каждого сопоставления пользователей.

При изменении или удалении определения стороннего сервера или сопоставления пользователей, связанные с ними соединения закрываются. Но обратите внимание, что если какие-либо соединения используются в текущей локальной транзакции, то они сохраняются до конца транзакции. Если для запросов, задействующих сторонние таблицы, понадобятся уже закрытые соединения, они будут открытыми заново.

По умолчанию установленное соединение сохраняется до конца локального или соответствующего удалённого сеанса. Чтобы соединения закрывались явно, можно отключить параметр keep_connections или использовать функции postgres_fdw_disconnect и postgres_fdw_disconnect_all. В частности, эти функции полезны тем, что позволяют освободить подключения на стороннем сервере, закрыв уже не нужные.

F.65.4. Управление транзакциями

В процессе выполнения запроса, в котором участвуют какие-либо удалённые таблицы на стороннем сервере, postgres_fdw открывает транзакцию на удалённом сервере, если такая транзакция ещё не была открыта для текущей локальной транзакции. Эта удалённая транзакция фиксируется или прерывается, когда фиксируется или прерывается локальная транзакция. Подобным образом реализуется и управление точками сохранения.

Для удалённой транзакции выбирается режим изоляции SERIALIZABLE, когда локальная транзакция открыта в режиме SERIALIZABLE; в противном случае применяется режим REPEATABLE READ. Этот выбор гарантирует, что если запрос сканирует несколько таблиц на удалённом сервере, он будет получать согласованные данные одного снимка для всех сканирований. Как следствие, последовательные запросы в одной транзакции будут видеть одни данные удалённого сервера, даже если на нём параллельно происходят изменения, вызванные другими действиями. Это поведение ожидаемо для локальной транзакции в режимах SERIALIZABLE и REPEATABLE READ, но для локальной транзакции в режиме READ COMMITTED оно может быть неожиданным. В будущих выпусках Postgres Pro эти правила могут быть изменены.

Заметьте, что подготовку удалённой транзакции для двухфазной фиксации postgres_fdw в настоящее время не поддерживает.

F.65.5. Оптимизация удалённых запросов

Обёртка postgres_fdw пытается оптимизировать удалённые запросы, уменьшая объём обмена данными со сторонними серверами. Для этого она может передавать на выполнение удалённому серверу предложения WHERE и не получать столбцы таблицы, не требующиеся для текущего запроса. Чтобы уменьшить риск некорректного выполнения запросов, предложения WHERE передаются удалённому серверу, только если в них используются типы данных, операторы и функции, встроенные в ядро или относящиеся к расширениям, перечисленным в параметре extensions. Операторы и функции в таких предложениях также должны быть постоянными (IMMUTABLE). Для запросов UPDATE или DELETE обёртка postgres_fdw пытается оптимизировать выполнение, передавая весь запрос на удалённый сервер, если в запросе нет предложения WHERE, которое нельзя было бы передать, не выполняется локальное соединение, в целевой таблице отсутствуют хранимые генерируемые столбцы и локальные триггеры BEFORE/AFTER уровня строки, а в родительских представлениях нет ограничения CHECK OPTION. Кроме того, в запросах UPDATE выражения, присваиваемые целевым столбцам, должны задействовать только встроенные типы данных и постоянные (IMMUTABLE) операторы и функции, чтобы уменьшить риск неверного выполнения запроса.

Когда обёртка postgres_fdw обнаруживает соединение сторонних таблиц на одном стороннем сервере, она передаёт всё соединение этому серверу, если только по какой-то причине не решит, что будет эффективнее выбирать строки из каждой таблицы по отдельности, или сопоставляемые при обращении к таблицам пользователи оказываются разными. При передаче предложений JOIN принимаются те же меры предосторожности, что были описаны выше для предложений WHERE.

Запрос, фактически отправляемый удалённому серверу для выполнения, можно изучить с помощью команды EXPLAIN VERBOSE.

F.65.6. Окружение удалённого выполнения запросов

В удалённых сеансах, установленных обёрткой postgres_fdw, в параметре search_path задаётся только pg_catalog, так что без указания схемы видны только встроенные объекты. Это не проблема для запросов, которые генерирует сама postgres_fdw, так как она всегда добавляет такие указания. Однако это может быть опасно для функций, которые выполняются на удалённом сервере при срабатывании триггеров или правил для удалённых таблиц. Например, если удалённая таблица на самом деле представляет собой представление, любые функции, используемые в этом представлении, будут выполняться с таким ограниченным путём поиска. Поэтому рекомендуется в таких функциях дополнять схемой все имена либо добавлять параметры SET search_path (см. CREATE FUNCTION) в определения таких функций, чтобы установить ожидаемый ими путь поиска в окружении.

Обёртка postgres_fdw подобным образом устанавливает для удалённого сеанса различные параметры:

TimeZone — UTC
DateStyle — ISO
IntervalStyle — postgres
extra_float_digits принимает значение 3 для удалённых серверов версии 9.0 и новее либо 2 для более старых версий

С ними проблемы менее вероятны, чем с search_path, но если они возникнут, их можно урегулировать, установив нужные значения с помощью SET.

Это поведение не рекомендуется переопределять, устанавливая значения этих параметров на уровне сеанса; это скорее всего приведёт к поломке postgres_fdw.

F.65.7. Совместимость с разными версиями

Модуль postgres_fdw может применяться с удалёнными серверами версий, начиная с PostgreSQL 8.3. Возможность работы в режиме «только для чтения» доступна, начиная с 8.1. Однако при этом есть ограничение, вызванное тем, что postgres_fdw полагает, что постоянные встроенные функции и операторы могут безопасно передаваться на удалённый сервер для выполнения, если они фигурируют в предложении WHERE для сторонней таблицы. Таким образом, встроенная функция, добавленная в более новой версии, чем на удалённом сервере, может быть отправлена на выполнение, что в результате приведёт к ошибке «функция не существует» или подобной. Отказы такого типа можно предотвратить, переписав запрос, например, поместив ссылку на стороннюю таблицу во вложенный SELECT с OFFSET 0 в качестве защиты от оптимизации, и применив проблематичную функцию или оператор снаружи этого вложенного SELECT.

F.65.8. Параметры конфигурации

postgres_fdw.application_name (string)

Задаёт значение конфигурационного параметра application_name, используемого при соединении postgres_fdw со сторонним сервером. Этот параметр переопределяет свойство application_name, заданное для объекта стороннего сервера. Обратите внимание, что изменение этого параметра не затронет существующие подключение, а повлияет только на вновь устанавливаемые.

В postgres_fdw.application_name можно задать любую строку произвольной длины, содержащую не только ASCII-символы. Однако обратите внимание, что когда она передаётся стороннему серверу в качестве параметра application_name, она усекается до NAMEDATALEN символов, а символы не из набора ASCII заменяются вопросительными знаками (?). За дополнительной информацией обратитесь к application_name.

Символы % в этой строке обозначают начало спецпоследовательностей, заменяемых описанной далее информацией. Неизвестные управляющие последовательности игнорируются. Все остальные символы переносятся в имя приложения без изменений. Обратите внимание, что использовать знаки плюс/минус и число между знаком % и спецсимволом (чтобы обозначить выравнивание или дополнение) нельзя.

Спецпоследовательность	Назначение
`%a`	Имя приложения на локальном сервере
`%c`	ID сеанса на локальном сервере (подробнее см. log_line_prefix)
`%C`	Имя кластера на локальном сервере (подробнее см. cluster_name)
`%u`	Имя пользователя на локальном сервере
`%d`	Имя базы данных на локальном сервере
`%p`	PID обслуживающего процесса на локальном сервере
`%%`	Буквально %

Например, если пользователь local_user устанавливает подключение из базы данных local_db к базе данных foreign_db от имени foreign_user, значение 'db=%d, user=%u' заменяется на 'db=local_db, user=local_user'.

postgres_fdw.read_only_transactions (boolean)

Включает запросы только на чтение к сторонним таблицам в рамках транзакций на чтение и запись. Если задано значение on, все удалённые транзакции, которые выполняет расширение, создаются как READ ONLY.

F.65.9. Примеры

Ниже приведён пример создания сторонней таблицы с применением postgres_fdw. Сначала установите расширение:

CREATE EXTENSION postgres_fdw;

Затем создайте сторонний сервер с помощью команды CREATE SERVER. В данном примере мы хотим подключиться к серверу Postgres Pro, работающему по адресу 192.83.123.89, порт 5432. База данных, к которой устанавливается подключение, на удалённом сервере называется foreign_db:

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

Для определения роли, которая будет задействована на удалённом сервере, с помощью CREATE USER MAPPING задаётся сопоставление пользователей:

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

Теперь можно создать стороннюю таблицу, применив команду CREATE FOREIGN TABLE. В этом примере мы хотим обратиться к таблице some_schema.some_table на удалённом сервере. Локальным именем этой таблицы будет foreign_table:

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

Важно, чтобы типы данных и другие свойства столбцов, объявленных в CREATE FOREIGN TABLE, соответствовали фактической удалённой таблице. Также должны соответствовать имена столбцов, если только вы не добавите параметры column_name для отдельных столбцов, задающие их реальные имена в удалённой таблице. Во многих случаях использовать IMPORT FOREIGN SCHEMA предпочтительнее, чем конструировать определения сторонних таблиц вручную.

F.65.10. Автор

Шигеру Ханада <shigeru.hanada@gmail.com>

Пред.	Наверх	След.
F.64. plantuner	Начало	F.66. ptrack

F.65. postgres_fdw
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-ent-15-contrib`	Home	Next

F.65. postgres_fdw

F.65.1. FDW Options of postgres_fdw
F.65.2. Functions
F.65.3. Connection Management
F.65.4. Transaction Management
F.65.5. Remote Query Optimization
F.65.6. Remote Query Execution Environment
F.65.7. Cross-Version Compatibility
F.65.8. Configuration Parameters
F.65.9. Examples
F.65.10. Author

The postgres_fdw module provides the foreign-data wrapper postgres_fdw, which can be used to access data stored in external Postgres Pro servers.

The functionality provided by this module overlaps substantially with the functionality of the older dblink 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 EXTENSION.
Create a foreign server object, using CREATE SERVER, 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 CREATE USER MAPPING, 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 CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA, 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. You can also modify the remote table using INSERT, UPDATE, DELETE, COPY, or TRUNCATE. (Of course, the remote user you have specified in your user mapping must have privileges to do these things.)

Note that the ONLY option specified in SELECT, UPDATE, DELETE or TRUNCATE has no effect when accessing or modifying the remote table.

Note that postgres_fdw currently lacks support for INSERT statements with an ON CONFLICT DO UPDATE clause. However, the ON CONFLICT DO NOTHING clause is supported, provided a unique index inference specification is omitted. Note also that postgres_fdw supports row movement invoked by UPDATE 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 UPDATE target partition that will be updated elsewhere in the same command.

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 query conditions 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.

F.65.1. FDW Options of postgres_fdw

F.65.1.1. 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 Section 36.1.2, except that these options are not allowed or have special handling:

user, password and sslpassword (specify these in a user mapping, instead, or use a service file)
client_encoding (this is automatically set from the local server encoding)
application_name - this may appear in either or both a connection and postgres_fdw.application_name. If both are present, postgres_fdw.application_name overrides the connection setting. Unlike libpq, postgres_fdw allows application_name to include “escape sequences”. See postgres_fdw.application_name for details.
fallback_application_name (always set to postgres_fdw)
sslkey and 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.

Only superusers may create or modify user mappings with the sslcert or sslkey settings.

Only superusers may connect to foreign servers without password authentication, so always specify the password option for user mappings belonging to non-superusers.

A superuser may override this check on a per-user-mapping basis by setting the user mapping option password_required 'false', e.g.,

ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

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.

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 password_required=false on the public role. Keep in mind that the mapped user can potentially use any client certificates, .pgpass, .pg_service.conf 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 peer or ident authentication.

F.65.1.2. Object Name Options

These options can be used to control the names used in SQL statements sent to the remote Postgres Pro server. These options are needed when a foreign table is created with names different from the underlying remote table's names.

schema_name (string): 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 (string): 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 (string): 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.

F.65.1.3. 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 (boolean): 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 (floating point): 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 100.
fdw_tuple_cost (floating point): 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 0.01.

When use_remote_estimate is true, postgres_fdw obtains row count 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 row count 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 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.

F.65.1.4. Remote Execution Options

By default, only WHERE 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 WHERE clauses for remote execution. This behavior can be controlled using the following option:

extensions (string)

This option is a comma-separated list of names of Postgres Pro 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.

When using the extensions option, 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.

fetch_size (integer)

This option specifies the number of rows postgres_fdw 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 100.

batch_size (integer)

This option specifies the number of rows postgres_fdw 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 1.

Note the actual number of rows postgres_fdw inserts at once depends on the number of columns and the provided batch_size value. The batch is executed as a single query, and the libpq protocol (which postgres_fdw uses to connect to a remote server) limits the number of parameters in a single query to 65535. When the number of columns * batch_size exceeds the limit, the batch_size will be adjusted to avoid an error.

F.65.1.5. Asynchronous Execution Options

postgres_fdw supports asynchronous execution, which runs multiple parts of an Append node concurrently rather than serially to improve performance. This execution can be controlled using the following option:

async_capable (boolean)

This option controls whether postgres_fdw 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 false.

In order to ensure that the data being returned from a foreign server is consistent, postgres_fdw 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.

Asynchronous execution is applied even when an Append 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 postgres_fdw, 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.

F.65.1.6. Transaction Management Options

As described in the Transaction Management section, in postgres_fdw 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 postgres_fdw commits those remote transactions serially when the local transaction is committed. When multiple remote subtransactions are involved in the current local subtransaction, by default postgres_fdw commits those remote subtransactions serially when the local subtransaction is committed. Performance can be improved with the following option:

parallel_commit (boolean)

This option controls whether postgres_fdw 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 false.

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.

When this option is enabled, a foreign server with many remote transactions may see a negative performance impact when the local transaction is committed.

F.65.1.7. Updatability Options

By default all foreign tables using postgres_fdw are assumed to be updatable. This may be overridden using the following option:

updatable (boolean)

This option controls whether postgres_fdw allows foreign tables to be modified using INSERT, UPDATE and DELETE 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 true.

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 information_schema views will report a postgres_fdw foreign table to be updatable (or not) according to the setting of this option, without any check of the remote server.

F.65.1.8. Truncatability Options

By default all foreign tables using postgres_fdw are assumed to be truncatable. This may be overridden using the following option:

truncatable (boolean)

This option controls whether postgres_fdw allows foreign tables to be truncated using the TRUNCATE 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 true.

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.

F.65.1.9. Importing Options

postgres_fdw is 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.

Importing behavior can be customized with the following options (given in the IMPORT FOREIGN SCHEMA command):

import_collate (boolean)

This option controls whether column COLLATE options 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. 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.

Even when this parameter is set to true, importing columns whose collation is the remote server's default can be risky. They will be imported with COLLATE "default", which will select the local server's default collation, which could be different.

import_default (boolean)

This option controls whether column DEFAULT expressions 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 IMPORT will fail altogether if an imported default expression uses a function or operator that does not exist locally.

import_generated (boolean)

This option controls whether column GENERATED expressions are included in the definitions of foreign tables imported from a foreign server. The default is true. The IMPORT will fail altogether if an imported generated expression uses a function or operator that does not exist locally.

import_not_null (boolean)

This option controls whether column NOT NULL constraints are included in the definitions of foreign tables imported from a foreign server. The default is true.

Note that constraints other than NOT NULL will never be imported from the remote tables. Although Postgres Pro 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 CREATE FOREIGN TABLE.

Tables or foreign tables which are partitions of some other table are imported only when they are explicitly specified in LIMIT TO clause. Otherwise they are automatically excluded from IMPORT FOREIGN SCHEMA. 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.

F.65.1.10. Connection Management Options

By default, all connections that postgres_fdw establishes to foreign servers are kept open in the local session for re-use.

keep_connections (boolean): This option controls whether postgres_fdw 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 on. If set to off, all connections to this foreign server will be discarded at the end of each transaction.

F.65.2. Functions

postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record

This function returns the foreign server names of all the open connections that postgres_fdw established from the local session to the foreign servers. It also returns whether each connection is valid or not. false 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 NULL if the server is dropped), and then such invalid connection will be closed at the end of that transaction. true is returned otherwise. If there are no open connections, no record is returned. Example usage of the function:

postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
 server_name | valid
-------------+-------
 loopback1   | t
 loopback2   | f

postgres_fdw_disconnect(server_name text) returns boolean

This function discards the open connections that are established by postgres_fdw 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 true if it disconnects at least one connection, otherwise false. If no foreign server with the given name is found, an error is reported. Example usage of the function:

postgres=# SELECT postgres_fdw_disconnect('loopback1');
 postgres_fdw_disconnect
-------------------------
 t

postgres_fdw_disconnect_all() returns boolean

This function discards all the open connections that are established by postgres_fdw 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 true if it disconnects at least one connection, otherwise false. Example usage of the function:

postgres=# SELECT postgres_fdw_disconnect_all();
 postgres_fdw_disconnect_all
-----------------------------
 t

F.65.3. 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. By default this connection is kept and re-used for subsequent queries in the same session. This behavior can be controlled using keep_connections 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.

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.

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, keep_connections option for a foreign server may be disabled, or postgres_fdw_disconnect and postgres_fdw_disconnect_all functions may be used. For example, these are useful to close connections that are no longer necessary, thereby releasing connections on the foreign server.

F.65.4. 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 Postgres Pro release might modify these rules.

Note that it is currently not supported by postgres_fdw to prepare the remote transaction for two-phase commit.

F.65.5. 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 data types, operators, and functions that are built-in or belong to an extension that's listed in the foreign server's extensions option. Operators and functions in such clauses must be IMMUTABLE as well. For an UPDATE or DELETE query, postgres_fdw attempts to optimize the query execution by sending the whole query to the remote server if there are no query WHERE clauses that cannot be sent to the remote server, no local joins for the query, no row-level local BEFORE or AFTER triggers or stored generated columns on the target table, and no CHECK OPTION constraints from parent views. In UPDATE, expressions to assign to target columns must use only built-in data types, IMMUTABLE operators, or IMMUTABLE functions, to reduce the risk of misexecution of the query.

When postgres_fdw 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 JOIN clauses, it takes the same precautions as mentioned above for the WHERE clauses.

The query that is actually sent to the remote server for execution can be examined using EXPLAIN VERBOSE.

F.65.6. Remote Query Execution Environment

In the remote sessions opened by 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_fdw 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 SET search_path options (see CREATE FUNCTION) to such functions to establish their expected search path environment.

postgres_fdw likewise establishes remote session settings for various parameters:

TimeZone is set to UTC
DateStyle is set to ISO
IntervalStyle is set to postgres
extra_float_digits is set to 3 for remote servers 9.0 and newer and is set to 2 for older versions

These are less likely to be problematic than search_path, but can be handled with function SET options if the need arises.

It is not recommended that you override this behavior by changing the session-level settings of these parameters; that is likely to cause postgres_fdw to malfunction.

F.65.7. Cross-Version Compatibility

postgres_fdw can 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_fdw generally assumes that immutable built-in functions and operators are safe to send to the remote server for execution, if they appear in a WHERE 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 “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-SELECT with OFFSET 0 as an optimization fence, and placing the problematic function or operator outside the sub-SELECT.

F.65.8. Configuration Parameters

postgres_fdw.application_name (string)

Specifies a value for application_name configuration parameter used when postgres_fdw establishes a connection to a foreign server. This overrides application_name option of the server object. Note that change of this parameter doesn't affect any existing connections until they are re-established.

postgres_fdw.application_name can be any string of any length and contain even non-ASCII characters. However when it's passed to and used as application_name in a foreign server, note that it will be truncated to less than NAMEDATALEN characters and anything other than printable ASCII characters will be replaced with question marks (?). See application_name for details.

% characters begin “escape sequences” 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 % and before the option, for alignment and padding.

Escape	Effect
`%a`	Application name on local server
`%c`	Session ID on local server (see log_line_prefix for details)
`%C`	Cluster name on local server (see cluster_name for details)
`%u`	User name on local server
`%d`	Database name on local server
`%p`	Process ID of backend on local server
`%%`	Literal %

For example, suppose user local_user establishes a connection from database local_db to foreign_db as user foreign_user, the setting 'db=%d, user=%u' is replaced with 'db=local_db, user=local_user'.

postgres_fdw.read_only_transactions (boolean)

Enables read-only queries to foreign tables in read-write transactions. If set to on, all remote transactions executed by the extension are created as READ ONLY.

F.65.9. Examples

Here is an example of creating a foreign table with postgres_fdw. First install the extension:

CREATE EXTENSION postgres_fdw;

Then create a foreign server using CREATE SERVER. In this example we wish to connect to a Postgres Pro server on host 192.83.123.89 listening on port 5432. The database to which the connection is made is named foreign_db on the remote server:

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

A user mapping, defined with CREATE USER MAPPING, is needed as well to identify the role that will be used on the remote server:

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

Now it is possible to create a foreign table with CREATE FOREIGN TABLE. In this example we wish to access the table named some_schema.some_table on the remote server. The local name for it will be foreign_table:

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

It's essential that the data types and other properties of the columns declared in CREATE FOREIGN TABLE match the actual remote table. Column names must match as well, unless you attach column_name options to the individual columns to show how they are named in the remote table. In many cases, use of IMPORT FOREIGN SCHEMA is preferable to constructing foreign table definitions manually.

F.65.10. Author

Shigeru Hanada <shigeru.hanada@gmail.com>

Prev	Up	Next
F.64. plantuner	Home	F.66. ptrack