J.6. tds_fdw

J.6. tds_fdw
Prev	Up	Appendix J. Additional External Modules Distributed as Separate Packages	Home	Next

---

J.6. tds_fdw #

J.6.1. Overview

J.6.2. Installation

J.6.3. Usage

J.6.4. Notes about character sets/encoding

J.6.5. Encrypted connections to MSSQL

J.6.1. Overview #

Version: 2.0.5

Download from nexus-public

This is a PostgreSQL foreign data wrapper that 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.

This should support Tantor SE 14+.

The current version does not yet support JOIN push-down, or write operations.

It does support WHERE and column pushdowns when match_column_names is enabled.

J.6.2. Installation #

To install tds_fdw, download the installation script db_extension_installer.sh and make it executable:

wget https://public.tantorlabs.ru/db_extension_installer.sh && \
chmod +x db_extension_installer.sh

Next, you can install tds_fdw:

1. Locally from the downloaded package.

2. Automatically from the package repository.

J.6.2.1. Locally from the downloaded package #

1. Download the tds_fdw package suitable for your OS from nexus-public.

2. Run the tds_fdw installation using db_extension_installer.sh from the downloaded package:

   ./db_extension_installer.sh \
     --from-file=<path to tds_fdw package>
   

J.6.2.2. Automatically from the package repository #

1. Set the value of the environment variable NEXUS_URL:

   export NEXUS_URL="nexus-public.tantorlabs.ru"
   

2. Run the tds_fdw installation using db_extension_installer.sh with the following parameters:

   ./db_extension_installer.sh \
     --database-type=tantor \
     --database-major-version=17 \
     --edition=se \
     --extension=tds_fdw
   

J.6.3. Usage #

J.6.3.1. Creating a Foreign Server #

J.6.3.1.1. Options #

J.6.3.1.1.1. Foreign server parameters accepted: #

• servername

  Required: Yes

  Default: 127.0.0.1

  The servername, address or hostname of the foreign server server.

  This can be a DSN, as specified in freetds.conf. See FreeTDS name lookup.

  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 fail-over to a secondary server.

• port

  Required: No

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

• database

  Required: No

  The database to connect to for this server.

• dbuse

  Required: No

  Default: 0

  This option tells tds_fdw to connect directly to database if dbuse is 0. If dbuse is not 0, tds_fdw will connect to the server’s default database, and then select database by calling DB-Library’s dbuse() function.

  For Azure, dbuse currently needs to be set to 0.

• language

  Required: No

  The language to use for messages and the locale to use for date formats. FreeTDS may default to us_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.

• character_set

  Required: No

  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.

• tds_version

  Required: No

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

• msg_handler

  Required: No

  Default: blackhole

  The function used for the TDS message handler. Options are “notice” and “blackhole.” With the “notice” option, TDS messages are turned into Tantor SE notices. With the “blackhole” option, TDS messages are ignored.

• fdw_startup_cost

  Required: No

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

• fdw_tuple_cost

  Required: No

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

• sqlserver_ansi_mode

  Required: No

  This option is supported for SQL Server only. The default is “false”. 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.

J.6.3.1.1.2. Foreign table parameters accepted in server definition: #

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

• use_remote_estimate

• row_estimate_method

J.6.3.1.2. Example #

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');

J.6.3.2. Creating a Foreign Table #

J.6.3.2.1. Options #

J.6.3.2.1.1. Foreign table parameters accepted: #

• query

  Required: Yes (mutually exclusive with table)

  The query string to use to query the foreign table.

• schema_name

  Required: No

  The schema that the table is in. The schema name can also be included in table_name, so this is not required.

• table_name

  Aliases: table

  Required: Yes (mutually exclusive with query)

  The table on the foreign server to query.

• match_column_names

  Required: No

  Whether to match local columns with remote columns by comparing their table names or whether to use the order that they appear in the result set.

• use_remote_estimate

  Required: No

  Whether we estimate the size of the table by performing some operation on the remote server (as defined by row_estimate_method), or whether we just use a local estimate, as defined by local_tuple_estimate.

• local_tuple_estimate

  Required: No

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

• row_estimate_method

  Required: No

  Default: execute

  This 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: This gets the estimated number of rows using MS SQL Server’s SET SHOWPLAN_ALL.

J.6.3.2.1.2. Foreign table column parameters accepted: #

• column_name

  Required: No

  The name of the column on the remote server. If this is not set, the column’s remote name is assumed to be the same as the column’s local name. If match_column_names is set to 0 for the table, then column names are not used at all, so this is ignored.

J.6.3.2.2. Example #

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');

Or 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');

Or 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');

Or 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');

J.6.3.3. Creating a User Mapping #

J.6.3.3.1. Options #

User mapping parameters accepted:

• username

  Required: Yes

  The username of the account on the foreign server.

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

• password

  Required: Yes

  The password of the account on the foreign server.

J.6.3.3.2. Example #

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

J.6.3.4. Importing a Foreign Schema #

J.6.3.4.1. Options #

J.6.3.4.1.1. Foreign schema parameters accepted: #

• import_default

  Required: No

  Default: false

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

• import_not_null

  Required: No

  Default: true

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

J.6.3.4.2. Example #

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

J.6.3.5. Variables #

J.6.3.5.1. Available Variables #

• tds_fdw.show_before_row_memory_stats - print memory context stats to the Tantor SE log before each row is fetched.

• tds_fdw.show_after_row_memory_stats - print memory context stats to the Tantor SE log after each row is fetched.

• tds_fdw.show_finished_memory_stats - print memory context stats to the Tantor SE log when a query is finished.

J.6.3.5.2. Setting Variables #

To set a variable, use the SET command. i.e.:

postgres=# SET tds_fdw.show_finished_memory_stats=1;
SET

J.6.3.6. EXPLAIN #

EXPLAIN (VERBOSE) will show the query issued on the remote system.

J.6.4. Notes about character sets/encoding #

1. If you get an error like this with MS SQL Server when working with Unicode data:

   Note

   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, 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

   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.

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

J.6.5. Encrypted connections to MSSQL #

It is handled by FreeTDS, so this needs to be configured at the freetds.conf. Seee The freetds.conf File and at freetds.conf settings look for encryption.

---

Prev	Up	Next
J.5. pgbouncer	Home	J.7. wal-g