Segmentation fault

[gabfl]

Hi @manio,

I tested your version of odbc-link on both PostgreSQL 9.3 and 9.5 and I am getting a Segmentation fault on odbclink.connect.

I am installing PostgreSQL on an EC2 instance Ubuntu Server 16.04 LTS with:

apt-get update
apt-get -y install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

apt-get update
apt-get -y install postgresql-9.3 postgresql-server-dev-9.3

Then creating a test db:

create database test;

Then I clone and compile your program:

apt install make gcc iodbc odbc-postgresql unixodbc unixodbc-dev

cd
git clone https://github.com/manio/odbc-link.git
cd odbc-link

USE_PGXS=1 make
USE_PGXS=1 make install

su postgres
psql -f odbclink.sql test

Then I connect to the db and run odbclink.connect (I intentionally left 'dsn', 'username', 'password' because its irrelevant in the issue):

root@ip-10-0-1-134:~# su postgres
postgres@ip-10-0-1-134:~$ psql
psql (9.3.19)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".

test=# select odbclink.connect('dsn', 'username', 'password');
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.
!> 
!> 
!> 

Here are the PG logs:

2017-09-22 14:01:59.055 EDT [1570] LOG:  server process (PID 14339) was terminated by signal 11: Segmentation fault
2017-09-22 14:01:59.055 EDT [1570] DETAIL:  Failed process was running: select odbclink.connect('dsn', 'username', 'password');
2017-09-22 14:01:59.055 EDT [1570] LOG:  terminating any other active server processes
2017-09-22 14:01:59.057 EDT [14328] WARNING:  terminating connection because of crash of another server process
2017-09-22 14:01:59.057 EDT [14328] 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.
2017-09-22 14:01:59.057 EDT [14328] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2017-09-22 14:01:59.059 EDT [1570] LOG:  all server processes terminated; reinitializing
2017-09-22 14:01:59.068 EDT [14342] postgres@test FATAL:  the database system is in recovery mode
2017-09-22 14:01:59.069 EDT [14341] LOG:  database system was interrupted; last known up at 2017-09-22 14:01:32 EDT
2017-09-22 14:01:59.078 EDT [14341] LOG:  database system was not properly shut down; automatic recovery in progress
2017-09-22 14:01:59.080 EDT [14341] LOG:  record with zero length at 0/17A5AC0
2017-09-22 14:01:59.080 EDT [14341] LOG:  redo is not required
2017-09-22 14:01:59.101 EDT [14341] LOG:  MultiXact member wraparound protections are now enabled
2017-09-22 14:01:59.103 EDT [1570] LOG:  database system is ready to accept connections
2017-09-22 14:01:59.104 EDT [14346] LOG:  autovacuum launcher started

I tried initially with PG 9.5 and got the same issue. I tried with 9.3 thinking that it might be a compatibility issue with newer versions of PG.

Are you still maintaining odbc-link? Is it an issue you would be able to look into?

thanks!!

[manio]

Hello, Yeah, we are still using it in my company, but generally we now tend to use specified foreign data wrappers where appropriate. Nevertheless we are using the odbclink in many cases. We are using it mainly for Oracle and Firebird ODBC drivers. Our postgresql server versions vary - but we are using the odbclink even on PostgreSQL 9.6.1. I have to admit that this odbclink driver is very "sensitive" to some queries. I also had crashes when the odbclink plugin "was not happy" with the query, even if it was perfectly fine in the target database. Eg: I have to implicit cast firebird timestamps to varchar(24) in the odbclink query, otherwise it would crash the postgresql. But when I am aware of this glitches and the queries are well tested, then I have to admit that it is working generally stable enough for our needs.

What ODBC driver are you using? I am not sure if you are calling the odbclink.connect properly. For instance here is the sample call: select * from odbclink.connect('DSN=test-db;User=sysdba;Password=masterke;CHARSET=NONE'); or even simpler is sufficient if you have properly configured access for DSN in odbc.ini: select * from odbclink.connect('DSN=test-db');

You can also check if the ODBC part is working ok with isql tool, like this: http://www.unixodbc.org/doc/UserManual/Figure3.gif Do not test the odbclink in postgresql, until you're sure it is working properly in isql!

ps. as you can see in this repository description, I am not maintaining this plugin - I just added some fixes for PG > 9.3 version... :)

[gabfl]

Hi,

What ODBC driver are you using?

I was using this: http://www.simba.com/drivers/bigquery-odbc-jdbc/

I am not sure if you are calling the odbclink.connect properly. For instance here is the sample call:

Indeed I was missing * from but the problem is the same with the correct query:

postgres@ip-10-0-1-134:/home/gab$ psql
psql (9.3.19)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from odbclink.connect('DSN=Google BigQuery 64');
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.
!> 
!> 
!> 

You can also check if the ODBC part is working ok with isql tool, like this:

I checked with iodbctest and it is working correctly:

postgres@ip-10-0-1-134:/home/gab$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326

Enter ODBC connect string (? shows list): ?

DSN                              | Driver                                  
------------------------------------------------------------------------------
Google BigQuery 32               | Simba ODBC Driver for Google BigQuery 32-bit
Google BigQuery 64               | Simba ODBC Driver for Google BigQuery 64-bit

Enter ODBC connect string (? shows list): DSN=Google BigQuery 64
Driver: 2.0.6.1011 (Simba ODBC Driver for Google BigQuery)

SQL>
Have a nice day.

ps. as you can see in this repository description, I am not maintaining this plugin - I just added some fixes for PG > 9.3 version... :)

I understand, I will try to work with an ODBC FDW instead.

Thanks for getting back to me quickly!

[manio]

Indeed I was missing * from but the problem is the same with the correct query:

I rather meant that you was passing dsn, user and pass as separate function arguments, but I can see in the readme that it is also allowed. select * from just fetches the results of the function. Of course it should give you connection identifier not a crash...

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.

A backtrace would be very helpful, I suspect that odbclink is doing something nasty.

I checked with iodbctest and it is working correctly:

Oh, in this case the odbclink indeed "should" work.

I understand, I will try to work with an ODBC FDW instead.

Yes, you may try it as I believe that odbc-fdw may be better maintained and maybe it would just work for you. In my company we are tend to move to using oracle_fdw for Oracle, tds-fdw for MSSQL and so on (https://wiki.postgresql.org/wiki/Foreign_data_wrappers). But the odbclink has this advantage that I can just run any query in the remote database, while with the FDW that it not so obvious, because it is rather made up for obtaining remote data via so-called foreign tables.

I am not familiar with your bigdata, but maybe this is also related: https://www.cdata.com/kb/tech/bigquery-jdbc-postgresql-fdw-mysql.rst ... but attaching a MySQL into the chain doesn't make much sense for me...

[gabfl]

I had tried Cdata's odbc driver but not the jdbc driver that maps BQ to MySQL. Turns out its working pretty well, I will use that

Thanks for the link