One of the new features in PostgreSQL 9.1 that we've been meaning to try is the new foreign data wrapper support.
Now that we are in compile mode gearing up for releasing PostGIS 2.0.0 for windows (both 32 and 64-bit), we thought we'd give the odbc_fdw a try trying to compile on windows. Last we tried we weren't successful because
we couldn't get past the -lodbc required step.
It turns out there is an easy fix to the ODBC dependency issue and I'm not sure I changed the line right. In the makefile we changed -lodbc to -lodbc32. This was needed for both compiling 32-bit as well as the 64-bit. We compiled the 64-bit version under our Mingw-64 chain
and 32-bit under our old Mingw gcc 3.4.5. Sadly we still don't have our mingw64 (compile for windows 32-bit compile up yet). Our ming64 for windows 32 can compile the 9.2 development branch but not the 9.1.3. Go figure. Anyrate to make a long story short -- we have 32-bit binaries for PostgreSQL (you can use in VC++ builds) and 64-bit binaries as well that you can use for the VC++ EDB builds for those who are interested in experimenting.
PostgreSQL 9.1 Windows 32-bit ODBC FDWPostgreSQL 9.1 Windows 64-bit ODBC FDW
So far we've tried the PostgreSQL 64-bit data wrapper against a SQL Server 2005 DSN and it seems to work fine. Have yet to try it on other ODBC sources.
We'll write up a more detailed article describing how to make the connections.
There is one trick to getting Mingw64 compiled PostgreSQL extensions to work with the Windows 64-bit EDB builds, and that is that when you compile your PostgreSQL under mingw64,
you have to configure with option ----disable-float8-byval as we noted in our PostGIS Window 64 build instructions.
UPDATE We have PostGIS 2.0.0 available for both 32-bit and 64-bit windows PostgreSQL. We are wroking on getting the installers out
This past week has been very nerve racking but also exciting. We have successfully compiled PostGIS under the mingw64 chain and built a PostGIS windows 64-bit
for 2.0 (and 1.5), that can install under the Enterprise Db VC++ 64-bit builds of PostgreSQL 9.1. We haven't tried on 9.0, but we assume that should be fairly trivial.
Note only that, but it passes most of the PostGIS battery of tests. We first want to thank a group of people which made this all possible:
Andrew Dunstan we are greatly indebted to for making it possible to compile PostgreSQL under mingw64 tool chain. As much as people have whined
about wanting to compile PostGIS under a pure VC chain, this is not possible at this juncture just because a lot of the tests and other tool chains PostGIS uses for building
are too tied to the Unix build environment.
We want to thank the generous folks who provided money for our campaign so that we could funnel time from paid consulting work to focus on this effort and to prove that every little bit counts.
SpatiaLite developer Alessandro Furieri whose mingw64 compile instructions were invaluable to helping us overcome our GEOS and other compile obstacles. SpatiaLite (the OGC spatial extender for SQLite),uses much of the same plumbing that PostGIS uses under the hood, so many of the lessons he learned an provide could be put to use with our problems.
To Paul Ramsey especially and other PostGIS devs for general moral support and helping us tackle some PostGIS specific issues when compiled with mingw64. Paul demonstrated that yes you can mix VC++ built components with MingW and steps on how to do it. Part of the reason for that is the newer mingw32 seemed to crash with GEOS compiled under mingw32. Though the mingw64 chain didn't have this issue once we overcame our compile obstacle. We may in the future compare and see if compiling Geos under VC++ provides better performance and will also get us closer to having it possible to compile PostGIS fully under VC++ if people choose to. For the time being having a single tool chain that we can extract and run with is most important. We are preparing a self-standing Mingw64 tool chain with all the components needed to build PostGIS already compiled so that windows users who want to help with PostGIS need only extract to have a fully functioning postGIS dev environment and we also plan to move our mingw32 build to mingw64 chain of tools.
We hope to have a 64-bit compiled download ready next week for PostGIS 2.0.0 beta3 for people to try out. We are working on some issues with the raster2pgsql and loader/dumper guis we compiled not working right, but the core PostGIS works just fine in 64-bit and the 32-bit loader tools work fine against a 64-bit install. One thing we did notice with the 64-bit PostgreSQL is that we
can set shared_buffers much higher than the 32-bit PostgreSQL windows. On windows we could never go beyond ~700MB without it not being able to start or crashing. With the 64-bit we were able to go to 2GB. Haven't tried higher yet. We hope this will prove to be a performance boost for tasks such as geocoding that reuse a lot of the same datasets and benefit a lot from share memory.