H.6. TDS Foreign data wrapper#

H.6. TDS Foreign data wrapper

H.6. TDS Foreign data wrapper

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.

H.6.1. About tds_fdw

Version: 2.0.4

GitHub

Author: Geoff Montee

H.6.2. Installation on RHEL and clones (CentOS, Rocky Linux, AlmaLinux, Oracle…)

This document will show how to install tds_fdw on Rocky Linux 8.9. RHEL distributions should be similar.

Note

For the sake of simplicity, we will use yum as it works as an alias for dnf on newer distributions.

H.6.2.1. Option A: yum/dnf (released versions)

H.6.2.1.1. tds_fdw

The Tantor SE development team packages tds_fdw, but they do not provide FreeTDS.

First, install the EPEL repository:

sudo yum install epel-release

And then install tds_fdw:

sudo yum install tds_fdw11.x86_64

H.6.2.2. Option B: Compile tds_fdw

H.6.2.2.1. Install FreeTDS devel and build dependencies

The TDS foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS.

Note

You need the EPEL repository installed to install FreeTDS

sudo yum install epel-release
sudo yum install freetds-devel

Some other dependencies are also needed to install Tantor SE and then compile tds_fdw:

sudo yum install clang llvm make redhat-rpm-config wget

H.6.2.3. Final steps

H.6.2.3.1. Install extension
/usr/pgsql-11/bin/psql -U postgres
postgres=# CREATE EXTENSION tds_fdw;

H.6.3. Installation on Ubuntu

This document will show how to install tds_fdw on Ubuntu 18.04. Other Ubuntu distributions should be similar.

H.6.3.1. Install FreeTDS and build dependencies

The TDS foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS.

sudo apt-get update
sudo apt-get install libsybdb5 freetds-dev freetds-common

Some other dependencies are also needed to install PostgreSQL and then compile tds_fdw:

sudo apt-get install gnupg gcc make

H.6.4. Installation on Debian

This document will show how to install tds_fdw on Debian 10. Other Debian distributions should be similar.

H.6.4.1. Install FreeTDS and build dependencies

The TDS foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS.

sudo apt-get update
sudo apt-get install libsybdb5 freetds-dev freetds-common

Some other dependencies are also needed to install Tantor SE and then compile tds_fdw:

sudo apt-get install gnupg gcc make

H.6.5. Installation on openSUSE

This document will show how to install tds_fdw on openSUSE Leap 15.1. Other openSUSE and SUSE distributions should be similar.

H.6.5.1. Install FreeTDS and build dependencies

The TDS foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS.

sudo zypper install freetds-devel

Some other dependencies are also needed to install Tantor SE and then compile tds_fdw:

sudo zypper install gcc make

H.6.6. Installation on OSX

This document will show how to install tds_fdw on OSX using the Homebrew package manager for the required packages.

H.6.6.1. Install FreeTDS

The TDS foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS.

brew install freetds

Note: If you install FreeTDS from another source, e.g. MacPorts, you might have to adjust the value for TDS_INCLUDE in the make calls below (e.g. -I/opt/local/include/freetds for MacPorts).

H.6.7. Installation on Alpine (and Docker)

This document will show how to install tds_fdw on Alpine Linux 3.10.3. Other Alpine Linux distributions should be similar.

H.6.7.1. Install FreeTDS and build dependencies

The TDS foreign data wrapper requires a library that implements the DB-Library interface, such as FreeTDS.

apk add --update freetds-dev

Some other dependencies are also needed to install Tantor SE and then compile tds_fdw:

apk add gcc libc-dev make

In case you will get fatal error: stdio.h: No such file or directory later on (on make USE_PGXS=1) - installing musl-dev migth help (https://stackoverflow.com/questions/42366739/gcc-cant-find-stdio-h-in-alpine-linux):

apk add musl-dev

H.6.8. Usage

H.6.8.1. Creating a Foreign Server

H.6.8.1.1. Options
H.6.8.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.

H.6.8.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

H.6.8.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');

H.6.8.2. Creating a Foreign Table

H.6.8.2.1. Options
H.6.8.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.

H.6.8.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.

H.6.8.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');

H.6.8.3. Creating a User Mapping

H.6.8.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.

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

H.6.8.4. Importing a Foreign Schema

H.6.8.4.1. Options
H.6.8.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.

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

H.6.8.5. Variables

H.6.8.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.

H.6.8.5.2. Setting Variables

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

postgres=# SET tds_fdw.show_finished_memory_stats=1;
SET

H.6.8.6. EXPLAIN

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

H.6.9. 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.

H.6.10. 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.

H.6.11. Support

If you find any bugs, or you would like to request enhancements, please submit your comments on the project’s GitHub Issues page.

H.6.12. Debugging

H.6.12.1. Testing scripts

Testing should follow that workflow :

  • First build a MSSQL Server

    1. Create the server (local, container, VM or azure)

    2. Create a testing database with a proper user and access privileges

    3. Run mssql-tests.py against that server.

  • Next, build a Tantor SE Server

    1. Create the server (on your machine, with docker o rVM)

    2. Compile and install tds_fdw extension

    3. Create a testing database and schema with proper user and access privilege

    4. On that database you’ll have first to install tds_fdw: CREATE EXTENSION tds_fdw;

    5. You can run postgresql_test.py

H.6.12.2. Debugging

It may be interesting to build a full setup for debugging purpose and use tests to check if anythong regresses. For this, you can use --debugging parameter at postgresql-tests.py launch time.

The test program will stop just after connection creation and give you the backend PID used for testing. This will allow you to connect gdb in another shell session (gdb --pid=<PID>). Once connected with gdb, just put breakpoints where you need and run cont. Then you can press any key in the test shell script to start testing.

Also, in case of test failure, --debugging will allow to define quite precisely where the script crashed using psycopg2 Diagnostics class information and will give the corresponding SQL injected in Tantor SE.

H.6.12.3. Adding or modifying tests

There are two folders where tests are added:

  • tests/mssql contains the tests to interact with a MSSQL server using mssql-tests.py. Such tests are, normally, used to create stuff required for the Tantor SE test themselves.

  • tests/postgresql contains the test to interact with a Tantor SE server using postgresql-tests.py. Such tests are, normally, used to test the tds_fdw functionalities.

Each test is made up of two files, with the same name and different extension in this format:

XXX_description

For example: 000_my_test.json and 000_my_test.sql.

Rule1: XXX is used to provide an order to the scripts.

Rule2: If a script creates an item, or adds a row, it must assume that such item or row exists already, and handle it (for example dropping the table before creating it)

H.6.12.3.1. The JSON file

Always has the following format:

{
    "test_desc" : "<My description>",
    "server" : {
        "version" : {
            "min" : "<Min.Required.Ver>",
            "max" : "<Max.Supported.Ver>"
        }
    }
}
  • test_desc can be any arbitrary string describing the test.

  • min and max are version the for mat X.W[.Y[.Z]] for MSSQL and Tantor SE respectively.

    • min is mandatory, as minimum 7.0.623 for MSSQL (MSSQL 7.0 RTM) and 9.2.0 for Tantor SE.

    • max is also mandatory, but it can be an empty string if the test supports up to the most recent MSSQL or Tantor SE version.

You can check the list of versions for MSSQL (format X.Y.Z and X.Y.W.Z), Tantor SE (formats X.Y.Z and X.Y), to adjust the min and max values as needed.

To validate the JSON file, you can use the script validate-test-json.

H.6.12.3.2. The SQL file

It is a regular SQL file for one or more queries for either MSSQL or Tantor SE.

There are several variables that can be used and will be placed by the testing scripts.

For the MSSQL scripts, the values come from the mssql-tests.py parameters:

  • @SCHEMANAME: The MSSQL schema name.

For the Tantor SE scripts the values come from the postgresql-tests.py parameters:

  • @PSCHEMANAME: The Tantor SE schema name

  • @PUSER: The Tantor SE user

  • @MSERVER: The MSSQL Server

  • @MPORT: The MSSQL port

  • @MUSER: The MSSQL user

  • @MPASSWORD: The MSSQL password

  • @MDATABASE: The MSSQL database

  • @MSCHEMANAME: The MSSQL schema name

  • @TDSVERSION: The TDS version