H.6. TDS Foreign data wrapper#
H.6. TDS Foreign data wrapper
- H.6.1. About tds_fdw
- H.6.2. Installation on RHEL and clones (CentOS, Rocky Linux, AlmaLinux, Oracle…)
- H.6.3. Installation on Ubuntu
- H.6.4. Installation on Debian
- H.6.5. Installation on openSUSE
- H.6.6. Installation on OSX
- H.6.7. Installation on Alpine (and Docker)
- H.6.8. Usage
- H.6.9. Notes about character sets/encoding
- H.6.10. Encrypted connections to MSSQL
- H.6.11. Support
- H.6.12. Debugging
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.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
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.
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
Create the server (local, container, VM or azure)
Create a testing database with a proper user and access privileges
Run
mssql-tests.py
against that server.
Next, build a Tantor SE Server
Create the server (on your machine, with docker o rVM)
Compile and install
tds_fdw
extensionCreate a testing database and schema with proper user and access privilege
On that database you’ll have first to install
tds_fdw
:CREATE EXTENSION tds_fdw;
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 usingmssql-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 usingpostgresql-tests.py
. Such tests are, normally, used to test thetds_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
andmax
are version the for matX.W[.Y[.Z]]
for MSSQL and Tantor SE respectively.min
is mandatory, as minimum7.0.623
for MSSQL (MSSQL 7.0 RTM) and9.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