F.54. postgres_fdw — обращение к данным, находящимся на внешних серверах Postgres Pro
Пред. НаверхПриложение F. Дополнительно поставляемые модули и расширения, поставляемые в postgrespro-std-16-contribНачало След.

F.54. postgres_fdw — обращение к данным, находящимся на внешних серверах Postgres Pro #

F.54.1. Параметры обёртки для postgres_fdw
F.54.2. Функции
F.54.3. Управление соединением
F.54.4. Управление транзакциями
F.54.5. Оптимизация удалённых запросов
F.54.6. Окружение удалённого выполнения запросов
F.54.7. Совместимость с разными версиями
F.54.8. Параметры конфигурации
F.54.9. Примеры
F.54.10. Автор

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

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

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

  1. Установите расширение postgres_fdw с помощью команды CREATE EXTENSION.

  2. Создайте объект стороннего сервера, используя CREATE SERVER, который будет представлять удалённую базу данных, к которой вы хотите подключаться. Укажите свойства подключения, кроме user и password, в параметрах объекта сервера.

  3. Создайте сопоставление пользователей, используя CREATE USER MAPPING, для каждого пользователя базы, которому нужен доступ к удалённому серверу. Укажите имя и пароль удалённого пользователя в параметрах user и password сопоставления.

  4. Создайте стороннюю таблицу, используя 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.54.1. Параметры обёртки для postgres_fdw #

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

Для стороннего сервера, настраиваемого с использованием обёртки сторонних данных postgres_fdw, можно задать те же параметры, что принимает libpq в строках подключения, как описано в Подразделе 32.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, могут только суперпользователи.

Обычные пользователи могут подключаться к сторонним серверам с аутентификацией по паролю или с делегированными учётными данными GSSAPI, поэтому в сопоставлениях для обычных пользователей, где требуется аутентификация по паролю, необходимо указывать параметр 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.54.1.2. Параметры имени объекта #

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

schema_name (string)

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

table_name (string)

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

column_name (string)

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

F.54.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; при этом удалённая таблица будет просканирована, и по её содержимому будут вычислена и сохранена статистика как для локальной таблицы. Локальное хранение статистики может быть полезно для сокращения издержек планирования для удалённой таблицы — но если удалённая таблица меняется часто, локальная статистика будет быстро устаревать.

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

analyze_sampling (string)

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

F.54.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 корректируется во избежание ошибки.

Этот параметр также используется при копировании строк в сторонние таблицы. В этом случае фактическое число строк, которые копирует postgres_fdw в рамках одной операции, определяется так же, как и при операции вставки. Тем не менее ввиду ограничений реализации команды COPY максимальное число копируемых строк не может превышать 1000.

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

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

async_capable (boolean)

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

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

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

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

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

parallel_commit (boolean)

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

parallel_abort (boolean)

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

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

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

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

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

updatable (boolean)

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

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

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

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

truncatable (boolean)

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

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

F.54.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.54.1.10. Параметры управления соединением #

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

keep_connections (boolean)

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

F.54.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.54.3. Управление соединением #

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

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

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

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

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

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

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

F.54.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.54.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.54.7. Совместимость с разными версиями #

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

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

postgres_fdw.application_name (string) #

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

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

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

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

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

F.54.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.54.10. Автор #

Шигеру Ханада

Пред. Наверх След.
F.53. plantuner — поддержка указаний для планировщика, позволяющих отключать или подключать индексы при выполнении запроса Начало F.55. ptrack — механизм резервного копирования базы Postgres Pro на уровне блоков