Pgcon

The jsquery stuff from Oleg and Teodor looks awesome. I will be exploring it very soon. Meanwhile, here are my conference slides: http://www.slideshare.net/amdunstan/94json where I cover mostly 9.4 Json features that aren't about indexing.

This has been one of the better pgcons. Well done Dan and the rest of the team.

pgbouncer enhancements

A couple of customers have recently asked for enhancements of pgbouncer, and I have provided them.

One that's been working for a while now, puts the address and port of the actual client (i.e. the program that connects to the proxy) into the session's application_name setting. That means that if you want to see where the client is that's running some query that's gone rogue, it's no longer hidden from you by the fact that all connections appear to be coming from the pgbouncer host.You can see it appearing in places like pg_stat_activity.

It only works when a client connects, so if the client itself sets application_name then the setting gets overridden. But few clients do this, and the original requester has found it useful. I've submitted this to the upsteam repo, as can be seen at  https://github.com/markokr/pgbouncer-dev/pull/23.

The other enhancement is the ability to include files in the config file. This actually involves a modification to the library pgbouncer uses as a git submodule, libusual. With this enhancement, a line that has "%include filename" causes the contents of that file to be included in place of the directive. Includes can be nested up to 10 deep. The pull request for this is at  https://github.com/markokr/libusual/pull/7. This one too sems to be working happily at the client's site.

There is one more enhancement on the horizon, which involves adding in host based authentication control similar to that used by Postgres. That's a rather larger bit of work, but I hope to get to it in the next month or two.

Version 4.12 of the PostgreSQL Buildfarm client released.

I have released version 4.12 of the buildfarm client.

In addition to numerous bug fixes, it has the following:

• the global option branches_to_build can now be 'HEADPLUSLATESTn' for any single digit n
• there is a new module TestCollateLinuxUTF8
• there is a new module TestDecoding which is enabled by default, (but does nothing on MSVC systems, where we can't yet run these tests.) This runs the new contrib test_decoding module, which can't run under "make installcheck".
• running "perl -cw" on the scripts will now give you failures for missing perl modules on almost every platform. The only exception should now be on older Msys systems.
• improvements in the sample config file to make it better organized and better reflecting of best practice.
• find_typdefs is now supported on OSX

In addition I recently enhanced the HOWTO at http://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto covering especially best current git practice.

Thanks to Tom Lane for suggestions and complaints which are behind a number of the improvements and fixes, and for some code for OSX find_typedefs.

The release is available at http://www.pgbuildfarm.org/downloads/releases/build-farm-4_12.tgz

Almost a crosstab using JSON

Most of the recent focus on the new json facilities has been on jsonb. This is understandable - it shows great promise, and a lot of people put in a lot of work on it. Besides myself, there were Teodor Sigaev, Oleg Bartunov, Alexander Korotkov and Peter Geoghegan as principal authors, so it was quite a large effort.

But there are some other new facilities for JSON that shouldn't be forgotten. I was reminded today of some of the things we could do. In particular, we can generate json on all sorts of interesting and useful ways.

A client was finding the crosstab functionality of the tablefunc module unwieldy. The trouble is you need to know in advance what the categories are. But we can construct something with the JSON functions that doesn't need any such knowledge. It's not quite a crosstab, but it will give you very similar functionality. Instead of rolling up the values into SQL fields named after the categories, we roll them up as values in a JSON object where the fields are named for the categories.

It's fairly simple.

Consider the following table:

andrew=# select * from xx;
 a | b | c  
---+---+----
 1 | b | x
 1 | c | y
 1 | d | z
 2 | d | dz
 2 | c | cz
 2 | b | bz
 2 | e | ez
(7 rows)

To get a crosstab we do something like this:

andrew=# select * 
from crosstab('select * from xx') a(r int, b text, c text, d text, e text);
 r | b  | c  | d  | e  
---+----+----+----+----
 1 | x  | y  | z  | 
 2 | dz | cz | bz | ez
(2 rows)

To get the JSON object, instead we do this:

andrew=# select a as r, json_object(cols, vals) as pivot 
from (select a, array_agg(b order by b) as cols, array_agg(c order by b) as vals 
      from xx group by a) x;
 r |                      pivot                       
---+--------------------------------------------------
 1 | {"b" : "x", "c" : "y", "d" : "z"}
 2 | {"b" : "bz", "c" : "cz", "d" : "dz", "e" : "ez"}
(2 rows)

Note how we didn't need to supply a column list reflecting the category values. The new json_object() function takes one or two arrays and outputs the corresponding JSON object.

It's also possible to do this with hstore today:

andrew=# select a as r, hstore(cols, vals) as pivot 
from (select a, array_agg(b order by b) as cols, array_agg(c order by b) as vals 
      from xx group by a) x;
 r |                   pivot                    
---+--------------------------------------------
 1 | "b"=>"x", "c"=>"y", "d"=>"z"
 2 | "b"=>"bz", "c"=>"cz", "d"=>"dz", "e"=>"ez"
(2 rows)

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!