Installed postgres 9.5.1 using EnterpriseDB binary on a Windows 2012 R2 server.
Using DependencyWalker, kept adding to my PATH variable for missing DLLs until my CREATE EXTENSION command succeeded:
------
create extension ogr_fdw schema my_extensions;
CREATE SERVER xyz
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'ODBC:xxxxx/yyyyy@MedidataVPN',
format 'ODBC' );
create schema if not exists xyz;
import foreign schema ogr_all
from server xyz into xyz;
All of this worked, although the CREATE SERVER command took about 5 minutes.
----------------------------------------------------------------------------------------------
Using a VPN connection to a remote SQLServer database.
Defined an 64-bit ODBC System DSN using the SQL Server driver (driver version 6.03.9600.16384)
Chose a simple 91-record table as my query target. SSMS shows the design as:
/****** Object: Table [dbo].[ObjectTypeR] Script Date: 2/17/2016 5:30:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ObjectTypeR](
[ObjectTypeID] [tinyint] NOT NULL,
[ObjectName] [varchar](100) NOT NULL,
CONSTRAINT [PK_ObjectTypeR] PRIMARY KEY CLUSTERED
(
[ObjectTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/***************************************/
This query run on the Windows server that hosts postgres under Powershell using ODBC gives an instant response:
SELECT * FROM [dbo].[objecttyper]
------------------
postgres' information_schema.columns shows this for the foreign table (I wonder if that huge character_octet_length is a problem?) - OGR_FDW apparently adds the fid and geom columns:
| table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length |
| dbo_objecttyper | fid | 1 | | YES | integer | | |
| dbo_objecttyper | geom | 2 | | YES | bytea | | |
| dbo_objecttyper | objecttypeid | 3 | | YES | character varying | | 1073741824 |
| dbo_objecttyper | objectname | 4 | | YES | character varying | | 1073741824 |
---------------------------
Running the equivalent query under pgAdmin III using the foreign server defined above consistently takes > 10 minutes(!):
EXPLAIN ANALYZE select * from xyz.dbo_objecttyper;
"Foreign Scan on dbo_objecttyper (cost=25.00..1025.00 rows=1000 width=100) (actual time=48.275..49.171 rows=91 loops=1)"
"Planning time: 316745.992 ms"
"Execution time: 316735.626 ms"
----------------------------
Thank you in advance for any suggestions to improve my response times! If another FDW would be better and is available for 64-bit Windows, I would certainly try it.
Best Regards,
-Jack Ort
