Try the personal touch, please

One of the laziest recruiter tools in use today is Linkedin contact requests. I use Linkedin as a consumer because it helps me keep in contact with various people in a way that would otherwise be difficult. But when I get contact requests from recruiters I almost always ignore them. If recruiters can't be bothered to dig out my email address and send me something personal, then I'm not interested in them. The other day was a new low. I got a completely generic contact request from a new recruiter at the most persistent recruiter of all  - Google (they usually contact me about once a year). I once admired Google a lot, but this just added to the list of reasons I don't admire them so much now - one of the other reasons is their participation in a non-compete cartel among software companies. But in any case, I refuse to engage with cookie cutter recruiters. They tend to be lazy and incompetent.

Watch all the little new features

Sometimes it's quite difficult to keep on top of all the new features that we implement. One I had missed that I just found out about yesterday, goes all the way back to 9.1, when we got advisory locks with transactionaly scope. These locks work just like the normal Postgres locks, in that they go away when the transaction in which they are obtained commits or rolls back, and they can't be released prior to that.

What makes this so cool is that it makes these locks a whole lot safer to use. I'll be looking to use them now in places where I haven't previously used advisory locks, because making sure you release the older style advisory locks in all the possible code paths can sometimes be quite tricky, and getting it wrong can have nasty consequences.

New JSON standard passes the buck

There is a new JSON standard out. It contains a few interesting things. For example, here is one change noted in the changelog:

Changed the definition of "JSON text" so that it can be any JSON value, removing the constraint that it be an object or array.

This is consistent with what Postgres does, but we actually had a bit of a debate back when we implemented JSON about whether or not it was the right thing to do. Maybe it wasn't then but is now :-)

And there this is this delightful couple of new paragraphs:

An object whose names are all unique is interoperable in the sense that all software implementations receiving that object will agree on the name-value mappings. When the names within an object are not unique, the behavior of software that receives such an object is unpredictable. Many implementations report the last name/value pair only. Other implementations report an error or fail to parse the object, and some implementations report all of the name/value pairs, including duplicates.

JSON parsing libraries have been observed to differ as to whether or not they make the ordering of object members visible to calling software. Implementations whose behavior does not depend on member ordering will be interoperable in the sense that they will not be affected by these differences.

How is that for having it both ways? You might not be incorrect if you allow duplicate names or if your application relies on object member ordering, just not "interoperable." Once, RFCs laid down the law, now they just seem to codify existing, possibly "non-interoperable" practice.

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.