Use a VM when in any doubt

Long experience has taught me that some software is best run in a Virtual Machine where it can do relatively little harm to other components of your system. Right at the top of this list is almost all VPN software. The only exception I usually make is for OpenVPN, which is almost always very well behaved. I have a couple of VM's set up for just this purpose. They normally only get fired up when I need to connect to a client over a VPN.

The other day I needed to connect fairly urgently to a client's network, and they require use of a VPN. The VPN software in question, which is proprietary, is one I know from experience can cause problems. However, I could not get it working on my sacrificial VMs, which run Linux, and they told me that they couldn't get it reliably working on Linux either, and that they all connect from Windows. I didn't have a sacrificial VM running Windows, and so, despite my best judgement, I installed the software on a non-virtual Windows machine, that, among other things, runs a couple of buildfarm animals as well as a number of Linux VMs I am using currently on another project.

Big Mistake.

The VPN worked OK, but, when I was done, the Virtualbox network interfaces were hosed. It took me a long time - literally hours, to work out what had happened, since the problem only became apparent when the VMs were recycled, which was a couple of days after using the VPN software. I tried, in turn, disabling the VPN software, uninstalling the VPN software, rebooting, and updating Virtualbox. Nothing worked. Finally I did a system restore back to the point before I had first installed the VPN software. Suddenly everything worked again.

So today I have spent more hours setting up a Windows VM I can use for this purpose. First I had to go out and buy a legitimate copy of Windows, and then get it set up and installed in a VM on my laptop. I put it there so I still have it available when I'm on the road.

But this software still had more surprises for me. It's set up not to allow VPN connections to be made from remote desktop connections. And this is rather important to me since I don't carry my laptop around my house, and when at home I mostly don't work from the laptop. But it turns out that Virtualbox will run a remote desktop server for you and connect the guest console to it, so that the guest thinks the connection is local even when it's not. Take that, nasty VPN vendor!

All in all this has cost me the best part of two days to fix and configure. I'm more than mildly annoyed by it. But I have learned my lesson. I will never run this sort of software from a non-virtual host again.

unnest_ordinality extension

The other day Josh Berkus was working on one of our clients and needed a fast way to unnest some arrays with ordinality, that is to have a row number attached to each element. The usual way that is recommended for doing this is to use subscripting with generate_series(), or calling unnest() in a plpgsql function and then returning the elements one by one. Both of these turned out to be not fast enough, and he was resorting to some fairly ugly code to get around the speed issue. I offered him a simple C function that would do this very quickly, and we're releasing it publicly today. In use on our client's site it has been extremely fast and simple to use.

example:

andrew# create extension unnest_ordinality;

    andrew=# select * from unnest_ordinality('{a,b,c,d,e,f,g}'::text[]);
     element_number | element 
    ----------------+---------
                  1 | a
                  2 | b
                  3 | c
                  4 | d
                  5 | e
                  6 | f
                  7 | g

The package is available on The PostgreSQL Experts Inc Github Repository and also on PGXN

Vagrant buildfarm client

I've recently been exploring use of the nifty vagrant package for a project I'm about to start working on. So today in an idle moment waiting for some other stuff to complete I created a simple setup to allow you to create a complete and working, unregistered buildfarm client, all stashed away nicely in a ScientificLinux 6.3 x86_64 VM for you. If you have vagrant installed and working with VirtualBox, all you have to do to run this is:

git clone https://bitbucket.org/adunstan/sl6-x64-buildfarm-vagrant.git
cd sl6-x64-buildfarm-vagrant && vagrant up

Once the machine is up and running you can login and run the client straight away. It will create a git mirror, and configure, build, and test Postgres. All from one command:

cd ~vagrant/bf && perl run_build.pl --test --verbose

Enjoy.

Backport of 9.3 json enhancements

I've done a backport of the 9.3 JSON enhancements, as a 9.2 extension, or rather as much of them as it's reasonably possible to backport. There are some caveats to using this extension, so please read the warnings carefully before you use it.

If you use PGXN you can get it from there, or it can be got from the public git repository at bitbucket.

Thanks to David Wheeler for prodding me to finish this up.

Enjoy.

Linux/ZFS is great

A few days ago the ZFS on Linux people finally produced a production release of their latest code. I had been playing around a bit with it on an Ubuntu VM, but yesterday I installed it on a customer's  RedHat 6.2 production system. This system will be used to store PostgreSQL backups made with the barman package, and the feature we most wanted to use was the builtin file system compression - mainly to save on space requirements. Installing it and turning a SAN volume into a ZFS volume turned out to be incredibly easy and painfree. And it's a win-win situation. The IO time saved by using a compressed file system has reduced the time for a backup by about 60%, and the space saved in our case is about 80%. All in all I'm impressed, and I'm certainly going to be trying out some more of its features. It will be nice to ascertain what modes will be best suited to production database use.

JSON processing functions and the new JSON parser API

Today, with a great sense of relief, I committed the second of my major JSON patches for this release cycle, just beating the imminent close of the current commit-fest.

Most of the attention has focussed on the new JSON processing functions, which you can see details of in the dev docs. But the most important part in my mind is really the less visible part, the scaffolding on which these new functions were build. Essentially this took the original (and very good, for its purpose) JSON parser that Robert Haas wrote for release 9.1, which was stack based, and turned it into a classic recursive descent parser. In doing that it provided the ability of functions calling the parser to supply callback hooks for certain parsing events, such as the beginning or end of a JSON object or array. It's kind of the JSON equivalent of an XML SAX parser. That means it's quite easy to construct new JSON processing functions without having to worry at all about the nitty gritty of JSON parsing. I expect to see a number of JSON processing extensions based in this (and I expect to write a few myself).

I'm hoping to give a talk at a conference later in the year about how to build these processing functions.

Parallel pg_dump committed

Today I committed Joachim Wieland's  patch to add a parallel option to pg_dump when outputting directory format. Barring some unexpected and evil occurrence, this should be in the forthcoming 9.3 release. This is the culmination of several years of work. I started talking about this shortly after I did the parallel pg_restore work for release 8.4.

This was a lot more difficult and complex than parallel pg_restore. The three pieces that were required to make this work were: an archive format that we could sensibly write to in parallel, a way of making sure that a dump done in parallel was consistent, and then the actual code to use those features to achieve parallel dumping. The first piece was directory archive format, that was introduced with Release 9.1. The second piece was snapshot cloning, that was introduced in Release 9.2. This final piece builds on those two earlier pieces of work to make parallel dumps a reality.

This development throws away some of my earlier work, We now have a common parallel processing infrastructure that is used for both pg_dump and pg_restore, The new code pre-creates the required number of workers (threads on Windows, processes on Unix) and farms out multiple pieces of work to them.  This is quite different from the earlier way pg_restore worked, which created workers on demand and allocated exactly one piece of work to them.

Congratulations to Joachim on this milestone..

Recent feature requests

In the last few days customers have wanted the following PostgreSQL features:

• A time zone setting of "system" which will just pick up whatever the system's time zone setting is at server start. This would be useful for appliance  makers who pre-install a database and then ship the appliance to a customer who can change the time zone, as one of our customers does.
• The ability to set a default tablespace for a schema, and to move all the objects in the schema to that tablespace. This was apparently something that got ripped out in the very late stages of the 8.0 development cycle, but it doesn't seem like a terribly bad idea at first glance.

Writeable Foreign Tables

There are lots of cool things coming in 9.3, but the one I'm licking my lips over is one that was committed the other day by Tom Lane: Writeable Foreign Tables by KaiGai Kohei. As soon as I get done with the work I have in hand for core 9.3 I will be an early adopter, adding this to the Redis FDW. This is going to be very cool.

Loading Useful Modules in PLV8

PLV8 is a trusted language. In fact it arguably safer than pretty much any non-core loadable language, since the interpreter is naturally sandboxed. So there's no way to load external processing modules from the file system. However, pure JavaScript modules can be cleanly loaded from the database itself. A couple of extremely useful ones I have found are Underscore and node-jPath. The former can be loaded with no modification whatever. The latter is written for use with node.js and needs a small amount of adjustment before it's loaded in plv8.

So how do you do this? Well, first you need to grab the code:

curl -o underscore.js http://underscorejs.org/underscore-min.js
curl -o jpath.js https://raw.github.com/stsvilik/node-jpath/master/lib/node-jpath.js

Then edit jpath.js so that, at the bottom, instead of assigning to module.exports it creates an object called jpath and adds the select and filter methods to it.

Then you'll need a table to load the modules from, and to put the modules in this table. You'll also need a function to load them. These can be accomplished with the following psql script:

\set underscore `cat underscore-min.js`
\set jpath `cat jpath.js`

create table plv8_modules(modname text primary key, load_on_start boolean, code text);

insert into plv8_modules values ('underscore',true,:'underscore'),
    ('jpath',true,:'jpath');

create or replace function plv8_startup()
returns void
language plv8
as
$$
load_module = function(modname)
{
    var rows = plv8.execute("SELECT code from plv8_modules " +
                            " where modname = $1", [modname]);
    for (var r = 0; r < rows.length; r++)
    {
        var code = rows[r].code;
        eval("(function() { " + code + "})")();
    }
        
};
$$;

Now test it out:

select plv8_startup();
do language plv8 ' load_module("underscore"); load_module("jpath"); ';
-- test the underscore module's extend function
do language plv8 $$ 
   x = {'a':1}; 
   y=_.extend(x,{'a':2,'b':3},{'b':4,'c':5}); 
   plv8.elog(NOTICE,JSON.stringify(y)); 
$$;
-- test jpath module's filter function
do language plv8 $$
  var jsonData = {
    people: [
        {name: "John", age:26, gender:"male"},
        {name: "Steve", age:24, gender:"male"},
        {name: "Susan", age:22, gender:"female"},
        {name: "Linda", age:30, gender:"female"},
        {name: "Adam", age:32, gender:"male"}
    ]
  };
  //We want to get all males younger then 25
  var match = jpath.filter(jsonData, "people[gender=male && age < 25]");
  plv8.elog(NOTICE,JSON.stringify(match));
$$;

You should see output like:

NOTICE:  {"a":2,"b":4,"c":5}
DO
NOTICE:  [{"name":"Steve","age":24,"gender":"male"}]
DO

Now, the final step is to make these get loaded automatically. Alter your plv8_startup() function by adding to following at the end:

// now load all the modules marked for loading on start
var rows = plv8.execute("SELECT modname, code from plv8_modules where load_on_start");
for (var r = 0; r < rows.length; r++)
{
 var code = rows[r].code;
 eval("(function() { " + code + "})")();
}

and finally add

plv8.start_proc = 'plv8_startup'

to you postgresql.conf file, and restart. You should now be able to use these modules anywhere in your PLv8 code.

A few notes from the field on git

Any time a source code management system gets in your way it's not doing its job. It's really something you should have to think about as little as possible. One thing I found out by doing it the wrong way the other day is that of you create a pull request on bitbucket or github, you really need to do it on a topic branch created just for the purpose. After the upstream repo you have sent the pull request to has merged your changes you can delete the branch. Cleaning up after I unthinkingly sent a pull request for the node-postgres driver from a long-lived branch was rather ugly.

I also wish it were possibly to exclude certain files (e.g. regression test result files) from having trailing whitespace colored. But if it is possible I have yet to find that way.

Learning new technology

One of my oldest clients is switching to a new technology stack. After many years of using AOLServer, OpenACS and Postgres as their preferred basis for applications they have switched to using nginx, Redis, node.js and Postgres with plv8.

I'll be talking about some of this, especially the use of Redis with Postgres, at pgcon. in May.

I can't say I really like the node.js style of programming, but needs must when the devil drives. One thing worth noting is that there is quite a good node.js driver for Postgres, and that the maintainer was very responsive in merging in a patch I sent. I've sent in another one (to fix a bug connecting to Unix domain sockets) and I hope it too will be picked up very promptly.