Re: PG 9.5.2, freetds + tds_fdw => server crash - Mailing list pgsql-general

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@aklaver.com