H.2. tds_fdw — connect to databases that use the TDS protocol
Prev UpAppendix H. Third-Party Modules and Extensions Shipped as Individual PackagesHome Next

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

H.2.1. Limitations
H.2.2. Installing tds_fdw
H.2.3. Configuring tds_fdw
H.2.4. Usage
H.2.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.2.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.2.2. Installing tds_fdw #

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

Install the tds_fdw extension using CREATE EXTENSION.

H.2.3. Configuring tds_fdw #

H.2.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:

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

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.2.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.2.4. Usage #

To prepare for database access using tds_fdw:

  1. Create a foreign server object, using CREATE SERVER, to represent each database you want to connect to.

  2. Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server.

  3. Create a foreign table, using CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA, for each table you want to access.

H.2.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.2.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.2.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.2.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.2.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.2.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.2.5. Author #

Geoff Montee

Prev Up Next
H.1. oracle_fdw — access to Oracle databases Home H.3. wal2json — convert WAL changes into JSON via logical decoding