From: | James Foreman <jforeman(at)airasiaexpedia(dot)com> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Problem with FDW in pgadmin |
Date: | 2012-08-24 10:07:47 |
Message-ID: | 35FF2D1AE6891E40A05F561A4E62404C043A235CF7@CHC-CCR01.SEA.CORP.EXPECN.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2012-08-24 14:08:34 | Re: Canot access PostgreSQL via psql -h (Vmware Fusion) |
Previous Message | Andreas Schmitz | 2012-08-24 05:14:13 | Re: Canot access PostgreSQL via psql -h (Vmware Fusion) |