FreeBSD/Postgres UUID plugin

This is going to be none too short and in no way sweet.

We had a need for UUIDs on a Postgres 8.3.1 server running FreeBSD 7.0 and you wouldn't believe it but there is already a UUID module available in the Postgres distribution, just compile and install and jobs a gooden right? Wrong! The bundled module relies on the OSSP-UUID library which doesn't seem to work in FreeBSD right now.

Actually if it just didn't work that would be ok, but it segfaults the fuck out of the running postgres process and drops all connections to the server. This is EPIC FAIL. So gdb and ee in hand I headed into the unfamiliar world of *gulp* C coding to get to the bottom of this undesirable behaviour and what I found horrified me. Huge swathes of code, massive libraries and slow clunky execution. Bluurrgghh!

Anyway, a quick bit of background. A UUID is a 128bit number which is designed to be as statistically unique as possible. They make dead handy keys for databases, node identifiers in cluster environments and anywhere where you require a simple unique identifying number that can be easily generated and difficult to repeat. From FreeBSD 5 onwards there have been system headers to provide DCE v1.1 compliant UUIDs and even a userland app.

I'm sure OSSP-UUID is a far superior solution in every way... I'm sure it also provides statically superior numbers and indefinitely more security. However it doesn't seem to work on my platform and the amount of libraries and resources required to create a small psuedo-random number is ridiculous. This drove me to try (after hours of slaving away with OSSP-UUID and Postgres) to write my own small, simple and fast UUID procedure.

Ladies and gentlemen, I present spoo-uuid.


#include "/usr/include/uuid.h"
#include "utils/uuid.h"
#include "postgres.h"
#include "fmgr.h"

/* Magic postgres stuff! */


Datum spuuid()

/* Set up some variables */

uuid_t uu;
uint32_t status;
char buffer[37];
char *buf_p=buffer;

/* Use the system uuid library to generate a uuid string */

uuid_to_string(&uu, &buf_p,&status);

/* Return the string to postgres */

int32 buffer_size = (strlen(buffer)+VARHDRSZ);
char *spooj = palloc(buffer_size);
memcpy(VARDATA(spooj), buffer,buffer_size);

Yeah, I know there's nothing too it. That's sort of the point. Anyway I suppose I should probably provide a really shitty little makefile for this...


# Spoo's nasty make file

MODULE_big = spoo-uuid
OBJS = spoo-uuid.o
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Once you've run a gmake, copy the to your postgres lib folder. In my case this would mean -

cp libspoo-uuid.s* /usr/local/pgsql/lib

Next up you've got to create the procedure in Postgres otherwise this whole page of drivel has been without point.


Finally, the proof is in the pudding. A quick test to ensure that all is sexeh with the job.

test# /usr/local/pgsql/bin/./psql -d postgres -c "select spuuid();"
(1 row)

Splendid, and now for my own edification lets have a look at the requirements.

test# ldd ./
./ => /lib/ (0x28080000)

So, what do we have here? A UUID number that's based on system time, with the primary network interface mac address. It's not big, it's not clever but it does exactly what we needed. I'm sure the more awesome of you will be able to pick holes in this until the cows come home but I'm rather proud of it, so cock off!


printf "%s%s-%s-%s-%s-%s%s%s\n" `jot -r -w "%04x" 8 0 65536`

Shell script ftw... I just wish I could say this was mine :( I found it on my initial search for freebsd uuid issues and it's a beautiful little bit of script by a guy called Dan Nelson. You have no idea how tempting it was to use this instead.

p.s. I've also tested it on Postgres 8.3.5 and it works there too.


Heavy Engine Console
Loading Page... /1068-FreeBSDPostgres-UUID-plugin