On 04/27/2016 07:11 AM, Daniel Westermann wrote:
Hi,
I have installed freetds and can connect to the remote mssql server:
postgres@pgreporting:/home/postgres/ [PGREP] tsql -S mssql -U ds2user -P
xxxxxxx -D ds2 -o v
locale is
"LC_CTYPE=en_US.UTF-8;LC_NUMERIC=de_CH.UTF-8;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=de_CH.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=de_CH.UTF-8;LC_NAME=de_CH.UTF-8;LC_ADDRESS=de_CH.UTF-8;LC_TELEPHONE=de_CH.UTF-8;LC_MEASUREMENT=de_CH.UTF-8;LC_IDENTIFICATION=de_CH.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Setting ds2 as default database in login packet
1> select count(*) from sys.databases;
2> go
5
(1 row affected)
using TDS version 7.3
PostgreSQL version is 9.5.2:
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.2 dbi services build on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)
Created the fdw stuff like this:
postgres=# \dx
List of installed extensions
Name | Version | Schema |
Description
---------+---------+------------+-----------------------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
tds_fdw | 1.0.7 | public | Foreign data wrapper for querying a
TDS database (Sybase or Microsoft SQL Server)
(2 rows)
postgres=# create server mssql_svr foreign data wrapper tds_fdw options
( servername '192.168.22.102', port '1433', database 'ds2', tds_version
'7.3', msg_handler 'notice' );
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS
(username 'ds2user', password 'xxxxxx');
CREATE USER MAPPING
postgres=# create foreign table ds2_mssql.customers
(CUSTOMERID int
,FIRSTNAME varchar(50)
,LASTNAME varchar(50)
,ADDRESS1 varchar(50)
,ADDRESS2 varchar(50)
,CITY varchar(50)
,STATE varchar(50)
,ZIP int
,COUNTRY varchar(50)
,REGION int
,EMAIL varchar(50)
,PHONE varchar(50)
,CREDITCARDTYPE int
,CREDITCARD varchar(50)
,CREDITCARDEXPIRATION varchar(50)
,USERNAME varchar(50)
,PASSWORD varchar(50)
,AGE int
,GENDER varchar(50))
SERVER mssql_svr
OPTIONS (query 'select * from dbo.customers', row_estimate_method
'showplan_all');
CREATE FOREIGN TABLE
Once I do a select from the foreign table the server just crashes:
postgres=# select count(*) from ds2_mssql.customers;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
This is from the log:
2016-04-24 14:34:31.896 CEST - 16 - 23252 - - @ LOG: server process
(PID 23796) was terminated by signal 11: Segmentation fault
2016-04-24 14:34:31.896 CEST - 17 - 23252 - - @ DETAIL: Failed process
was running: select count(*) from ds2_mssql.customers;
2016-04-24 14:34:31.896 CEST - 18 - 23252 - - @ LOG: terminating any
other active server processes
2016-04-24 14:34:31.898 CEST - 2 - 23454 - - @ WARNING: terminating
connection because of crash of another server process
2016-04-24 14:34:31.898 CEST - 3 - 23454 - - @ DETAIL: The postmaster
has commanded this server process to roll back the current transaction
and exit, because another server process exited abnormally and possibly
corrupted shared memory.
2016-04-24 14:34:31.898 CEST - 4 - 23454 - - @ HINT: In a moment you
should be able to reconnect to the database and repeat your command.
2016-04-24 14:34:31.901 CEST - 19 - 23252 - - @ LOG: archiver process
(PID 23455) exited with exit code 1
2016-04-24 14:34:31.901 CEST - 1 - 23797 - [local] - postgres@postgres
FATAL: the database system is in recovery mode
2016-04-24 14:34:31.902 CEST - 20 - 23252 - - @ LOG: all server
processes terminated; reinitializing
2016-04-24 14:34:31.931 CEST - 1 - 23798 - - @ LOG: database system
was interrupted; last known up at 2016-04-24 14:33:15 CEST
2016-04-24 14:34:32.262 CEST - 2 - 23798 - - @ LOG: database system
was not properly shut down; automatic recovery in progress
2016-04-24 14:34:32.264 CEST - 3 - 23798 - - @ LOG: redo starts at
0/7074278
2016-04-24 14:34:32.264 CEST - 4 - 23798 - - @ LOG: invalid record
length at 0/7077270
2016-04-24 14:34:32.264 CEST - 5 - 23798 - - @ LOG: redo done at 0/7076100
2016-04-24 14:34:32.266 CEST - 6 - 23798 - - @ LOG: checkpoint
starting: end-of-recovery immediate
2016-04-24 14:34:32.270 CEST - 7 - 23798 - - @ LOG: checkpoint
complete: wrote 3 buffers (0.0%); 0 transaction log file(s) added, 0
removed, 0 recycled; write=0.000 s, sync=0.001 s, total=0.005 s; sync
files=1, longest=0.001 s, average=0.001 s; distance=12 kB, estimate=12 kB
2016-04-24 14:34:32.272 CEST - 8 - 23798 - - @ LOG: MultiXact member
wraparound protections are now enabled
2016-04-24 14:34:32.274 CEST - 21 - 23252 - - @ LOG: database system
is ready to accept connections
2016-04-24 14:34:32.274 CEST - 1 - 23802 - - @ LOG: autovacuum
launcher started
If I increase the log level:
postgres=# alter system set log_min_messages='INFO';
ALTER SYSTEM
... I additionally get this:
2016-04-24 14:43:56.265 CEST - 1 - 24539 - [local] - postgres@postgres
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed
database context to 'ds2'., Server: WSCORE\SQL2014, Process: , Line: 1,
Level: 0
2016-04-24 14:43:56.265 CEST - 2 - 24539 - [local] - postgres@postgres
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed
language setting to us_english., Server: WSCORE\SQL2014, Process: ,
Line: 1, Level: 0
2016-04-24 14:43:56.268 CEST - 3 - 24539 - [local] - postgres@postgres
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed
database context to 'ds2'., Server: WSCORE\SQL2014, Process: , Line: 1,
Level: 0
2016-04-24 14:43:56.268 CEST - 4 - 24539 - [local] - postgres@postgres
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed
language setting to us_english., Server: WSCORE\SQL2014, Process: ,
Line: 1, Level: 0
The OS is (64bit):
postgres@pgreporting:/home/postgres/ [PGREP] cat /etc/centos-release
CentOS Linux release 7.2.1511 (Core)
Any ideas?
File an issue here:
https://github.com/tds-fdw/tds_fdw/issues
Thanks in advance
Daniel
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
