Postgres OnLine Journal: April 2016 / May 2016 / June 2016
An in-depth Exploration of the PostgreSQL Open Source Database

Table Of Contents

From the Editors
What's new and upcoming in PostgreSQL
PostgreSQL Q & A

From the Editors


PGConfUS 2016 PostGIS slides and tutorial material

We gave a PostGIS Intro Training and a PostGIS talk at PGConfUS 2016 in Brooklyn, New York and just got back. A number of people asked if we'd make the slides and material available. We have these posted on our presentation page: and will be putting on the PostgreSQL Wiki as well in due time. There will be a video coming along for the talk, but the training was not recorded.

We also have two more talks coming up in North Carolina in Early May at FOSS4G NA 2016 - one on PostGIS Spatial Tricks which has more of a GIS specialist focus than the top 10 talk we gave, but there will be some overlap. The other talk is a topic a couple of people asked us in training and after our talk, on routing along constrained paths. If you are attending FOSS4G NA 2016, you won't want to miss our talk pgRouting: A Crash Course which is also the topic of our upcoming book.

Just like FOSS4G NA 2015, there is a pgDay track which is PostgreSQL specific material, useful to a spatial crowd, but not PostGIS focused.

I should also add, that there will be a couple of other talks on routing (not all pgRouting) and other PostGIS topics like PostGIS topology. Couple that I'm looking forward to seeing

What's new and upcoming in PostgreSQL


PLV8 binaries for PostgreSQL 9.6beta1 windows both 32-bit and 64-bit

To celebrate recent release of PostgreSQL 9.6beta1, we've started to experiment with our favorite extensions. For starters, PLV8 (aka PL/JavaScript) binaries listed below and upcoming PostGIS 2.3.0 and ogr_fdw detailed here

Listed below PL/V8 binaries built for PostgreSQL 9.6beta1 for windows 64-bit and 32-bit. These I tested with EDB PostgreSQL 9.6 windows installs and seem to work fine and also passed all regression tests. These were built with PLV8 v1.4.8.

PL/V8 PostgreSQL 9.6 binaries

We hope windows users find these useful.

What's new and upcoming in PostgreSQL


PostgreSQL 9.6 phrase text searching how far apart can you go

We've been playing around with new phrase text feature of full-text PostgreSQL 9.6. In doing so, I was curious how big a number one can designate as max that words can be apart. I discovered thru trial and error, that the magic number is 16384 which is much bigger than I had suspected.

The tests

To test out the new full text search phrase feature, we downloaded data from We picked the data science archive since it's relatively small. Data is prepped in

Load up the data from psql with:

\encoding utf8;
\i stackexchange_posts.sql

Add full text index

ALTER TABLE posts ADD COLUMN fts tsvector;
UPDATE posts SET fts =
setweight( to_tsvector(COALESCE(tags,'') ), 'A') ||
setweight( to_tsvector(COALESCE(title,'') ) , 'B') ||
setweight( to_tsvector(COALESCE(body,'') ), 'C');
CREATE INDEX idx_posts_fts_gin ON posts USING gin(fts);

Do a sample query:

SELECT count(*)
FROM posts
WHERE fts @@ to_tsquery('social <5> ( science | scientist)') and title > '';

Up this to 20

SELECT count(*)
FROM posts
WHERE fts @@ to_tsquery('social <20> ( science | scientist)') and title > '';

Let's get crazy

SELECT count(*)
FROM posts
WHERE fts @@ to_tsquery('social <20000000> ( science | scientist)') and title > '';
ERROR:  distance in phrase operator should not be greater than 16384

and just to confirm the error is right, I did try going up one more and exactly 16384.

On a related note, we've started work on our 3rd edition of PostgreSQL: Up and Running, slated to be due out in January 2017, and hoping for pre-release around time of PostgreSQL 9.6 release. 2nd edition did not cover Full text at all, which I regreted. Full Text will have a fairly nice size showing in the 3rd edition. There will be quite a bit of coverage of 9.5 and 9.6 features in it.

PostgreSQL Q & A


PostGIS 2.2 Windows users hold off on installing latest PostgreSQL patch release

Someone reported recently on PostGIS mailing list, that they were unable to install PostGIS 2.2.1 bundle or PostGIS 2.2.2 binaries on a clean PostgreSQL 9.5.2 install. Someone also complained about PostgreSQL 9.3 (though not clear the version) if that is a separate issue or the same. I have tested on PostgreSQL 9.5.2 Windows 64-bit and confirmed the issue. The issue does not affect PostgreSQL 9.5.1 and older. I haven't confirmed its an issue with the 32-bit installs, but I suspect so too. This issue will affect OGR_FDW users and people who used our compiled WWW_FDW.

We've built new binaries for PostGIS 2.2.2 that have CURL compatible with the OpenSSL distributed with lastest PostgreSQL patch release for (PostgreSQL 9.3-9.5). These downloadable from experimental section - If you still have issues after using these, post to postgis mailing list. We'll be making new installers in a couple of weeks.

What's the issue?

In PostGIS 2.2, we started packaging CURL support with the GDAL driver. GDAL services both PostGIS raster and OGR_FDW extensions packaged with PostGIS 2.2 and enabling curl allowed for a couple more popular raster and vector drivers to be exposed. Since our clients needed SSL support and we needed SSL support with Curl, we compiled CURL with SSL support. However EDB already ships OpenSSL, so we had compiled against a version of OpenSSL that is compatible with what they shipped with PostgreSQL and do not ship it with our binaries. In PostgreSQL 9.5.2 (and I suspect other versions released in last round of releases), the OpenSSL libraries libeasy32.dll and ssleay32.dll got upgraded to 1.0.2g (9.5.1 shipped with 1.0.2f which in theory should be compatible with 1.0.2g since they look like micro differences), but for whatever reason aren't. Might have to do with something else. Anyrate the error you get when you try to do.

is: ERROR: could not load library "../9.5/lib/rtpostgis-2.2.dll":The specified procedure could not be found. Since the www_fdw.dll and ogr_fdw.dll were also compiled with Curl support, I suspect you'll run into the same issue.

How do I fix it now?

If you already installed the latest PostgreSQL 64-bit patch release and are running into this issue, we do have a hot fix which seems to work but haven't throughly tested. We have recompiled the libcurl-4 with the same version of OpenSSL 1.0.2g that ships with latest EDB binaries and that seems to do the trick. To fix your install, replace the libcurl-4.dll packaged with PostGIS 2.2, with the version in On quick tests, this seems to still work okay with PostgrSQL 9.5.1 as well.

We are working on a fix now, that works with PostgreSQL 9.5.2 and is also backward compatible with PostgreSQL 9.5.1 (and other). We will create a new PostGIS 2.2.2 binary package with (latest PostgreSQL patch release). The binaries will be available in the experimental section of

We will not have the installers available for another couple of weeks or month because we want to package pgRouting 2.2.0 with that (which is right now in RC1) and also installer packaging takes a lot more time to prepare and test. Given we do this in our free-time, paying work almost always takes priority over installer packaging.

To those who have asked, if you would like to contribute to our packaging efforts (and other PostGIS activties), I have set up a patreon account.



First Look at pgAdmin 4

When David Page announced pgAdmin 4, I was really excited to try it out. I was impressed I could compile it so easily on windows. I had a few bumps, but not too bad.

One of the reasons I'm excited about it is that it's built on Python and a web framework, and there is a large Python and web developer following in the GIS community, so I suspect someone will step up to the plate to add a mapviewer plugin to this so I can have a seamless PostGIS experience.

The interface is also very slick and pretty and I love the sorting and paging capability now in the query window. Check this sampling from our workshop database.

Still room in Introduction to PostGIS Training

We'll be giving a Training on PostGIS in this coming Monday in New York. Still slots available for signup.

If you are looking for something a bit shorter and will be attending the conference, we'll also be giving a PostGIS talk covering some things you can do with PostGIS.