Postgres Pro Enterprise : Документация: 17: H.7. tds_fdw — подключение к базам данных, использующим протокол TDS : Компания Postgres Professional

H.7. tds_fdw — подключение к базам данных, использующим протокол TDS
Пред.	Наверх	Приложение H. Сторонние модули и расширения, поставляемые в виде отдельных пакетов	Начало	След.

H.7. tds_fdw — подключение к базам данных, использующим протокол TDS #

H.7.1. Ограничения
H.7.2. Установка tds_fdw
H.7.3. Настройка tds_fdw
H.7.4. Использование
H.7.5. Автор

Модуль tds_fdw предоставляет обёртку сторонних данных tds_fdw, которая может подключаться к базам данных, использующим протокол TDS (Tabular Data Stream, поток табличных данных), например Sybase и Microsoft SQL Server.

Для этой обёртки требуется библиотека, реализующая интерфейс DB-Library, например FreeTDS. Обёртка была протестирована с FreeTDS, но не с проприетарными реализациями DB-Library.

H.7.1. Ограничения #

При включённом match_column_names поддерживаются условия WHERE и вынос столбцов наружу. Однако вынос наружу JOIN и пишущие операции не поддерживаются.
DML-операции INSERT, UPDATE и DELETE со сторонними таблицами не поддерживаются.

H.7.2. Установка tds_fdw #

tds_fdw поставляется вместе с Postgres Pro Enterprise в виде отдельного пакета tds_fdw (подробные инструкции по установке приведены в Главе 17).

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

H.7.3. Настройка tds_fdw #

H.7.3.1. Управление наборами символов/кодировкой #

Хотя многие новые версии протокола TDS для связи с сервером используют только стандарт USC-2, FreeTDS преобразует UCS-2 в клиентскую кодировку по вашему выбору. Чтобы установить клиентскую кодировку, можно задать для переменной client charset значение freetds.conf. За подробностями обратитесь к описанию файла freetds.conf (файла freetds.conf) и Localization and TDS 7.0 (локализации и TDS 7.0).

При получении указанной ниже ошибки во время работы Microsoft SQL Server с данными Unicode может потребоваться дополнительная настройка:

NOTICE: DB-Library notice: Msg #: 4004, Msg state: 1, Msg: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier., Server: PILLIUM\SQLEXPRESS, Process: , Line: 1, Level: 16

ERROR: DB-Library error: DB #: 4004, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), Level: 16

В таком случае, возможно, придётся вручную указать в файле freetds.conf версию tds 7.0 или выше. За подробностями обратитесь к описанию файла freetds.conf и Выбор версии протокола TDS.

H.7.3.2. Настройка зашифрованных подключений к MSSQL #

Такие подключения настраиваются в файле freetds.conf. Найдите "шифрование" в разделе freetds.conf settings файла freetds.conf.

H.7.4. Использование #

Чтобы обеспечить доступ к базе данных с помощью tds_fdw, выполните следующие шаги:

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

H.7.4.1. Создание стороннего сервера #

Чтобы создать сторонний сервер, выполните команду CREATE SERVER со следующими параметрами:

servername

Имя сервера, адрес или имя узла стороннего сервера. Значением может быть DSN, как указано в freetds.conf. За подробностями обратитесь к разделу Поиск по имени FreeTDS. Можно задать в этом параметре список имён серверов, разделённый запятыми, тогда будут выполняться попытки подключиться к каждому серверу до тех пор, пока не будет установлено первое подключение. Такой список может использоваться для автоматического переключения на резервный сервер.

Обязательный: да

Значение по умолчанию: 127.0.0.1

port

Порт стороннего сервера. Вместо этого его можно указать в freetds.conf (если servername является DSN).

Обязательный: нет

database

База данных для подключения на этом сервере

Обязательный: нет

dbuse

Если dbuse равен 0, tds_fdw подключится напрямую к database. Если dbuse не равен 0, tds_fdw подключится к базе данных сервера по умолчанию, а затем выберет базу данных, вызвав функцию dbuse() библиотеки DB-Library. Чтобы использовать Azure, для dbuse необходимо установить значение 0.

Обязательный: нет

По умолчанию: 0

language

Язык, используемый для сообщений, и локаль, используемая для форматов дат. В большинстве систем для FreeTDS по умолчанию может использоваться английский (США). Вероятно, их также можно изменить в freetds.conf. За дополнительной информацией по этой настройке для MS SQL Server обратитесь к разделу SET LANGUAGE in MS SQL Server (SET LANGUAGE в MS SQL Server), а для Sybase ASE — к разделам Sybase ASE login options (Параметры входа в Sybase ASE) и SET LANGUAGE in Sybase ASE (SET LANGUAGE в Sybase ASE).

Обязательный: нет

character_set

Клиентская кодировка, которая будет использоваться при подключении. В протоколе TDS версии 7.0 и выше для подключения всегда используется UCS-2, поэтому в таких случаях этот параметр ничего не делает. За подробностями обратитесь к разделу Localization and TDS 7.0 (локализация и TDS 7.0).

Обязательный: нет

tds_version

Версия протокола TDS, которая будет использоваться для этого сервера. За подробностями обратитесь к разделам Выбор версии протокола TDS и История версий TDS.

Обязательный: нет

msg_handler

Функция, используемая для обработчика сообщений TDS. Допустимо одно из следующих значений:

notice: сообщения TDS преобразуются в замечания PostgreSQL
blackhole: сообщения TDS игнорируются

Обязательный: нет

По умолчанию: blackhole

fdw_startup_cost

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

Обязательный: нет

fdw_tuple_cost

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

Обязательный: нет

sqlserver_ansi_mode

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

Этот параметр поддерживается только для SQL Server. Установка значения true включит следующие параметры стороннего сервера после успешного подключения к нему:

CONCAT_NULLS_YIELDS_NULL ON
ANSI_NULLS ON
ANSI_WARNINGS ON
QUOTED_IDENTIFIER ON
ANSI_PADDING ON
ANSI_NULL_DFLT_ON ON

Эти параметры в целом сопоставимы с параметром SQL Server ANSI_DEFAULTS. Параметр sqlserver_ansi_mode, напротив, в настоящее время не активирует следующие параметры:

CURSOR_CLOSE_ON_COMMIT
IMPLICIT_TRANSACTIONS

Это соответствует поведению собственных драйверов ODBC и OLEDB для SQL-серверов, которые явно отключают эти параметры, если не настроено иначе.

Обязательный: нет

По умолчанию: false

Некоторые параметры сторонней таблицы также могут быть установлены на уровне сервера. К ним относятся:

Пример H.1. Создание стороннего сервера

CREATE SERVER mssql_svr
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test', tds_version '7.1');

H.7.4.2. Создание сопоставлений пользователей #

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

username

Имя пользователя учётной записи на стороннем сервере

Важно

Если вы используете Azure SQL, имя пользователя для стороннего сервера должно быть в формате username@servername. Если используется только имя пользователя, аутентификация завершится ошибкой.

Обязательный: да

password

Пароль учётной записи на стороннем сервере

Обязательный: да

Пример H.2. Создание сопоставления пользователя

CREATE USER MAPPING FOR postgres
    SERVER mssql_svr 
    OPTIONS (username 'sa', password '');

H.7.4.3. Создание сторонней таблицы #

Чтобы создать стороннюю таблицу, выполните команду CREATE FOREIGN TABLE со следующими параметрами:

query #

Строка запроса, используемая для обращения к сторонней таблице

Обязательный: Да (взаимоисключающий с table_name)

schema_name #

Схема, в которой находится таблица. Имя схемы также можно включить в table_name.

Обязательный: нет

table_name #

Таблица на стороннем сервере, к которой выполняется запрос

Псевдонимы: таблица

Обязательный: Да (взаимоисключающий с query)

match_column_names #

Сопоставить локальные столбцы с удалёнными, сравнивая их имена в таблицах вместо использования порядка, в котором они появляются в наборе результатов. Требуется для WHERE и выноса столбцов наружу.

Обязательный: нет

use_remote_estimate #

Оценить размер таблицы, выполнив какую-нибудь операцию на удалённом сервере, как определено в row_estimate_method, вместо использования локальной оценки, как определено в local_tuple_estimate

Обязательный: нет

local_tuple_estimate #

Локально заданная оценка количества кортежей, которая используется, когда отключён параметр use_remote_estimate

Обязательный: нет

row_estimate_method #

Может принимать одно из следующих значений:

execute: выполнить запрос на удалённом сервере и получить фактическое количество строк в запросе
showplan_all: получить оценку количества строк с помощью MS SQL Server SET SHOWPLAN_ALL

Обязательный: нет

По умолчанию: execute

Пример H.3. Создание сторонней таблицы

Использование определения table_name:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');

Использование определений schema_name и table_name:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Использование определения query:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Установка имени расположенного удалённо столбца:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    col2 varchar OPTIONS (column_name 'data'))
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

H.7.4.4. Импорт сторонней схемы #

Чтобы импортировать стороннюю схему, выполните команду IMPORT FOREIGN SCHEMA со следующими параметрами:

import_default

Управляет включением выражений столбцов DEFAULT в определения сторонних таблиц

Обязательный: нет

По умолчанию: false

import_not_null

Управляет включением ограничений столбцов NOT NULL в определения сторонних таблиц

Обязательный: нет

По умолчанию: true

Пример H.4. Импорт сторонней схемы

IMPORT FOREIGN SCHEMA dbo
    EXCEPT (mssql_table)
    FROM SERVER mssql_svr
    INTO public
    OPTIONS (import_default 'true');

H.7.4.5. Задание переменных #

Чтобы задать переменную, выполните команду SET.

Доступны следующие переменные:

tds_fdw.show_before_row_memory_stats: Выводить статистику контекста памяти в журнал Postgres Pro перед выборкой каждой строки
tds_fdw.show_after_row_memory_stats: Выводить статистику контекста памяти в журнал Postgres Pro после выборки каждой строки
tds_fdw.show_finished_memory_stats: Вывести статистику контекста памяти в журнал Postgres Pro после завершения запроса

Пример H.5. Задание переменной

postgres=# SET tds_fdw.show_finished_memory_stats=1;
SET

H.7.4.6. Просмотр запроса, выданного на удалённо расположенной системе #

Чтобы просмотреть запрос, выданный в удалённо расположенной системе, выполните команду EXPLAIN [ VERBOSE ].

H.7.5. Автор #

Джефф Монти

Пред.	Наверх	След.
H.6. plpgsql_check — статический анализ кода для PL/pgSQL	Начало	H.8. wal2json — преобразование изменений из WAL в JSON с помощью логического декодирования

H.7. tds_fdw — connect to databases that use the TDS protocol
Prev	Up	Appendix H. Third-Party Modules and Extensions Shipped as Individual Packages	Home	Next

H.7. tds_fdw — connect to databases that use the TDS protocol #

H.7.1. Limitations
H.7.2. Installing tds_fdw
H.7.3. Configuring tds_fdw
H.7.4. Usage
H.7.5. Author

The tds_fdw module provides the foreign data wrapper tds_fdw, which can connect to databases that use the Tabular Data Stream (TDS) protocol, such as Sybase databases and Microsoft SQL Server.

This foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS. This has been tested with FreeTDS, but not the proprietary implementations of DB-Library.

H.7.1. Limitations #

Unlike WHERE and column pushdowns, which are supported when match_column_names is enabled, JOIN pushdown or write operations are not supported.
DML operations INSERT, UPDATE, and DELETE with foreign tables are not supported.

H.7.2. Installing tds_fdw #

tds_fdw is provided with Postgres Pro Enterprise as a separate pre-built package tds-fdw (for the detailed installation instructions, see Chapter 17).

Install the tds_fdw extension using CREATE EXTENSION.

H.7.3. Configuring tds_fdw #

H.7.3.1. Managing Character Sets/Encoding #

Although many newer versions of the TDS protocol will only use USC-2 to communicate with the server, FreeTDS converts the UCS-2 to the client character set of your choice. To set the client character set, you can set client charset in freetds.conf. See The freetds.conf File and Localization and TDS 7.0 for details.

You may need more configuring in case you get an error like this with Microsoft SQL Server when working with Unicode data:

ERROR: DB-Library error: DB #: 4004, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), Level: 16

In this case, you may have to manually set tds version in freetds.conf to 7.0 or higher. See The freetds.conf File and Choosing a TDS protocol version for details.

H.7.3.2. Configuring Encrypted Connections to MSSQL #

This needs to be configured at the freetds.conf. See The freetds.conf File and under freetds.conf settings, look for encryption.

H.7.4. Usage #

To prepare for database access using tds_fdw:

Create a foreign server object, using CREATE SERVER, to represent each database you want to connect to.
Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server.
Create a foreign table, using CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA, for each table you want to access.

H.7.4.1. Creating a Foreign Server #

To create a foreign server, execute the CREATE SERVER command providing the following options:

servername

The servername, address or hostname of the foreign server. This can be a DSN, as specified in freetds.conf. See FreeTDS name lookup for details. You can set this option to a comma-separated list of server names, then each server is tried until the first connection succeeds. This is useful for automatic failover to a secondary server.

Required: Yes

Default: 127.0.0.1

port

The port of the foreign server. Instead of providing a port here, it can be specified in freetds.conf (if servername is a DSN).

Required: No

database

The database to connect to for this server

Required: No

dbuse

If dbuse is 0, tds_fdw will connect directly to database. If dbuse is not 0, tds_fdw will connect to the server's default database and then select the database by calling the DB-Library's dbuse() function. For Azure, dbuse currently needs to be set to 0.

Required: No

Default: 0

language

The language to use for messages and the locale to use for date formats. FreeTDS may default to U.S. English on most systems. You can probably also change this in freetds.conf. For information related to this for MS SQL Server, see SET LANGUAGE in MS SQL Server. For information related to Sybase ASE, see Sybase ASE login options and SET LANGUAGE in Sybase ASE.

Required: No

character_set

The client character set to use for the connection if you need to set this for some reason. For TDS protocol versions 7.0+, the connection always uses UCS-2, so this parameter does nothing in those cases. See Localization and TDS 7.0 for details.

Required: No

tds_version

The version of the TDS protocol to use for this server. See Choosing a TDS protocol version and History of TDS Versions for details.

Required: No

msg_handler

The function used for the TDS message handler. Can be one of the following values:

notice: TDS messages are turned into PostgreSQL notices
blackhole: TDS messages are ignored

Required: No

Default: blackhole

fdw_startup_cost

A cost that is used in query planning to represent the overhead of using this foreign data wrapper.

Required: No

fdw_tuple_cost

A cost that is used in query planning to represent the overhead of fetching rows from this server.

Required: No

sqlserver_ansi_mode

A cost that is used to represent the overhead of fetching rows from this server used in query planning.

This option is supported for SQL Server only. Setting this to true will enable the following server-side settings after a successful connection to the foreign server:

CONCAT_NULLS_YIELDS_NULL ON
ANSI_NULLS ON
ANSI_WARNINGS ON
QUOTED_IDENTIFIER ON
ANSI_PADDING ON
ANSI_NULL_DFLT_ON ON

Those parameters in summary are comparable to the SQL Server option ANSI_DEFAULTS. In contrast, sqlserver_ansi_mode currently does not activate the following options:

CURSOR_CLOSE_ON_COMMIT
IMPLICIT_TRANSACTIONS

This follows the behavior of the native ODBC and OLEDB driver for SQL servers, which explicitly turn them off if not configured otherwise.

Required: No

Default: false

Some foreign table options can also be set at the server level. Those include:

Example H.1. Create a Foreign Server

CREATE SERVER mssql_svr
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test', tds_version '7.1');

H.7.4.2. Creating a User Mapping #

To create a user mapping, execute the CREATE USER MAPPING command providing the following options:

username

The username of the account on the foreign server

Important

If you are using Azure SQL, then your username for the foreign server will need to be in the format username@servername. If you only use the username, the authentication will fail.

Required: Yes

password

The password of the account on the foreign server

Required: Yes

Example H.2. Create a User Mapping

CREATE USER MAPPING FOR postgres
    SERVER mssql_svr 
    OPTIONS (username 'sa', password '');

H.7.4.3. Creating a Foreign Table #

To create a foreign table, execute the CREATE FOREIGN TABLE command providing the following options:

query #

The query string to use to query the foreign table

Required: Yes (mutually exclusive with table_name)

schema_name #

The schema that the table is in. The schema name can also be included in table_name.

Required: No

table_name #

The table on the foreign server to query

Aliases: table

Required: Yes (mutually exclusive with query)

match_column_names #

Match local columns with remote columns by comparing their table names instead of using the order in which they appear in the result set. Required for WHERE and column pushdowns.

Required: No

use_remote_estimate #

Estimate the size of the table by performing some operation on the remote server, as defined by row_estimate_method, instead of using the local estimate, as defined by local_tuple_estimate

Required: No

local_tuple_estimate #

A locally set estimate of the number of tuples that is used when use_remote_estimate is disabled

Required: No

row_estimate_method #

Can be one of the following values:

execute: Execute the query on the remote server and get the actual number of rows in the query
showplan_all: Get the estimated number of rows using MS SQL Server’s SET SHOWPLAN_ALL

Required: No

Default: execute

Example H.3. Create a Foreign Table

Using a table_name definition:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');

Using a schema_name and table_name definition:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Using a query definition:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Setting a remote column name:

CREATE FOREIGN TABLE mssql_table (
    id integer,
    col2 varchar OPTIONS (column_name 'data'))
    SERVER mssql_svr
    OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

H.7.4.4. Importing a Foreign Schema #

To import a foreign schema, execute the IMPORT FOREIGN SCHEMA command providing the following options:

import_default

Controls whether column DEFAULT expressions are included in the definitions of foreign tables

Required: No

Default: false

import_not_null

Controls whether column NOT NULL constraints are included in the definitions of foreign tables

Required: No

Default: true

Example H.4. Import a Foreign Schema

IMPORT FOREIGN SCHEMA dbo
    EXCEPT (mssql_table)
    FROM SERVER mssql_svr
    INTO public
    OPTIONS (import_default 'true');

H.7.4.5. Setting Variables #

To set a variable, execute the SET command.

The following variables are available:

tds_fdw.show_before_row_memory_stats: Print memory context stats to the Postgres Pro log before each row is fetched
tds_fdw.show_after_row_memory_stats: Print memory context stats to the Postgres Pro log after each row is fetched
tds_fdw.show_finished_memory_stats: Print memory context stats to the Postgres Pro log when a query is finished

Example H.5. Set a Variable

postgres=# SET tds_fdw.show_finished_memory_stats=1;
SET

H.7.4.6. Viewing the Query Issued on the Remote System #

To view the query issued on the remote system, execute the EXPLAIN [ VERBOSE ] command.

H.7.5. Author #

Geoff Montee

Prev	Up	Next
H.6. plpgsql_check — static code analysis for PL/pgSQL	Home	H.8. wal2json — convert WAL changes into JSON via logical decoding