PG 9.5.2, freetds + tds_fdw => server crash - Mailing list pgsql-general
| From | Daniel Westermann |
|---|---|
| Subject | PG 9.5.2, freetds + tds_fdw => server crash |
| Date | |
| Msg-id | 273678067.3402035.1461766285315.JavaMail.zimbra@dbi-services.com Whole thread Raw |
| Responses |
Re: PG 9.5.2, freetds + tds_fdw => server crash
|
| List | pgsql-general |
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)
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)
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)
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
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'ds2user', password 'xxxxxx');
CREATE USER MAPPING
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
,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.
!>
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
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
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
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)
CentOS Linux release 7.2.1511 (Core)
Any ideas?
Thanks in advance
Daniel
pgsql-general by date: