search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
381 stars 102 forks source link

Error when trying to get data from sys.sequences #209

Open adrianboangiu opened 5 years ago

adrianboangiu commented 5 years ago

Hello,

I have successfully imported a schema and retrieved data from a foreign SQL Server database. I was unable to retrieve data from a "system" table. I want to get the current values of the sequences. I declared a foreign table to get the values from sys.sequences in the following two ways: 1. create foreign table mssql_coswin.user_sequences ( name character varying(30) not null, current_value numeric not null ) server mssql_srv options (query 'select name, current_value from sys.sequences', match_column_names 'true', row_estimate_method 'showplan_all'); 2. create foreign table mssql_coswin.user_sequences ( name character varying(30) not null, current_value numeric not null ) server mssql_srv options (table 'sys.sequences', match_column_names 'true', row_estimate_method 'showplan_all'); In both cases my postgres server crashed with the same error when I try to select from the foreign table: 127.0.0.1:5432 coswin@coswin=> select * from mssql_coswin.user_sequences; 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. Time: 6802.145 ms (00:06.802) : @!>? Any ideea what is wrong or how to achieve this goal?

Thank you, Adrian Boangiu

juliogonzalez commented 5 years ago

You should start checking the logs (postgresql, system, etc).

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.

Could me a lot of things, such as:

If you can't see anything at the logs, maybe you can consider running the same query you have at the CREATE TABLE with tsql CLI utility. If you are able to run the query there, then the problem could be at tds_fdw.

Consider playing around with TDSVER variable when using tsql. If you need to force one, make sure you have it as tds_version at CREATE SERVER, as explained at the doc.

adrianboangiu commented 5 years ago

I forgot to mention the version of tds_fdw is 2.0.0-alpha.3.2

I have checked the log files and this is what I got: 2019-10-04 08:33:21 CEST pid:26045 [7-1] xid:0 LOG: server process (PID 26326) was terminated by signal 11: Segmentation fault 2019-10-04 08:33:21 CEST pid:26045 [8-1] xid:0 DETAIL: Failed process was running: copy (select format('alter sequence %s restart with %s;', name, current_value) from (select name, current_value from mssql_coswin.user_sequences where name not in ('SEQ_TBL_TRACE_TRIGGER') order by name) mssql_sequences) to '/tmp/tmp.1ROcD9UybC/mssql2pg_seq_transfer.sql'; 2019-10-04 08:33:21 CEST pid:26045 [9-1] xid:0 LOG: terminating any other active server processes 2019-10-04 08:33:21 CEST pid:26069 [1-1] xid:0 WARNING: terminating connection because of crash of another server process 2019-10-04 08:33:21 CEST pid:26069 [2-1] xid:0 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. 2019-10-04 08:33:21 CEST pid:26069 [3-1] xid:0 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2019-10-04 08:33:21 CEST pid:26045 [10-1] xid:0 LOG: all server processes terminated; reinitializing 2019-10-04 08:33:21 CEST pid:26347 [1-1] xid:0 LOG: database system was interrupted; last known up at 2019-10-04 08:29:31 CEST

From the reasons you mention I would exclude the following three since I am able to retrieve data from ordinary tables. Wrong FreeTDS configuration. The FreeTDS installed does not support the protocol the SQL server is using. Wrong CREATE SERVER.

I have postgres 10 and according to the tds_fdw doc I cannot use the stable release 1.0.8 since for installing free_tds I don't have it under the option A (Yum released versions) and I cannot obtain it under option B (compile tds_fdw) which apply only for postgres 11.