Postgres OnLine Journal: June 2013 / July 2013
An in-depth Exploration of the PostgreSQL Open Source Database

Table Of Contents

From the Editors
What's new and upcoming in PostgreSQL

From the Editors


CMake support for PostGIS planned

I've been having issues with PostgreSQL error handling mostly on windows 64-bit using binaries I build with mingw64 and using under VC++ built PostgreSQL. Most of which I don't understand. As many have pointed out these issues MIGHT go away or be easier to debug if PostGIS was built with same tool chain as EDB VC++ distributions. I can't argue with that especially since its never been done.

The good news is changing my build chain from mingw64 GCC 4.5.4 to mingw64 GCC 4.8.0 has eradicated all of these issues, and even ones I had with PL/V8 that happended both in a pure mingw64(w32/w64) as well as PostgreSQL VCC (32/64 bit) environment. It did introduce this minor annoying nat, presumably because my libpq.dll now has a dependency on user32.dll (which just seems wrong). The other nat is that all the binaries I built that have c++ in them (depend on libstd++) now need to be recompiled which means my prior builds of PostGIS will not be compatible with my upcoming 4.8.0 ones. A real pain since for PostGIS/pgRouting I've got like at least 15 of those dependencies and growing. So I've decided to perform this exercise just for PostgreSQL 9.3 -- a new beginning at PostGIS 2.1 and see how it goes before I bother with 9.2, 9.1. These issues really only affect PostgreSQL 64-bit PostGIS users and as a 64-bit user, you may never have even come across them.

PostGIS moving to CMake

As some people know, PostGIS DOES NOT YET support compiling in a pure VC++ environment, though we hope to in PostGIS 2.2 or 2.3 series when we hope to start supporting CMake as our standard as ticketed here. I blame this on lack of people wanting to put the effort where their mouth is and my not pressuring because I prefer mingw64 as a build environment which works fine with a posix environment such as msys. If you really want to see VC++ supported as a build environment for PostGIS you should really start paying attention to this development and, instead of whining, roll up your sleeves, line up test/work out the kinks, and prove that YOU a VC++ developer really want this bad enough to work for it. I suspect I won't have the energy or knowledge to help out much with VC++. I suspect it will be a long and painful journey but one we have to travel to better cater to all community of developers. I plan to still stay in my little mingw64 camp though (I will do my part of trying to build PostGIS fully with CMake).

The need for cross tool support

A lot of what makes PostgreSQL special is its extendable run-time architecture and what makes it especially special to me is I can build my extensions (even an extension as huge as PostGIS) with my tool of choice (mingw64) and plug it right into PostgreSQL VC++ built by someone else and it for the most part just works. THAT IS DARN AWESOME and I would hope this coolness does not go away. The parts that don't work (usually involve C++ code) are well an inconvenience, but I still prefer suffering this over being stuck building everything myself or requiring others to eat my preferred dogfood or changing my dogfood. The big wrinkle is trying to debug these things in a mix of environments and that I admit is challenging but hope doable and hope something that others besides me are interested in doing.

From the Editors


Boston PostgreSQL Meetup September 10, 2013: PostGIS Intro Beginner

We'll be hosting the September 10th, 2013 PostgreSQL User Group meetup at Devonshire Sky Club (in the center of Boston Financial district and block away from city hall). This is the same venue we hosted the Boston OSGeo Code Sprint except this time we'll have some EnterpriseDb folks joining us. Sign-up while supplies last. Leo's got a nice cocktail and food lineup.

We'll be giving an introductory talk about PostGIS and also give a brief overview of what's packed in the PostGIS 2.1 release that is currently in beta, and that should be out by meetup time. Remember this is the first version of PostGIS to support upcoming PostgreSQL 9.3.

Not only will we have cocktails, but we'll also be giving out PostgreSQL/PostGIS door prizes.

Book writing, not one but two second editions

That's right, not only are we currently writing PostGIS in Action, 2nd Edition , but we are also writing the 2nd edition of PostgreSQL: Up and Running. Two books at once is a new first for us.

The second edition of PostgreSQL: Up and Running is going to be a little longer; expect an additional 20-50 pages (for a page count of about 200 pages) and its going to cover A LOT OF PostgreSQL 9.3 features packed with some JSON action, LATERAL moves, some programming examples with PL/V8 (aka PL/JavaScript), PostgreSQL FDW, and new parallel backup feature to name a few. The reader base we are targetting is much the same as the last: users coming from other relational databases and existing PostgreSQL users wanting to learn how to take advantage of new features of PostgreSQL; for this upcoming release: 9.2 and 9.3 are the primary targets.

Although PostgreSQL: Up and Running, 1st edition sold fairly well (a steady 300-375 book sells a month with slightly higher on the eBook sale), we did get several complaints. The complaints were conflicting. We got high points for providing a terse guide that guided you through the voluminous information in the official docs. We got dinged because our book could be read from cover to cover in a matter of hours and pointed you at the official docs section for more information. We also got dinged because we had no index, which we admit really made us irritated too. So this second edition WILL have an index, and will be a tad bit longer, but not so long that its a tome.

The other thing that we got points taken off on is that our book was not introductory enough and was too introductory. I think people who said it was not introductory enough were looking for an introduction to databases (with a PostgreSQL focus) book. These folks would probably be more satisfied reading something else first like one of PacktPub's Instant series or even a plain-old beginning SQL book. We expected readers of our book to be fairly comfortable using SQL so that we could focus our energy showcasing PostgreSQL SQL idioms/twists that are rare or non-existent in other databases; things like arrays, key value stores, json type, row as an object, aggregation of custom types, and ordereded aggregates wrapped in an sql nugget of logic as well as fundamentals of role management, permissions, and backup. The people who said it was too introductory we have no idea what they were expecting except possibly they were looking for examples of changing PostgreSQL core or building extensions which was clearly not our audience focus.

As much as some people whined about the shortness of the book, we got a lot more positive comments about the shortness and easy-read of the book. It's also much easier to keep content fresh and up to date with a shorter book. That said, next book we write we hope to be even more focussed and equally short; perhaps more niche focused, like programming in PL/V8 or PL/R. PL/V8 and PL/R are both languages optimized for a domain problems and a unique way of working/thinking that really make PostgreSQL stand out as very special from other databases. When you get down to the nitty-gritty you could write a huge book about each. Each has a dedicated and growing following of web developers and scientists. They are largely ignored by other PostgreSQL books. Even the PostgreSQL manual, as large as it is, has nothing to say about these extensions because they are not part of core.

From the Editors


pgRouting 2.0.0rc1 and PostGIS 2.1.0rc1

This past day, the pgRouting development team released pgRouting 2.0.0 RC1 just steps after PostGIS 2.1.0 rc1. Last week PostGIS project released RC1 of upcoming 2.1.0 PostGIS 2.1.0 RC1.

Now only thing left to make this a 3fer sweetened pot is if strk would move his butt a little faster to get out GEOS 3.4.0.

Also in the news I am now on the GEOS Project Steering Committee and pgRouting development team. Just waiting for my commit keys for GEOS so I can help out with the GEOS release. Yap that's right threaten to help out to speed things up and they make you a development team member or a project steering committee member. I am very proud to be a member of all 3 teams and will do my best to keep all 3 aligned with each other and also PostgreSQL changes. In the past we've stepped on each others toes, e.g making changes in PostGIS 2.0 that broke pgRouting or not testing changes in upcoming PostgreSQL releases and changing accordingly. I hope to keep tabs on these issues and proactively fix them.

For those who wanted a quick tutorial on pgRouting 2.0, I was meaning to write one, but Anita Graser beat me to it. Her pgrouting 2.0 for windows quick guide has a windows flavor, but since pgRouting 2.0 now supports the PostgreSQL extension model, the installation process is much the same regardless what OS you are on if you are running PostgreSQL 9.1+. The rest of the tutorial is QGIS based which is a desktop GIS tool supported on all OS I can think of so definitely worth a read even if you are not on windows.

From the Editors


In defense of being blunt and to the point

This is partly in response to Josh's blog entry Calling Bullsh*t in Open Source communities and Sarah Sharp's plea for No more verbal abuse. I think Linus Torvalds is being treated unfairly and is being abused for his frank, no bullsh*t, flowery quiky style of stating it. Many have accepted the axiom that Linus is a jerk, childish and needs to conform to the professional standard simply because we are too lazy to analyze the facts for ourselves and like many public figures Linus likes to give people a good show.

Quotes from Linus

"Because if you want me to "act professional," I can tell you that I'm not interested. I'm sitting in my home office wearing a bathrobe. The same way I'm not going to start wearing ties, I'm *also* not going to buy into the fake politeness, the lying, the office politics and backstabbing, the passive aggressiveness, and the buzzwords. Because THAT is what "acting professionally" results in: people resort to all kinds of really nasty things because they are forced to act out their normal urges in unnatural ways."

So I read that as "Let's lynch Linus, cause he refuses to wear a noose of the oppressor around his neck like everyone else". Any one of you who've worked in an oppressive office environment know EXACTLY what he's talking about.

In his Defense of being accused of being a verbal abuser and

"I definitely am not willing to string people along, either. I've had that happen too—not telling people clearly enough that I don't like their approach, they go on to re-architect something, and get really upset when I am then not willing to take their work."

I curse when there isn't any argument. The cursing happens for the "you're so f*cking wrong that it's not even worth trying to make logical arguments about it, because you have no possible excuse" case.

.. and sometimes people surprise me and come back with a valid excuse after all. "My whole family died in a tragic freak accident and my pony got cancer, and I was distracted". And then I might even tell them I'm sorry. No. Not really. Linus

Victim of his wrath defending him.

She simply doesn't agree with Linus brand of Tough love which is a brand that I share -- perhaps because I'm in the same age group as Linus.

So my plea is, please let us not get into the business of training people on the ART OF NICENESS and SENSITIVITY and studying the loop holes so that we can use that as a weapon to stab each other behind closed doors and private emails where NO ONE CAN HEAR YOU CRYING FOR HELP.

I think I am more qualified than most to judge verbal abuse and oppression when I see it. I'm the daughter of a black Nigerian man and a white American mother. I was born in Nigeria and spent my youth there. I've been traumatized all my life both verbally and physically from all sides for being different in all kinds of ways. This trauma is most often inflicted by women who've told me to be quiet because I do not have the social skills to grasp what is happening. My lesson learned is your oppressor may be someone that looks just like you (or thinks he/she understands what you are feeling) and your ally just MAY BE a purple dragon living in a cave wearing a bathrobe.

Lets analyze the facts. I don't think it was Linus' cursing -- that's just comic relief and Sarah did some herself. In fact Linus, doesn't curse or scream at strangers or newbies. He curses at people that he knows well. True he does go over at times, but everyone does. It wasn't so much even him saying this: (which he apologized for)
or this:

It was this:

So Greg, if you want it all to change, create some _real_ threat: be frank with contributors and sometimes swear a bit. That will cut your mail queue in half, promise!

Greg, the reason you get a lot of stable patches seems to be that you make it easy to act as a door-mat. Clearly at least some people say "I know this patch isn't important enough to send to Linus, but I know Greg will silently accept it after the fact, so I'll just wait and mark it for stable.

You may need to learn to shout at people.

and Sarah's response:

Seriously, guys? Is this what we need in order to get improve -stable? Linus Torvalds is advocating for physical intimidation and violence. Ingo Molnar and Linus are advocating for verbal abuse.

Not *fucking* cool. Violence, whether it be physical intimidation, verbal threats or verbal abuse is not acceptable. Keep it professional on the mailing lists.


Sometimes the truth hurts, but sugarcoating it in niceties and polite language clouds the meaning at best and masks an underlying viciousness at worst.

What's new and upcoming in PostgreSQL


PostGIS 2.1.0beta3 released

The PostGIS development team is proud to release a feature complete beta version of upcoming PostGIS 2.1.0. As befits a minor release, the focus is on speed improvements, more features, and bug fixes. While this beta release is feature complete, we expect some bugs and we'd appreciate it if you test it before final release and report back with any issues you run into so we can have a smooth release.

PostgreSQL versions supported in this release are PostgreSQL 9.0-9.3. PostGIS 2.1 is the first minor release to support PostgreSQL 9.3.

If you are currently using PostGIS 2.0+ (compiled with raster support) and PostgreSQL 9.1+, you can go the soft upgrade path:

ALTER EXTENSION postgis UPDATE TO "2.1.0beta3";

Users of 1.5 and below will need to go the hard-upgrade path.

Best served with a bottle of GEOS 3.4.0dev (still in development) and PostgreSQL 9.3beta2 (which will also be released in next week or so).

Key features of upcoming PostGIS 2.1.0 were outlined in our beta2 release

This release contains bug fixes completed since 2.1.0beta2 release

Bug Fixes
    #2185 postgis: Geometry output functions crash server with invalid WKT (windows 64)
    #2213 postgis: undefined symbol: jsontokenererrors
    #2336 tiger geocoder: FIPS 20 causes wildcard expansion to wget all files
    #2339 postgis: PostgreSQL 9.3beta1 EDB regress failures
    #2359 raster: raster2pgsql - large collection of examples of different bugs in -l (overviews)
    #1959 build/upgrade/install: Upgrading with extensions getting a row is too big
    #1850 build/upgrade/install: upgrade scripts for postgis 2.0 and 2.1
    #2356 build/upgrade/install: PostGIS extension files no longer installing
    #2334 build/upgrade/install: Cannot upgrade from 2.0 to 2.1 (non-extension upgrade)
    #2279 build/upgrade/install: Can't upgrade PostGIS from 2.0
    #2351 postgis: Postgis 2.1 st_distance between geographies wrong
    #2262 postgis: autocast to box causing issues with removed functions
    #2300 postgis: Cleanup ePub formatting and generation
    #2315 postgis: geographydistanceuncached: variable 'tolerance' set but not used
    #2165 postgis: ST_NumPoints regression failure with CircularString
    #2348 raster: Provide 2.0 to 2.1 upgrade path for raster
    #2346 topology: Fix topology tests after GEOSSnap fixes
    #2344 buildbots: Changing in regress caused winnie to scream
    #2332 postgis: ST_GeomFromWKB crashes PostgreSQL server
    #2329 tiger geocoder: bug on backup
    #2326 documentation: [raster]: ST_ColorMap doesn't respect nodata value
    #2323 sfcgal: doc po builds missing sfcgal items
This moves us to our RC phase
Thank you for your support
Team PostGIS