Re: PG 9.5.2, freetds + tds_fdw => server crash

On 04/27/2016 07:11 AM, Daniel Westermann wrote:
> Hi,
>
> I have installed freetds and can connect to the remote mssql server:
>
> postgres(at)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(at)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(at)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(at)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(at)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(at)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(at)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(dot)klaver(at)aklaver(dot)com