I’ve installed the odbc_fdw extension and I’ve got a DB2 ODBC driver installed. When I try the instructions at http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html to create a server object, I don’t get any errors reported, and I appear to have successfully created a foreign table, but I don’t see either the server or the foreign table in the object browser. CREATE SERVER remote_db FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'DB2DSN'); CREATE USER MAPPING FOR postgres SERVER remote_db OPTIONS (username ‘jforeman', password 'this-is-a-secret'); CREATE schema remote_query; CREATE FOREIGN TABLE remote_query.tpids (tpid int) SERVER remote_db OPTIONS (database 'DB2DSN', sql_query 'SELECT TPID FROM DM.V_TPID_DIM.TPID'); All these are apparently fine, although I can’t find the foreign table anywhere (it’s not visible in the tables in the remote_query schema) on pgAdmin. The foreign table has a few hundred rows – it’s not anything massive that should take a long time to query. Then I try SELECT * FROM remote_query.tpids And the result is *********Error ********** no connection to the server Connection reset. After that, I have no connection to the postgres server, and have to restart the machine before I can reconnect – attempting to use the FDW apparently breaks my existing connection, and prevents me creating any new ones. 2 questions: should I be able to see the foreign table object? It definitely exists, because if I were to try rerunning the CREATE FOREIGN TABLE command I get an error reporting that it already exists. Secondly, where should I look to figure out why running the select statement kills the connection, and stops me making any new connection? Thanks James |
