New Json functions

I've been pretty busy, and neglected this blog for a while, but I'm very happy that today I have been able to commit a parcel of new JSON functions that will be in the next PostgreSQL release. These include functions to create very complex JSON tree structures, and a function to aggregate pairs of values as JSON objects.

You can see some documentation in the development version Documents.

Thanks to Marko Tiikkaja for his timely review.

Stay tuned, there's more JSON goodness coming!

SSPI auth on Windows

Does anyone have experience setting up SSPI authentication for PostgreSQL on a Windows machine that's not part of a domain? If so, I'd like to pick your brains a bit, so please let me know.

ActiveState make ActivePerl non-free

I have just received an email from ActiveState which contains the following:

To be better equipped to answer support questions, particularly for customers using ActivePerl in production, we have changed the licensing terms of Community Edition: http://www.activestate.com/activeperl/license-agreement. To use ActivePerl in production, you must now have a supported Business or Enterprise Edition license; we continue to provide the latest versions free for development and testing use.

So now we need a decent free-as-in-beer and free-as-in-free Perl distribution for Windows. And don't tell me Strawberry Perl - last time I looked it didn't work well, and anyway most people don't want to have to install whole development environment just to get Perl.

I guess ActiveState have the right to do this, strictly speaking, but it's a dick move nevertheless. To say I'm annoyed would be understating it considerably. It's a classic bait and switch. Build up a community's dependence on what you give them, and then start charging them for it.

For the time being I will be advising clients on Windows to steer clear of PLPerl for Postgres. I'm not sure what I'll recommend they use instead. It certainly makes it more imperative to make sure that we can use PLV8 on Windows.

A big news day

Of course the biggest news of the day is that Amazon has announced immediate support for Postgres on their RDS service.

I have taken this for a very short spin, and it looks like it works as I expected.

I noticed a couple of things. First, it doesn't require SSL to connect. That's probably not a good thing. They should have at least an option to require use of SSL. And second, they don't seem to have support for the awesome pg_stat_statements module, which is such an important tool in the modern Postgres DBA's arsenal. I hope I'm wrong or that they fix that one quickly.

The other interesting news I heard for the first time today from my colleague Jeff Frost, is that Ubuntu have released a new kernel version for the 12.04 LTS release that doesn't have the performance horrors of earlier kernels. That should be good news for lots of Ubuntu users. I have one client who downgraded to a non-LTS release on the basis of the performance regression, so this is a big deal.

Looking forward to Triangle PUG kickoff

The Triangle PUG has got 31 members since starting less than a month ago, and 20 have said they will attend the kickoff meeting tomorrow (Wednesday) evening. I'm quite excited!

Triangle PUG kickoff meeting, 7.00 pm Oct 23rd

After a little time spent trying to get a good venue, I've scheduled the kickoff meeting for the Triangle PUG for Oct 23rd at 7.00 pm. Robert Treat will be in town and attending, and I'm going to reprise my PostgresOpen talk on PostgreSQL 9.3 and JSON. Details are at meetup.com.

NC Triangle PUG moving along

We're up to about 19 members with very little effort, so I think this will be viable. We have a group on meetup, and I hope to be able the schedule the first meeting, which will be a week or two from now, very soon.

Extra functions needed

I'm trying to collect a list of things that people need for handling JSON, particularly in generating JSON.

For example, Josh gave me this little problem yesterday:

given: 
serial | name | type 
847001 | t15 | GE1043 
847002 | t16 | GE1043 
847003 | sub-alpha | GESS90 

I want to produce: 
  { "turbines" : 
    { 
       847001 : { "name" : "t15", "type" : "GE1043" }, 
       847002 : { "name" : "t16", "type" : "GE1043" }, 
       847003 : { "name" : "sub-alpha", "type" : "GESS90" } 
    } 
  }

I think this is going to call for a small addition to the json_build package, a json object aggregator. With that we'd be able to do this with:

select json_build_object("turbines",
        json_object_agg(serial, json_build_object("name",name,"type",type)))
from mytable;

Probably we'd want a similar aggregator for arrays. This would be different from the builtin json_agg function which takes a record as its argument and always produces an array of hashes.

Features still wanted

I was uploading my recent slide decks to SlideShare and noticed I had the deck there from a talk I gave about 4 years ago at TriLug about new features in Postgres 8.4. In there is a slide (#24) labeled "Major missing SQL features" and containing these two items:

• grouping sets
• merge

Sadly, four years later those features are still missing. And they are still the thing I think are the most urgent new SQL features we should provide.  Both are hard to do well, and probably both require major funding kicks to have any chance of being done.

Interesting new technologies

A couple of things I heard about at Postgres Open that deserve closer inspection are Docker, a nice container toolkit, leveraging among other things Linux LXC, and OpenShift, another containerization platform. Today I discovered that while I was travelling home yesterday (and taking far too long about it), a major announcement was made of collaboration between Docker and RedHat, the principal sponsors of OpenShift. This is pretty cool stuff. OpenShift already has "cartridge" for PostgreSQL, and I am going to play with it the first opportunity I get.

NC Triangle PUG here we go

Quite by chance I and two other people from the NC Triangle area (Raleigh, Durham, Chapel Hill and surrounds) went to the 2ndQuadrant sponsored dinner (thanks guys) at Postgres Open on Monday night. Out of that came a proposal for a Triangle Postgres Users Group. I set a few things in motion on that today, and I hope it will be a reality very soon. Please email me if you are interested and live in the area, so I can make sure we don't miss you when everything gets set up.

Slides from Postgres Open - 9.3 JSON and Blackhole FDW

Here are the slides from my talk at Postgres Open about JSON in Postgres 9.3

I also reprised my pgCon lightning talk about the Blackhole Foreign Data Wrapper.