postgres_fdw

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

Функциональность, обеспечиваемая этим модулем, во многом пересекается с функциональностью старого модуля 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 или TRUNCATE. (Естественно, удаленный пользователь, которого вы указали в сопоставлении, должен иметь необходимые права для этого.)

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

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

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

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

Параметры обертки сторонних данных для postgres_fdw

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

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

user, password и sslpassword (их следует задавать в сопоставлениях пользователей или использовать файл служб)
client_encoding (автоматически принимается равной кодировке локального сервера)
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');

Чтобы не позволить недоверенным пользователям злоупотреблять правами аутентификации пользователя, от имени которого работает сервер qhb, чтобы повысить свои права до суперпользователя, изменять этот параметр в сопоставлении пользователя может только суперпользователь.

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

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

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

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

table_name
Этот параметр, который можно указывать для сторонней таблицы, задает имя таблицы для обращения к этой таблице на удаленном сервере. Если этот параметр опущен, используется имя сторонней таблицы.

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

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

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

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

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

fdw_tuple_cost
Этот параметр, который можно указывать для стороннего сервера, является значением с плавающей запятой, которое используется как дополнительная стоимость чтения одного кортежа при сканировании сторонней таблицы на этом сервере. Он отображает дополнительные издержки на передачу данных между серверами. Это число можно увеличить или уменьшить, чтобы отразить большую или меньшую сетевую задержку при взаимодействии с удаленным сервером. Значение по умолчанию — 0.01.

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

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

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

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

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

batch_size
Этот параметр задает число строк, которое должна вставлять postgres_fdw в каждой операции вставки. Его можно задать для сторонней таблицы или стороннего сервера. Параметр, указанный для таблицы, переопределяет параметр сервера. Значение по умолчанию — 1.
Обратите внимание, что фактическое число строк, вставляемых postgres_fdw в одной операции, зависит от числа столбцов и предоставленного значения batch_size. Вставка пакета выполняется в одном запросе, а протокол libpq (который postgres_fdw использует для подключения к удаленному серверу) ограничивает количество параметров одного запроса числом 65535. Когда количество столбцов, умноженное на batch_size, превышает этот предел, batch_size корректируется во избежание ошибки.

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

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

async_capable
Этот параметр определяет, будет ли postgres_fdw допускать одновременное сканирование сторонних таблиц для асинхронного выполнения. Его можно задать для сторонней таблицы или стороннего сервера. Параметр, определенный на уровне таблицы, переопределяет параметр уровня сервера. Значение по умолчанию — false (запрещено).
Для гарантии того, что возвращаемые со стороннего сервера данные являются согласованными, postgres_fdw открывает только одно соединение для каждого стороннего сервера и выполняет все запросы к нему последовательно, даже если задействовано несколько сторонних таблиц (несколько соединений будет открыто, только если для этих таблиц используются разные сопоставления пользователей). Возможно, в таком случае для увеличения производительности стоит выключить этот параметр, чтобы убрать издержки, связанные с асинхронным выполнением запросов.
Асинхронное выполнение применяется, даже когда узел Append содержит как подпланы, выполняемые синхронно, так и подпланы, выполняемые асинхронно. В таких случаях, если асинхронные подпланы обрабатываются postgres_fdw, кортежи этих подпланов не возвращаются, пока хотя бы один синхронный подплан не вернет все кортежи, так как этот подплан выполняется, пока асинхронные подпланы ожидают результатов асинхронных запросов, переданных сторонним серверам. Это поведение может измениться в будущих версиях.

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

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

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

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

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

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

Параметры импорта

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

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

import_collate
Этот параметр определяет, будут ли в определениях сторонних таблиц, импортируемых со стороннего сервера, включаться правила сортировки столбцов (параметр COLLATE). Значение по умолчанию — true (включен). Вам может понадобиться выключить его, если на удаленном сервере набор имен правил сортировки отличается от локального, что скорее всего произойдет, если удаленный сервер работает в другой операционной системе. Однако при отключении крайне велик риск, что правила сортировки в столбцах импортируемых таблиц не совпадут с правилами для нижележащих данных, что приведет к аномальному поведению запросов.
Даже когда этот параметр равен true, импортировать столбцы с правилом сортировки, выбираемом на удаленном сервере по умолчанию, может быть рискованно. Эти столбцы будут импортироваться со свойством COLLATE "default", в результате чего будет применяться правило сортировки, выбираемое по умолчанию на локальном сервере, которое может отличаться от выбираемого на удаленном.

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

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

import_not_null
Этот параметр определяет, будут ли в определениях сторонних таблиц, импортируемых со стороннего сервера, включаться заданные для столбцов ограничения NOT NULL. Значение по умолчанию — true (включен).

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

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

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

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

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

Функции

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

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

qhb=# 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. Если сторонний сервер с заданным именем не найден, выдается ошибка. Пример использования этой функции:

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

postgres_fdw_disconnect_all() returns boolean

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

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

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

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

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

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

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

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

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

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

Оптимизация удаленных запросов

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

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

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

Среда удаленного выполнения запросов

В удаленных сеансах, установленных 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

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

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

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

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

Примеры

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

CREATE EXTENSION postgres_fdw;

Затем создайте сторонний сервер командой CREATE SERVER. В данном примере мы хотим подключиться к серверу QHB на хосте 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 предпочтительнее, чем конструировать определения сторонних таблиц вручную.

Автор

Шигеру Ханада (Shigeru Hanada), shigeru.hanada@gmail.com