Table Of Contents

As David Page already noted, Leo and I are taking over responsibility of building PostGIS windows one-click installers/stack builder from Mark Cave-Ayland. The PostGIS 1.4 windows packaging was a little late in coming this time since it was our first and also some things changed in the PostGIS packaging for 1.4. Even so we made some mistakes such as statically compiling libproj in with the postgis-1.4.dll and forgetting some new images in the packaged html help, which we will fix in 1.4.1 release.

Mark will still be providing a supporting role and helping out when we screw up or helping us if we run into compile issues as we go along so he's not going away; he will be a great safety net. When Mark started his role a long time ago, he was as many would like to say "Very entrenched in the dark side," and over the years, he has seen the light. As a result, these moments of catching issues in the PostGIS release cycle that effect windows users such as troubleshooting the memory bug in the loader files that affected Windows Vista users and testing on various Windows OS, has fallen on us, because well we have access to all windows os.

It also became painful for Mark to walk in the shadow of darkness once he had seen the light. Luckily we are still windows addicts so this having to constantly test on Windows and building for Windows is something we would naturally do anyway and yes as shocking as it sounds we do run some production PostgreSQL apps on windows and it works pretty well, thank you very much. We don't expect this to change any time soon.

As part of this change, we hope to provide more interim windows builds of PostGIS so windows users can experiment with future releases before they come out. Yes compiling on windows is a tad bit more difficult than on Linux. These PostGIS windows experimental builds can be found http://postgis.net/windows_downloads

Main changes in PostGIS

• The PostGIS steering committee has agreed to be good and not be adding new functions between micro releases of PostGIS as we have done in the past and as we've been smacked around for. As part of that change, from PostGIS 1.4 moving forward each micro version will overwrite the previous micro version in the MS Windows registry. E.g. 1.4.1 will overwrite 1.4.0 so no need to uninstall the old and reinstall to get rid of registry junk. Just install on top of your existing 1.4.
• As of PostGIS 1.4 it is possible to run different versions of PostGIS in different databases on teh same PostgreSQL server install since the .so/.dll from minor to minor have unique names (naming is postgis-1.4.so (postgis-1.4.dll), postgis-1.5.so etc). This is mostly useful for testing and comparing different versions of PostGIS before you officially upgrade and if you have several different spatial apps using different databases, you don't risk breaking them all at once.
• PostGIS is now an official incubation project of OSGEO. Things are still being drafted. But I guess that means our PostGIS defacto steering committee composed of Kevin, Paul, Mark, and myself is now more or less official.

On the topic of Writing

We just submitted first revision of chapter 6 of our upcoming book PostGIS in Action and are working on our second revision of that chapter. The second revision will most likely be split into 2 chapters because its too long for a single chapter. It will cover loading/exporting various format types of spatial data and everything you never wanted to know about spatial reference systems.

We also submitted the second revision of the upcoming RefCardz DZone Essential PostgreSQL. Not sure when that will be released since we are already a bit behind schedule. I imagine we will have a couple more iterations of that before its done. It is really hard to cram into six pages the most important things that every PostgreSQL user should know and all the exciting stuff in PostgreSQL without feeling like you are leaving something critical out.

I'm always amazed how people manage to write books by themselves. If you think for each chapter you go thru 4-5 revisions and each subsequent revision is about 20-50% of the previous, that's a lot. Leo and I are writing together and its still a struggle though its been fun. I guess writing together has its own challenges because you are constantly cutting each others stuff out and arguing over what is important and what is not. We have managed to not have too many heated debates that would spoil dinner. This is good since Leo does the cooking because he thinks I'm too absent-minded to be capable of complex tasks like cooking.

Just read that Microsoft has formed a new foundation called CodePlex foundation, presumably to spinoff their Code plex site and allow it to stand separately from Microsoft. The mission appears to be to allow an easier avenue for developers working for proprietary software companies to contribute to open source projects.

Monty has some details about this on his blog The CodePlex Foundation: Why is Microsoft founding it?.

The line up of people they have on their advisory board (including Monty) and board of directors is interesting CodePlex About. I'm particularly happy that Miguel De Icaza is on the board since he is one of my favorite people and I believe shares my pragmatic ideals on the synergy between open source and non-open source software. I wonder what it takes to get on this board. It would be really nice if someone in the PostgreSQL community were on this board just to ensure the needs of the PostgreSQL community (especially our growing number of windows users) is well represented.

As to the argument of Monty's that software for sell is dying, not sure I quite agree though haven't given it much thought. Certainly I would like to think that service for sell is rising since that's the business we are in and enjoy most. One thing I believe is that software is getting more complicated and people expect more. With that said even as a company that sells software, you would be foolish not to try to leverage on the open source software out there that fits nicely into your codebase. You just won't be able to compete even with the sole proprietor next door who is with it.

Microsoft reinventing itself

First I would first like to give this caveat that yes I am a pro Microsofter. Always was, and really can't see myself changing. Over the years I have seen myself change along with the face of Microsoft. I no longer use just Microsoft products, but partake too of all the good things that the world has to offer, much of which thank goodness is free in the sense of not costing as well as not having restrictive uses, and fitting often more nicely with our clients codebase and general requirements than closed source software.

I have observed over the years, that Microsoft has been embracing PHP and basically trying to make the world forget about ASP. This all seemed puzzling to me at first and then realized it makes perfect logistical sense. ASP is a dead product and even as the owner of said dead product, you want to get your customer off of it as quickly as possible so you don't have to waste money supporting it. That is why the PostgreSQL and other communities push people to upgrade. All those die-hard ASP fans hated ASP.NET. ASP.NET was too complicated for their needs. Ironically the transition for many people from ASP to PHP was a much easier one than from ASP to ASP.NET. PHP also had the advantage of running pretty consistently on Linux as it did on Windows. Work still needs to be done to allow ASP.NET that luxury. I happen to like both for very different reasons and workflows.

Last week I noticed what appears to be an acceleration in whatever company Microsoft is turning into. I wanted to try out the new SQL Server 2008 Reporting Services by downloading the free SQL server 2008 Express with Advanced Services. Last time I downloaded it the install of SQL Server 2008 express was a bit painful, but this time I spent a lot of time puzzled. You see I had to download this thing called a Web Platform Installer. It recognized the dependencies I was missing and dowloaded it for me. What puzzled me however were these screens.

The strange thing is that a lot of the PHP side of products are MySQL centric and don't even work with SQL Server to my knowledge and much of it doesn't work with PostgreSQL (except for Moodle, Gallery, the variant of Drupal they packaged Acquia Drupal - claims to only support MySQL. What happened to plain Drupal that supports PostgreSQL). So on the one hand I was glad that Microsoft was embracing open source and on the other hand I was irritated by the choice of offerings. I'm not sure if I can blame Microsoft for the lack of applications I can actually use. I'm sure a poll was done and this is what people commonly use and being customer centric as they are that is what they put out.

One of the small little treats provided in PostgreSQL 8.4 is the new pg_terminate_backend function. In the past when we wanted to kill runaway postgresql queries issued by a database or user or hmm us, we would call the pg_cancel_backend function. The problem with that is it would simply cancel the query in the backend process, but often times the offending application would simply launch the same query again.

In PostgreSQL 8.4 a new function was introduced called pg_terminate_backend. This doesn't completely replace pg_cancel_backend, but basically does what you do when you go into say a Windows Task manager and kill the offending postgres process or on Linux, you call a kill command on a postgres process. Its nicer in the sense that you can do it all within PostgreSQL and you can use the pg_stat_activity query to help you out a bit. Also you don't run the risk as easily of killing the root postgres process and killing the postgres service all together.

Below are the ways we commonly use these functions. One of the features I really love about PostgreSQL which I miss when working with SQL Server, is the ability to call a function that does something from within a query. This feature makes SQL so much more powerful.

Cancel/Termination Examples

Slap on Wrist

    -- Cancel all queries in an annoying database
	SELECT pg_cancel_backend(procpid)
	FROM pg_stat_activity
	WHERE datname = 'baddatabase';

	-- Cancel all queries by an annoying user
	SELECT pg_cancel_backend(procpid)
	FROM pg_stat_activity
	WHERE usename = 'baduser';

Baseball bat to the head

Terminating backends is also useful for freeing up memory from idle postgres processes that for whatever reason were not released or if you need to rename a database and need to kill all live connections to a database to do so.

-- terminate process by annoying database
	SELECT pg_terminate_backend(procpid)
	FROM pg_stat_activity
	WHERE datname = 'baddatabase';

-- terminate process by an annoying user
	SELECT pg_terminate_backend(procpid)
	FROM pg_stat_activity
	WHERE usename = 'baduser';

One thing we have noticed with the baseball approach to database management is that it doesn't always play well with pooled connection like things. For example in one PHP app we had that uses pooled connections, the connections became stale. Or at least we think this is the culprit. So you sometimes have to restart the app, or it sometimes gives annoying messages to the user until the dead connections are released. With that said, we still try the slap on the wrist before reaching for the baseball bat. Its always nice to have an easy to swing baseball bat handy though.

Comparison of PostgreSQL 8.4, Microsoft SQL Server 2008, MySQL 5.1

In our May 2008 issue of Postgres OnLine Journal, we cross compared Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3. Some people mentioned well since 8.4 has now come out, shouldn't we go back and update the reference. We deliberated and decided not to. To be fair all 3 products have released new versions, so it would seem unfair to compare a newer PostgreSQL against older versions of MS SQL Server and MySQL. We have therefore decided to repeat our exercise and include parts people felt we should have covered, as well as comparing the latest and greatest stable release of each product.

People ask us time and time again what's the difference why should you care which database you use. We will try to be very fair in our comparison. We will show equally how PostgreSQL sucks compared to the others. These are the items we most care about or think others most care about. There are numerous other differences if you get deep into the trenches of each.

People have been posting comments on Reddit as well

A very long time ago, we wrote an article on how to use PostgreSQL to show the fully qualified name of an item in an inventory tree. Basically we were modeling a paper products tree. The original article can be found here Using PostgreSQL User-Defined Functions to solve the Tree Problem and was based on PostgreSQL 7.4 technology.

We'll repeat the text here for completeness and demonstrate the PostgreSQL 8.4 that solves this and more efficiently.

Suppose you are tracking supplies and have a field called si_item and another called si_parentid. The parent keeps track of what subclass a supply item belongs to. E.g. you have paper parent that has subclasses such as recycled, non-recycled. When someone takes supplies, you want to return the fully qualified name e.g. Paper->Recycled->20 Lb

CREATE TABLE supplyitem(si_id integer PRIMARY KEY, si_parentid integer, si_item varchar(100));

--load up the table (multirow constructor introduced in 8.2)
INSERT INTO supplyitem(si_id,si_parentid, si_item)
VALUES (1, NULL, 'Paper'),
(2,1, 'Recycled'),
(3,2, '20 lb'),
(4,2, '40 lb'),
(5,1, 'Non-Recycled'),
(6,5, '20 lb'),
(7,5, '40 lb'),
(8,5, 'Scraps');

--Recursive query (introduced in 8.4 returns fully qualified name)
WITH RECURSIVE supplytree AS
(SELECT si_id, si_item, si_parentid, CAST(si_item As varchar(1000)) As si_item_fullname
FROM supplyitem
WHERE si_parentid IS NULL
UNION ALL
SELECT si.si_id,si.si_item,
	si.si_parentid,
	CAST(sp.si_item_fullname || '->' || si.si_item As varchar(1000)) As si_item_fullname
FROM supplyitem As si
	INNER JOIN supplytree AS sp
	ON (si.si_parentid = sp.si_id)
)
SELECT si_id, si_item_fullname
FROM supplytree
ORDER BY si_item_fullname;

Result looks like

si_id |      si_item_fullname
------+-----------------------------
 1    | Paper
 5    | Paper->Non-Recycled
 6    | Paper->Non-Recycled->20 lb
 7    | Paper->Non-Recycled->40 lb
 8    | Paper->Non-Recycled->Scraps
 2    | Paper->Recycled
 3    | Paper->Recycled->20 lb
 4    | Paper->Recycled->40 lb

One of the most common questions people ask is Which tools work with PostgreSQL. In a sense the measure of a database's maturity/popularity are the number of vendors willing to produce management and development tools for it. Luckily there are a lot of vendors producing tools for PostgreSQL and the list is growing. One set of tools people are interested in are Database administration, ER diagramming, Query tools, and quickie application generators (RAD).

For this issue of our product showcase, we will not talk about one product, but several that fit in the aforementioned category. All the listed products work with PostgreSQL and can be used for database administration and/or architecting or provide some sort of light reporting/rapid application building suite. By light reporting/application building, we mean a tool with a simple wizard that a novice can use to build somewhat functional applications in minutes or days. This rules out all-purpose development things like raw PHP, .NET, Visual Studio, database drivers etc. Things we consider in this realm are things like OpenOffice Base and MS Access. Most of these tools are either free or have 30-day try before you buy options.

You can't really say one tool is absolutely better than another since each has its own strengths and caters to slightly different audiences and also you may like the way one tool does one important thing really well, though it may be mediocre in other respects. We also left out a lot of products we are not familiar with and may have gotten some things wrong.

If we left out your favorite product and you feel it meets these criteria, or you feel we made any errors, please let us know, and we'll add or correct it. We will be including Free open source as well as proprietary products in this mix. If we left out what you consider an important criteria, please let us know and we'll try to squeeze it in somewhere.

UPDATE: We have added more entries since we initially published this. In playing around with some of these, we discovered that more than we thought sport a drag and drop table draw line join kind of query builder, but some do it better than others. The same holds true with ER modeling relational foreign key gui dialogs within the relational diagram. Since our list is now bigger, we went back to test drive some of these to see how well they do it for PostgreSQL. So in these cases, you'll see more than a Yes/No answer and some of our personal prefences such as JOIN syntax implementation may show more than we would like.

The query builder part is probably something we are more critical of, not because we care that much for them, but that's the first thing new database users need badly. From an expert database user stance, this is only really useful if you can toggle back and forth between design and SQL view without losing your changes.

There were some also added features we noticed such as code completion and plpgsql debugger which we didn't test, but tried to put in the general admin/edit description section if we know it does it or someone commented on it.

We may have also incorrectly tagged some things No if it wasn't intuitively obvious how to do it in our 5-10 minutes of testing, so please correct us in those cases and how you do it as well.