tds_fdw PostgreSQL Foreign Table Truncates Large Text to 2048 Characters-CodePudding

I have created several foreign tables already, and they all work fine, but now I have to deal with a table that has a column containing 20-30 KB of text in some rows. If that text is small, it is rendered in full, but if it happens to be longer than 2048 characters, the rest is cut off.

My tds_fdw setup:

CREATE SERVER mssql_srv
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'mssql_srv.my_domain.com', port '1433', database 'MY_DB', msg_handler 'blackhole');

ALTER SERVER mssql_srv OWNER TO my_user;

CREATE USER MAPPING FOR my_user
SERVER mssql_srv
OPTIONS (username 'my_user', password 'xxxxxxxx');

CREATE FOREIGN TABLE my_large_table (
  ID VARCHAR (64),
  FULL_TEXT TEXT)
SERVER vsql_tx
OPTIONS (schema_name 'dbo', table_name 'my_large_table', row_estimate_method 'showplan_all');

Yes, column names on the MSSQL side are in uppercase. Their data types are:

ID nvarchar(64)
FULL_TEXT nvarchar(max)

SELECT * FROM my_large_table;

Returns this partial value of FULL_TEXT.

For each truncated field:

SELECT LENGTH(full_text) FROM my_large_table WHERE ID = '50166cd8ed2266e0c8d15d9161477c3d636f193e873c4a97a6309cff237d8f0';
 
 length 
--------
   2048
(1 row)

When I try to select the same values using tsql (the FreeTDS utility), then it returns whole values, no truncation even for 20 KB.

I also have another PostgreSQL server with a similar table, having long textual records. I tried to connect to it using postgres_fdw, and, again, no problem, no truncations.

Any ideas of what could be wrong or what to check? Thanks.

Server setup: Debian 11.1 PostgreSQL 13.4-4.pgdg110 1 tds_fdw: 2.0.2-2.pgdg110 1 freetds-common: 1.2.3-1 libsybdb5:amd64: 1.2.3-1

Microsoft SQL Server 2014 (SP2-CU14) (KB4459860) - 12.0.5600.1 (X64) Sep 27 2018 21:47:31 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)

Windows 2016 Standard Edition

CodePudding user response：

Thanks to @FlipperPA. In short, the solution I take is to set the "text size" parameter of /etc/freetds/freetds.conf or /var/lib/postgresql/.freetds.conf to the maximum value (4294967295):

[global]
...
tds version = auto
...
text size = 4294967295
...

It's not entirely clear why this is needed: the FreeTDS documentation states that this is default already, and tsql doesn't suffer from truncations. I also could not find any other freetds.conf file that tds_fdw could prefer over the main one. Maybe tds_fdw has its own defaults which can be overriden by freetds.conf. Whatever the explanation, setting the above parameter works.