Linked server in babelfish

[kranthi1385]

Hi Support,

Can you please please help me on set up of linked server from one Babelfish to another Babelfish server.

[staticlibs]

Hi, something like this should work (reference):

exec sp_execute_postgresql 'create extension tds_fdw'
exec sp_addlinkedserver @server='babelfish_remote', @provider='SQLNCLI', @datasrc='192.168.178.58:1433'
exec sp_addlinkedsrvlogin @rmtsrvname='babelfish_remote', @useself='False', @rmtuser='jdbc_user', @rmtpassword='12345678'
select * from openquery(babelfish_remote, 'select pg_backend_pid()')

[kranthi1385]

@staticlibs thanks for the query i configured linked server but getting this error when tried to query using linked server can you please suggest any. please find the error in the attachments

[staticlibs]

@kranthi1385

It looks like your build of Babelfish does not include the support for Linked Servers. You need to build it with FreeTDS lib (sybdb) and tds_fdw extension included, see details in the install guide.

[kranthi1385]

@staticlibs

I have built FreeTDS lib and followed the same steps that are mentioned in install guide.

[staticlibs]

@kranthi1385

The flag ENABLE_TDS_LIB did not come through in your case, you can add it (and sybdb lib) in a Makefile instead:

diff --git a/contrib/babelfishpg_tsql/Makefile b/contrib/babelfishpg_tsql/Makefile
index f89bdbcf..b03e79c0 100644
--- a/contrib/babelfishpg_tsql/Makefile
+++ b/contrib/babelfishpg_tsql/Makefile
@@ -96,10 +96,10 @@ PG_CFLAGS += -g -Werror -Wfloat-conversion
 PG_CFLAGS += -fstack-protector-strong
 PG_CFLAGS += -fcommon -Wno-error=format-security -Wno-error=maybe-uninitialized
 PG_CFLAGS += -ggdb -O0 -fno-omit-frame-pointer -Wno-error=infinite-recursion
-PG_CFLAGS += -Wno-error=deprecated-declarations
+PG_CFLAGS += -Wno-error=deprecated-declarations -DENABLE_TDS_LIB -Wno-error=address
 PG_CPPFLAGS += -I$(TSQLSRC) -I$(PG_SRC) -DFAULT_INJECTOR -Wfloat-conversion

-SHLIB_LINK += -L$(ANTLR4_RUNTIME_LIB_DIR) $(ANTLR4_RUNTIME_LIB) -lcrypto $(ICU_LIBS)
+SHLIB_LINK += -L$(ANTLR4_RUNTIME_LIB_DIR) $(ANTLR4_RUNTIME_LIB) -lsybdb -lcrypto $(ICU_LIBS)

 UPGRADES = $(patsubst sql/upgrades/%.sql,sql/%.sql,$(wildcard sql/upgrades/*.sql))

[kranthi1385]

@staticlibs i am not able to resolve the issue still getting same error

Msg 33557097, Level 16, State 1, Line 1 Could not establish connection with remote server as use of TDS client library has been disabled. Please recompile source with 'ENABLE_TDS_LIB' flag to enable client library.

Can you please share step by step commands to setup
I am following the steps mentioned in the link https://github.com/tds-fdw/tds_fdw/blob/master/InstallUbuntu.md

[staticlibs]

@kranthi1385

Please show your compilation output for linked_servers.c file, it should look like this, note that -DENABLE_TDS_LIB flag must be present there:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -ggdb3 -O0 -DENABLE_TDS_LIB -Wno-error=address -g -Werror -Wfloat-conversion -fstack-protector-strong -fcommon -Wno-error=format-security -Wno-error=maybe-uninitialized -ggdb -O0 -fno-omit-frame-pointer -Wno-error=infinite-recursion -Wno-error=deprecated-declarations -fPIC -fvisibility=hidden -DENABLE_TDS_LIB -Wno-error=address -g -Werror -Wfloat-conversion -fstack-protector-strong -fcommon -Wno-error=format-security -Wno-error=maybe-uninitialized -ggdb -O0 -fno-omit-frame-pointer -Wno-error=infinite-recursion -Wno-error=deprecated-declarations -fPIC -fvisibility=hidden -I. -I/home/alex/projects/postgres/dev/postgresql_modified_for_babelfish/ -DFAULT_INJECTOR -Wfloat-conversion -I. -I./ -I/home/alex/projects/postgres/dist/include/server -I/home/alex/projects/postgres/dist/include/internal  -I -I/home/alex/projects/postgres/dev/postgresql_modified_for_babelfish/ -DFAULT_INJECTOR -Wfloat-conversion -I. -I./ -I/home/alex/projects/postgres/dist/include/server -I/home/alex/projects/postgres/dist/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/linked_servers.o src/linked_servers.c
src/linked_servers.c: In function ‘getOpenqueryTupdescFromMetadata’:
src/linked_servers.c:1026:59: warning: the comparison will always evaluate as ‘false’ for the address of ‘bind_typename’ will never be NULL [-Waddress]
src/linked_servers.c:967:49: note: ‘bind_typename’ declared here
src/linked_servers.c:1045:59: warning: the comparison will always evaluate as ‘false’ for the address of ‘bind_colname’ will never be NULL [-Waddress]
src/linked_servers.c:966:49: note: ‘bind_colname’ declared here

[kranthi1385]

@staticlibs please find the output.

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I./include/ -fvisibility=hidden -I/lib/x86_64-linux-gnu -DENABLE_TDS_LIB -Wall -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o src/deparse.o src/deparse.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-10/lib -Wl,--as-needed -fvisibility=hidden -L/lib/x86_64-linux-gnu -lsybdb cp sql/tds_fdw.sql sql/tds_fdw--2.0.4.sql cp README.md README.tds_fdw.md /usr/bin/clang-10 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I./include/ -fvisibility=hidden -I/lib/x86_64-linux-gnu -DENABLE_TDS_LIB -Wall -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o src/tds_fdw.bc src/tds_fdw.c src/tds_fdw.c:3953:8: warning: unused variable 'is_sql_server' [-Wunused-variable] bool is_sql_server = true; ^ src/tds_fdw.c:3954:17: warning: unused variable 'buf' [-Wunused-variable] StringInfoData buf; ^ 2 warnings generated. /usr/bin/clang-10 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I./include/ -fvisibility=hidden -I/lib/x86_64-linux-gnu -DENABLE_TDS_LIB -Wall -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o src/options.bc src/options.c /usr/bin/clang-10 -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I./include/ -fvisibility=hidden -I/lib/x86_64-linux-gnu -DENABLE_TDS_LIB -Wall -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o src/deparse.bc src/deparse.c

[staticlibs]

@kranthi1385

This is the output from compilation of the tds_fdw extension. And I was pointing out the linked_servers.c that is a part of babelfishpg_tsql extension. Please check that -DENABLE_TDS_LIB is passed to linked_servers.c.

[kranthi1385]

@staticlibs

I am following these steps to enable tds_fdw and creating linked server.

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

sudo apt-get install gnupg gcc make

sudo apt install curl ca-certificates sudo install -d /usr/share/postgresql-common/pgdg sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' sudo apt update sudo apt -y install postgresql-client-16 postgresql-server-dev-16

export TDS_FDW_VERSION="2.0.4" sudo apt-get install wget wget https://github.com/tds-fdw/tds_fdw/archive/v${TDS_FDW_VERSION}.tar.gz tar -xvzf v${TDS_FDW_VERSION}.tar.gz cd tds_fdw-${TDS_FDW_VERSION}/ PG_CPPFLAGS='-I/usr/include -DENABLE_TDS_LIB' SHLIB_LINK='-lsybdb -L/usr/lib64' make PG_CPPFLAGS='-I/usr/include -DENABLE_TDS_LIB' SHLIB_LINK='-lsybdb -L/usr/lib64' make install make USE_PGXS=1 PG_CONFIG=/opt/babelfish/4.1.1/bin/pg_config sudo make USE_PGXS=1 PG_CONFIG=/opt/babelfish/4.1.1/bin/pg_config install

Linked server is created

EXEC master.dbo.sp_addlinkedserver @server=N'rds_sqlserver', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'SERVER_IP'; EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'rds_sqlserver',@useself=N'False',@locallogin=NULL,@rmtuser=N'user_babel',@rmtpassword='########';

after creating linked server i am using syntax as below. Executing below query in SSMS

SELECT * FROM linked_name.db_name.schema_name.table_name;

Getting belwo error. Msg 33557097, Level 16, State 1, Line 6 Could not establish connection with remote server as use of TDS client library has been disabled. Please recompile source with 'ENABLE_TDS_LIB' flag to enable client library.

I dont know how to compilation output of linked_server.c please help me on this..

[staticlibs]

@kranthi1385

When building babelfishpg_tsql extension you need to make sure that -DENABLE_TDS_LIB compiler flag is actually passed to gcc. The definition ENABLE_TDS_LIB is used in linked_servers.c (and in the corresponding header) to decide whether to compile in the support for linked servers or not. The Babelfish binaries you are using were apparently built without this flag. One of the ways to enable this flag is to apply this patch to Babelfish sources before running the build. Your build of tds_fdw is likely okay (though the mix of gcc and clang in your log is unusual), but the tds_fdw extension cannot be used from Babelfish unless Babelfish itself was compiled with the support for linked servers.