new in postgresql   

Lots of people have been asking the never ending question of when PostGIS is going to get on the band wagon and support KNN GIST like other GIST based types trigrams, full text search etc. Well it's happened in PostGIS 2.0 and now committed. More of the gory details at Indexed Nearest Neighbour Search in PostGIS. In short this will make point / point distance searches and rankings way way faster and help also with other distance searches by providing approximations to start with.

We are still preparing the PostgreSQL 9.1 2.0 32-bit windows builds that will have this functionality and should have that ready in the next day or so.

To summarize what you can expect. We spent a lot of time discussing and were torn between a box distance operator <#> and a centroid box distance operator <->, so we ended up having both. The reason being is that for some kinds of geometries e.g. streets that aren't diagonal a box distance operator seems to be a much better approximation of distance than a centroid box distance operator. For points of course the two concepts are the same and not an approximation so point / point distance you'd be better off using the new KNN sorting than ST_Distance + ST_DWithin as we have suggested in past. I'll be doing some benchmarks in the coming weeks comparing the old way and speed differences you can expect and perhaps throwing together box and centroid cocktails that combine the two weapons into thought provoking WTFs (or as Dave Fetter would say "That's very Rube Goldberg of you").

I suspect I'll probably be sticking with <#> because I like the symbol better and I was one of the ones fighting for it :).

We attended FOSS4G this year in Denver, Colorado. Friday was a PostGIS bonanza with 5 PostGIS talks back to back including ours. The crowd was huge. All the PostGIS talks as I recall were so packed that there were not enough seats to accommodate everyone. A more comprehensive detail of the events is described on OpenGeo FOSS4G Day #5

We admit to overstuffing our slides with SQL and ran short on time at the end. Leo complained and vowed to do a better job next time. We really weren't expecting such a large crowd. Admittedly I'm all for the after conference experience which is much longer than the conference which is why I tend to make slides that are very dense. WARNING: The following slides feature SQL doing unconventional things suitable only for mature audiences. Viewer discretion is adviced.. You can check out our slides here PostGIS 2.0 the new stuff.

PostGIS 2.0.0 has inched a lot closer to completion. This past week, Paul enabled his gserialization work which changed the on disk-format of PostGIS and in return I think we'll have a much better platform to grow on. With this change we now have the 3D index and bounding box bindings in place. Say hello to the &&& operator which is like &&, but is 3D aware and comes with its own companion 3D spatial indexes. This will allow you to do true 3D bounding box searches with any of the new 2.5/3D geometries we have in place for PostGIS 2.0.0. We are still noodling out the semantics of boxes. Read Paul's call for action on the The Box Plan?, if you are interested. PostgreSQL 8.4 is the lowest supported version for PostGIS 2.0.0. It took a bit of squabbling between PSC members to make that decision, but I put my foot down and I think in the end was for the best to allow us to use new features, less platforms to test, and get rid of some unnecessary code.

PostGIS Windows 32-bit Experimental builds fresh off the presses

With all these changes, if you are running an earlier alpha release of PostGIS 2.0.0, you'll need to do a dump restore since the on disk format is now changed.

If you are on windows and want to give some of this all a test drive, you can download one of our PostGIS 2.0.0 Windows experimental builds. We still only have 32-bit builds. We have builds for PostgreSQL 8.4, PostgreSQL 9.0, and PostgreSQL 9.1 beta 2. The problems we faced in PostgreSQL 9.1 beta 1 were resolved in beta 2 so that most regress tests past except some minor ones involving stupid things like difference in line number marking of errors. Complement your PostgreSQL 9.1 beta 2 meal with a yummy large helping of PostGIS 2.0.0 goodness.

PostgreSQL 9.0 beta 2 just got released this week. We may see another beta before 9.0 is finally released, but it looks like PostgreSQL 9.0 will be here probably sometime this month. Robert Treat has a great slide presentation showcasing all the new features. The slide share for those on Robert Treat's slide share page.

We'll list the key ones with our favorites at the top:

Our favorites

1. The window function functionality has been enhanced to support ROWS PRECEDING and FOLLOWING. Recall we discussed this in Running totals and sums using PostgreSQL 8.4 a hack for getting around the lack of ROWS x PRECEDING and FOLLOWING. No more need for that. This changes our comparison we did Window Functions Comparison Between PostgreSQL 8.4, SQL Server 2008, Oracle, IBM DB2. Now the syntax is inching even closer to Oracle's window functionality, far superior to SQL Server 2005/2008, and about on par with IBM DB2. We'll do updated compare late this month or early next month. Depesz has an example of this in Waiting for 9.0 – extended frames for window functions
2. Ordered Aggregates. This is extremely useful for spatial aggregates and ARRAY_AGG, STRING_AGG, and medians where you care about the order of the aggregation. Will have to give it a try. For example if you are building a linestring using ST_MakeLine, a hack you normally do would be to order your dataset a certain way and then run ST_MakeLine. This will allow you to do ST_MakeLine(pt_geom ORDER BY track_time) or ARRAY_AGG(student ORDER BY score) This is very very cool. Depesz has some examples of ordered aggregates.
3. Join removal -- this is a feature that will remove joins from the execution plans where they are not needed. For example where you have a left join that doesn't appear in a where or as a column in select. This is important for people like us that rely on views to allow less skilled users to be able to write meaningful queries without knowing too much about joins or creating ad-hoc query tools that allow users to pick from multiple tables. Check out Robert Haas why join removal is cool for more use cases.
4. GRANT/REVOKE ON ALL object IN SCHEMA and ALTER DEFAULT PRIVILEGES. This is just a much simpler user-friendly way of applying permissions. I can't tell you how many times we get beat up by MySQL users who find the PostgreSQL security management tricky and tedious to get right. Of course you can count on Depesz to have an example of this too Waiting for 9.0 - GRANT ALL

Today Microsoft unveiled their top secret project code named CatchMe. This is their new flagship database for Linux and Unix based on predominantly the PostgreSQL 9.0 code base, but with an emulation layer that makes it behave like SQL Server 2008 R2. Unlike the Windows SQL Server 2008 R2 product, this version is completely free and open source under the Microsoft Public License (Ms-PL). Downloads for the RCs of these will be available soon. Please stay tuned.

Reporter Dat A. Base managed to get an exclusive interview with the head of the project, Quasi Modo. The transcript follows:

Ah a new year, a new PostgreSQL release in the works. Beware -- this post is a bit sappy as we are going to highlight those that have made our lives and lives of many a little easier.

These are people we consider the most important because they provide the first impression that newcomers get when first starting off with PostgreSQL. The newcomer that quickly walks out the door unimpressed, is the easy sale you've lost. Make your pitch short and sweet.

As always Hubert does a really good job of taste testing the new treats in the oven and detailing how cool they are. I highly suggest his posts if people have not read them already or are looking at PostgreSQL for the first time. You can catch his Waiting for PostgreSQL 8.5 series which is in progress. Surely gives us a list of things to test drive.

Then there are those that document, the volumes of PostgreSQL documentation which are just great, up to date and rich with content. Probably too many of these people to call out, and sadly we don't know them by name.

Of course its not just enough to announce releases, document them and talk about them, you must make it really easy for people to try them out. If people have to compile stuff, especially windows users, forget about it. You won't hear complaints, you won't hear whispers, you'll hear dust blowing. The biggest audience you have is the one you just lost because you didn't make it easy for them to try your stuff. The apple hit me on the head one day when a very dear friend said to me and here is a slight paraphrase. You don't actually expect me to compile this myself do you? How much time do you think I have? It is not about you, it is about me.. This was especially surprising coming from a guy I always thought of as selfless. This I realized is the biggest problem with many open source projects, that they are lost in the flawed mentality that its about scratching their own itch and the rest will come. It is not. Always concentrating on your own itch and scratching it is a sure way of guaranteeing that no one will scratch your itch for you. Think of it like a pool game. Do you target the aim at the ball you are trying to hit, or balls near by that will knock down the others. So in short don't be a complete wuss that people can walk all over, but look past your nose and choose your balls wisely; make sure all your balls are not focused on software development.

The upcoming version of PostGIS - PostGIS 1.5 will be an exciting one. It has native geodetic support in the form of the new geography type, similar in concept to SQL Server's geography support. For windows users, we have experimental binary builds hot off the presses for PostgreSQL 8.3 and 8.4

PostgresQL 8.4 beta will be out any day and 8.4 official release will hopefully not be too far behind. As we wait patiently for the official release, Robert Treat has summarized nicely all the new features you can expect in 8.4. PostgreSQL 8.4 is what I like to call an earth-shattering release because it has so many big ticket items in there, but also some long-needed usability features in it.

While we all know about the Windowing functions and CTEs and Recursive CTEs, there are a couple of usability features that we always get beat up on, which I am glad to see will be in 8.4. these are

1. Ability to add new columns to a view with CREATE OR REPLACE without having to drop the view and all the view dependents
2. Case insensitivity module
3. Improved Vacuum performance
4. Common Table Expressions and Recursive Common Table Expressions (CTE), windowing functions - Hubert has an example of this in Waiting for 8.4 - window functions

Now the other niceties and usuability features which are nice but not quite as top of our list as the aforementioned. Note this far from an exhaustive list, but Robert Treat's 8.4 slide presentaton is pretty exhaustive:

• Variadic functions -- these are functions that have default values defined so can be called with varying arguments. To achieve this before you would have had to create a separate function that calls the first and passes in the default arg. NOte this can be done with any pl langauge and in fact we demonstrated its use in PL/Python PL/Python and default parameters.
• All plpgsql language and other non-sql/non-c proc languages that return sets to be called in the SELECT clause. To get around this problem before, you'd create your sophisticated set returning function in plpgsql or python or whatever and then wrap it in an SQL function. No need for that hack anymore. Again we demonstrated this feature in PL/Python Pl/Python for loops and returning sets
• pg_terminate_backend -- this kills a backend PostgreSQL process instead of just cancelling the query running on it as pg_cancel_backend did
• Column level priviledges - Hubert has a good example of this in Waiting for 8.4 - column level privileges .
• Faster Restore -- now Restore can use parallel threads
• RETURN QUERY EXECUTE support in plpgsql
• LIMIT clause can take a subquery -- SELECT a.field1, a.field2 FROM a LIMIT (SELECT COUNT(*)/10 FROM a)
• Make As alias in column SELECT optional as the ANSI SQL Standard allows. So you can now do - SELECT a field1, b field2 .... This is not something we would suggest since we find it makes code hard to read, but does make code that used this regrettable syntax more portably converted to PostgreSQL. It would be nice if this were a flag though in the config that can be turned on since I find it to be bad practice and encouraging bad habits.
• Numerours changes to EXPLAIN to show columns used, maintenance improvements such as dead-locking reporting

PostGIS 1.3.4 is almost out the door - Need testers

Well PostGIS 1.3.4 is almost out the door and we will be releasing an RC3 very shortly. As a developer in the group and also as a user of the product that is near and dear to me I would be really appreciative if people in the PostgreSQL community interested in PostGIS can test this out. Below is a clip of Mark Cave-Ayland's note to the postgis-dev group.

PostgreSQL 8.1+ - Win32
PostgreSQL 8.1+ - Solaris
PostgreSQL 7.3  - Linux

PostgreSQL 8.1+ - GEOS 2.2.3 - Linux
PostgreSQL 8.1+ - GEOS 3.0.3 - Linux
PostgreSQL 8.1+ - GEOS 3.1 - Linux

The plan here is to check Win32/Solaris both pass the new sed tests in
Makefile.config.in, make sure we haven't broken really old PostgreSQL
builds, and also make sure that GEOS 2.2, GEOS 3.0 and the very new GEOS
3.1 with prepared geometry doesn't break either.

As we mentioned in a previous article, RECURSIVE queries, often referred to in SQL ANSI specs and by DB2 and SQL Server as Common Table Expressions (CTE) will make it into the 8.4 release and can already be found in the dev source. Technically CTE is a superset and RECURSIVE queries are a subclass of CTE. Looks like basic windowing functionality will make it in 8.4 as well.

A summary of where your favorite patches are at can be found at the September 2008 PostgreSQL 8.4 commit-fest summary page http://wiki.postgresql.org/wiki/CommitFest:2008-09.

The PostgreSQL 8.4 planned release is March 1, 2009 and is outlined in the PostgreSQL 8.4 Development plan. It has just passed its May 2008 commit fest milestone and is currently in its July 2008 Commit Fest. Lots of PostgreSQL Planet bloggers have started showcasing some of the new features in store. We will briefly list our favorite planned and already committed patches.

PostgreSQL 8.3 is out

As many have said - PostgreSQL 8.3 was released on February 4th, 2008 and has numerous enhancements. Listing of features can be found at PostgreSQL 8.3 release notes, and has been mentioned ad-nauseum by several Postgres bloggers. Robert Treat has provided a nice round-up of blog entries that demonstrate various 8.3 enhancements in his PostgreSQL Blog's 8.3 Feature Round-Up. As a side note, the new EnterpriseDb funded Stack Builder feature for windows provides a nice complement for getting add-ons to PostgreSQL.

Horizon of PostgreSQL

Many PostgreSQL contributors are very proud of the fact that PostgreSQL is an open source project and therefore can not be bought like MySQL which is an open source product made by a commercial company. I'm not sure general PostgreSQL users really care that much about this. I suspect that many think

• yah - and Microsoft can be bought, Oracle can be bought, IBM can be bought - who is big enough to buy them and like they will kill off their prize cows
• and if Oracle, IBM or Microsoft one day were to give away non-crippled versions of Oracle 11G, SQL Server 2008, DB2 to leverage their other holdings (perhaps slightly unrealistic), what would that mean to PostgreSQL or MySQL?
• Can a community grow without money pumping into it and if it is not growing does it F*** matter that it is an open source project?

MySQL and Sun?

We just read that Sun is purchasing MySQL for a little under a billion. We are a little shocked and not quite sure what to make of it or how this affects Sun's investment in PostgreSQL. Further comments on the deal on Jignesh Shah's blog and Josh Berkus blogs. Jignesh and Josh both work at Sun and do PostgreSQL work as well.

Couple of random thoughts

• First, better Sun than Oracle. The thought of Oracle eating up MySQL has always been rather disturbing to us especially since we do a fair amount of MySQL consulting and don't care much for Oracle as a company. I suppose it could still happen.
• Given the fact that Sun is a large contributor to the PostgreSQL project, does this mean PostgreSQL fans can't make fun of MySQL anymore? Are we like friends now? This could take away some fun and add a little fun at the same time.
• Will this mean MySQL will have no qualms of using PostgreSQL underlying storage engine and what would it be called? - MyPost

Overall we think the move should prove positive for both camps.

PostgreSQL 8.3 really around the corner

8.3 is now on release candidate 8.3 RC1 and as Bruce Momjian noted, it looks like there might be an RC2.

We've been playing around with the 8.3 betas and RCs and really like the integrated Full Text Indexing and XML features. The new features make it possible to do a quickie REST service-based application. In the next issue of this journal, we hope to demonstrate creating REST services using 8.3 with server side - (PHP and/or ASP.NET) and front-end Adobe FLEX. We would have liked to demonstrate SilverLight/MoonLight as well, but we want to wait till Silverlight 2.0 hits release. We'll try to use the Pagila demo database for the upcoming demo app as Robert Treat has suggested.

PostgreSQL 8.3 is currently in Beta 4 and promises to offer some whoppingly neat features. First before we go over the new features we are excited about in this upcoming release, we'd like to briefly cover what was added in past releases.

The big 8.0 Highlights

1. The biggest feature in 8.0 was native support for Windows
2. Dollar quoting syntax for stored functions which made it a lot easier to write stored functions since instead of having to escape quotes you could use the $something$ body of function $something$ delimiter approach.
3. Next favorite was - Tablespaces so that people no longer needed to resort to messy symlinks to simulate this needed feature
4. Save points, and improved buffer management.

8.1 Highlights

1. Introduced Bitmap indexes which allowed for ability to use multiple indexes on a table simultaneously and less need for compound indexes.
2. Auto vacuuming. Auto Vacuuming was a huge benefit that all could appreciate. It allowed for automatic cleaning of dead space without human or scheduled intervention.
3. Also introduced in this release was improved shared locking and two phase commit
4. change in security - introducing login roles and group roles plus prevention of dropping roles that owned objects.
5. Constraint exclusion which improved speed of inherited tables thus improving table partitioning strategies.

8.2 Highlights

1. Query optimization improvements
2. 8.2 introduced multi row valued lists insert syntax (example here) similar to what MySQL has . As a side note, SQL Server 2008 will introduce a similar feature as row constructors.
3. Improved indexed creation that no longer required blocking concurrent insert, create, delete.
4. Ability to remove table inheritance from a child table without having to rebuild it.
5. Aggregates that can take multiple inputs and SQL:2003 statistical functions
6. Introduction of Fill Factor for tables similar to Microsoft SQL Server's Fill Factor functionality

8.3 upcoming Highlights

8.3 has numerous highlights just as previous versions, but we shall focus on our favorite ones.

1. Support Security Service Provider Interface (SSPI) for authentication on Windows - which presumably will allow PostgreSQL databases to enjoy the same single signon you get with Microsoft SQL Server 7-2005.
2. GSSAPI with Kerberos authentication as a new and improved authentication scheme for single signon
3. Numerous performance improvements - too many to itemize - check out Stefan Kaltenbrunner's 8.3 vs. 8.2 simple benchmark
4. The new QUERY functionality in plpgsql which offers a simpler way of returning result sets
5. Scrollable cursors in PLPgSQL
6. Improved shared buffers on windows
7. TSearch - Full Text Search is now integrated into PostgreSQL instead of being a contrib module
8. Support for SQL/XML and new XML datatype
9. ENUM datatype
10. New add-on feature to PgAdmin III - a PL debugger most compliments of EnterpriseDB