Table Of Contents

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.

Yap that's right. PostGIS 2.0.0 is finally out the door. It took us Two years and 2 months, a super long incubation for us, but we did it and just in time for Javier's Where 2.0 2.0 Talk.. Paul has some border-line R rated pictures of the birthing process.

We have windows 32 binaries posted for those adventurous enough to taste the cookies while they are hot. We are working on the windows 64-bit binaries. Those should be out tomorrow. We'll be working in the coming week to get the installers ready to put up so they are available via Stack Builder. We'll probably put up the 32-bit ones first, hopefully followed shortly by the 64-bit ones. You should see PostGIS 2.0.0 soon on Yum as well. Devrim is cooking :).

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 FDW   PostgreSQL 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.

One of the things people have complained about for quite some time is that postgis is installed in the public schema by default and it's difficult to move after the fact. With now over 900 functions types, etc, in the 2.0.0 release that is a lot of cluttering of workspace. Now that postgis 2.0.0 is packaged as an extension, you can move all those functions etc. to another schema with the ALTER EXTENSION command. PgAdmin even throws a nice GUI on top to allow you to do this with some mouse maneuvering if you prefer the guided way. This might very well be my most favorite usability feature, because if things don't work out you can just move it back to public. I've been hesitant to do this before because well it was harder and I have a lot of 3rd party apps I work with and fear one of them hard-coded public.geometry somewhere. With extensions I can easily revert if it doesn't work out.

I've done this with some of my databases and been testing out how it works. So far so good. Here is how you do it.

CREATE SCHEMA postgis;
ALTER DATABASE your_db_goes_here SET search_path="$user", public, postgis,topology;
GRANT ALL ON SCHEMA postgis TO public;
ALTER EXTENSION postgis SET SCHEMA postgis;

On a somewhat unrelated side note aside from the fact it has to do with postgis not being in same schema as geometry table is someone mentioned in PostGIS newsgroup recently that is an issue if you are using conditional triggers. That is that if you have a conditional when trigger it can't find the geometry when you restore the database because of the way the restore process changes search_path.

I'm expecting the extension model to significantly simplify PostGIS upgrades in the future, because since the functions don't get backed up, they don't get in the way when you do a hard upgrade. Hard upgrade will simply reduce to just restoring your database.

As promised in our prior article: ODBC Foreign Data wrapper on windows, we'll demonstrate how to query SQL Server using the Foreign Data Wrapper. This we are testing on windows. As far as querying SQL Server / PostgreSQL goes, the Foreign Data Wrapper still lacks many features that the SQL Server Linked Server approach provides. The key ones we find currently lacking: ability to do updates and reference a table directly from server without knowing underlying structure. That said the Foreign data Wrapper approach has possiblity to support a lot more data sources with ease. We'll demonstrate in subsequent articles using the www_fdw to query web services which we've been playing a lot with and the often packaged in file_fdw. Enough of that let's start with a concrete example.

Warning, this is not production ready, but seems like a very promising start and with more testing can become very robust. Although we are demonstrating odbc_fdw on windows, it is supported on Unix via the UnixODBC, but the data sources you can query will probably be different. I'm really looking forward to how the FDW technology in PostgreSQL will push the envelop. I've been playing around with the www_fdw as well and been impressed how easily it is to query webservices with SQL. A very ah-hah moment.

How to Install ODBC FDW handler and Setup Foreign Server Connection

1. Copy the odbc_fdw binaries in your PostgreSQL install folder -- for windows we have these for both PostgreSQL 9.1 32-bit and PostgreSQL 9.1 64-bit in our windows FDW packages. PostgreSQL windows 32-bit 9.1 FDWs and PostgreSQL windows 64-bit 9.1 FDWs We'll be adding more fdws to these zip packages as we experiment with new ones.

2. Install the ODBC FDW in your PostgreSQL with SQL Statement: CREATE EXTENSION odbc_fdw;
3. Setup a System DSN entry via ODBC manager. If you are running PostgreSQL 32-bit on a windows 64-bit machine, the ODBC manager you want is the one in C:\Windows\SysWOW64\odbcad32.exe. For postgresql 64-bit or postgresql 32-bit on 32-bit windows, its the one in C:\windows\System32\odbcad32.exe. I'm running SQL Server 2008 so I used the SQL Client 10. I named the DSN something meaningful so I know its specifically for postgres e.g. LocalSQL2008ForPg.
4. Next create a Foreign Server that uses this DSN connection CREATE SERVER localsql2008 FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'LocalSQL2008ForPg');

5. Next create a User Mapping that maps a postgres role to a SQL Account. It doesn't need to be a specific postgres user, you can map a whole role group to one SQL Server account and in fact define multiple. Note that the account in the username has to be a valid account on SQL Server and have access to the tables you'll be foreigning.

   CREATE USER MAPPING 
      FOR postgres
      SERVER localsql2008
     OPTIONS (username 'pg_reader',password 'helosqlserver!');

Creating Foreign Tables: The basics

Now we are ready to create tables to query. You can place your Foreign Tables in any postgres schema you want. I like to put ones from the same SQL database in same schema. So for example if I want to query my SuperStore sql server database, I'll create a schema in my postgres database called super_store like shown below. Observe that you don't need to select all columns of a table, you could have a shortened version of your table in postgres that has just the columns you want to select.

Next create the table

CREATE FOREIGN TABLE super_store.orders
   (orderid integer NOT NULL
    , customerid varchar(20) NOT NULL )
   SERVER localsql2008
   OPTIONS (database 'superstore', schema 'dbo', sql_query 'SELECT orderid, customerid
  FROM orders', sql_count 'select count(orderid) from orders');

More challenging table -- information_schema.tables

If I'm going to have a remote server, I'll like to have a catalog of tables. Luckily SQL server has an information_schema.tables similar to postgresql, but the types don't seem to map well and even will CRASH your PostgreSQL server with an Opps! exclamation. work around we found was to do this:

CREATE FOREIGN TABLE super_store.is_tables
(table_catalog character varying(128) ,
    table_schema character varying(128) ,
    table_name character varying(128) ,
    table_type character varying(128) )
   SERVER localsql2005
   OPTIONS (database 'superstore', sql_query 'SELECT CAST(TABLE_CATALOG As varchar(128)) As table_catalog
   , CAST(TABLE_SCHEMA As varchar(128)) As table_schema, CAST(TABLE_NAME As varchar(128)) As table_name
   , CAST(TABLE_TYPE As varchar(128)) As table_type
   FROM information_schema.tables', sql_count 'select count(TABLE_NAME) from information_schema.tables');

Querying the tables

The beauty of the FDW approach is you can query the foreign tables much like you can with other tables. Caution must be taken since the planner doesn't know much about the other side. So for example to pull a listing of views from our super_store.is_tables foreign table.

Dropping Foreign tables

To drop a foreign table, which it seems you must if you need to change structure, you can do a:

DROP FOREIGN TABLE IF EXISTS super_store.is_tables;

Gotchas

I must stress that this is not a production FDW. So you will have issues, couple we have observed with expermenting with it.

• It doesn't like integer columns that have NULLs. So we had to declare our nullable integer fields as varchar instead of integer. Otherwise we get an error Can't CAST NULL to integer.
• Case sensitivity. It's best to lowercase all the fields in your sql_query even if they are mixed case in SQL Server. If you don't the mapping fails and you end up with blank data in columns when you query.
• Some tables just cause crashing. Haven't figured out that -- we just get a Opps message and the postgresql service crashes. Perhaps an issue with unicode or exotic types. So casting is necessary as we did in the information_schema example.
• The driver doesn't give any errors when you have typos in your SQL or invalid mappings. To find these out, we had to run them in SQL Server to confirm they run.