postgres_fdw — обращение к данным, хранящимся на внешних серверах QHB

Модуль 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, COPY или 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 (этот параметр автоматически устанавливается из кодировки локального сервера)
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 могут только суперпользователи.

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

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

table_name (string)

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

column_name (string)

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

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

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

Следующий параметр управляет поведением операции ANALYZE:

analyze_sampling (string)

Этот параметр, который можно задать для сторонней таблицы или стороннего сервера, определяет, будет ли ANALYZE при выполнении для сторонней таблицы выбирать данные на удаленной стороне или считывать и переносить все данные и выполнять выборку локально. Поддерживаемые значения: off, random, system, bernoulli и auto. При значении off удаленную выборку отключается, так что все данные переносятся и выбираются локально. При значении random удаленная выборка выполняется, используя для выбора возвращаемых строк функцию random(), тогда как значения system и bernoulli полагаются на встроенные методы TABLESAMPLE с теми же названиями. При значении auto (по умолчанию) рекомендованный метод выборки выбирается автоматически; в настоящее время это означает bernoulli или random, в зависимости от версии удаленного сервера.

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

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

extensions (string)

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

fetch_size (integer)

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

batch_size (integer)

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

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

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

async_capable (boolean)

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

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

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

parallel_commit (boolean)

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

parallel_abort (boolean)

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

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

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

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

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

updatable (boolean)

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

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

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

truncatable (boolean)

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

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

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

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

import_collate (boolean)

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

import_default (boolean)

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

import_generated (boolean)

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

import_not_null (boolean)

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

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

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

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

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

keep_connections (boolean)

Этот параметр определяет, будет ли 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.application_name (string)

Задает значение параметра конфигурации application_name, используемого, когда postgres_fdw устанавливает соединение со сторонним сервером. Это переопределяет параметр application_name объекта сервера. Обратите внимание, что изменение этого параметра не влияет ни на какие существующие соединения, пока они не станут вновь устанавливаемыми.
postgres_fdw.application_name может быть любой строкой любой длины и даже содержать не только символы ASCII. Однако когда она передается на сторонний сервер и используется там в качестве параметра application_name, она будет усечена до менее NAMEDATALEN символов. Все символы, отличающиеся от печатаемых символов ASCII, заменяются шестнадцатеричными управляющими символами в стиле C. Подробную информацию см. в описании параметра application_name.
С символов % начинаются «управляющие последовательности», которые заменяются информацией о статусе, описанной ниже. Нераспознанные управляющие последовательности игнорируются. Другие символы напрямую копируются в имя приложения. Обратите внимание, что указывать знак плюс/минус или числовой литерал (для выравнивания и заполнения) между знаком % и параметром не разрешается.

Управляющая последовательность	Что обозначает
%a	Имя приложения на локальном сервере
%c	Идентификатор сеанса на локальном сервере (подробную информацию см. в описании параметра log_line_prefix)
%C	Имя кластера на локальном сервере (подробную информацию см. в описании параметра instance_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. Сначала установите расширение:

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