Someone set up a server at my workplace that allows our Postgres servers to pull data from our MS SQL servers using a foreign data wrapper, with something like this: https://github.com/GeoffMontee/tds_fdw
It's working pretty cool, until we try to pull or query large amounts of records, in the 10,000s. Postgres just kicks us out, like so:
apitransform=# select count(*) from fdw_ThingInstance;
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., Server: abcdefg, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: abcdefg, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'abcdefg_staging'., Server: abcdefg, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., Server: abcdefg, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: abcdefg, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'abcdefg_staging'., Server: abcdefg, Process: , Line: 1, Level: 0
The connection to the server was lost. Attempting reset: Succeeded.
We're having trouble populating our records because it feels as though there's a hard limit being imposed by one of these systems.
One clue is that when FreeTDS, when set to 4.2, we're able to query roughly normally, or at least, able to count the records. However, we ran into UTF character encoding issues. So, we set it to 7.0 and 8.0, and this solves the UTF issue, but there's now a limit to how much we can pull over at a time, and the limit is unpredictable and breaks things often.
Might anyone know what the deal is? Is it just a small setting somewhere?
Try with 'initial block size' parameter in freetds.conf, I've set this in 5120 and sometimes even bigger. See http://www.freetds.org/userguide/seemtooslow.htm