[postgis-users] Where to Find Help with ogr_fdw ?

[Jack Ort]

Hello! I obtained ogr_fdw from the PostGIS winnie experimental builds (
http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ ), which I
learned about here -
http://www.postgresonline.com/journal/index.php?url=archives/361-Foreign-Data-Wrappers-for-PostgreSQL-9.5-windows.html
------
I originally posted this to pgsql-novice, but their moderator thought I
might have better luck on a PostGIS forum.

I welcome any suggestions on where to turn for help!  I really need this to
work!!  The full text of my pgsql-novice post is below.

Best Regards,
-Jack Ort
------

Installed postgres 9.5.1 using EnterpriseDB binary on a Windows 2012 R2
server.

Downloaded org_fdw for x64 from PostGIS.net <http://postgis.net/>
(http://winnie.postgis.net/download/windows/pg95/buildbot/extras/ogrfdw-pg95-binaries-1.0.1w64gcc48.zip),
and copied files to 9.5 share\extension, \bin and \lib

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(at)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"
----------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160219/db76cdbf/attachment.html>