Singleton Rows in PostgreSQL

A common technique in Unix is the lock file. This is often followed up by having the script that creates the lock file run a command and automatically remove it afterwards. It’s a convenient way of enforcing serialisation.

Unfortunately, I’ve got a number of jobs to serialise across two different machines. They’re all database related tasks. So, I thought that I’d try to put the lock inside PostgreSQL itself as it’s what they share in common (never use NFS for locking!).

What we need is a table that can only ever contain one single row. Initially I thought of something like this:

CREATE TABLE mutex (
  pid integer not null,
  hostname text not null,
  command text not null,
  created_at timestamp with time zone default now(),
  -- zero or one rows only in this table
  CHECK (count(*) < 2)
);

Sadly, you’re not allowed aggregate functions in a CHECK constraint. I’m not sure why, but I’ll have to work around it. So, instead, I came up with this:

CREATE TABLE mutex (
  active boolean DEFAULT true NOT NULL UNIQUE,
  pid integer NOT NULL,
  hostname text NOT NULL,
  command text NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT no_inactive_mutex CHECK (active)
);

This has a boolean column, no nulls allowed, and with a unique index. So no more than two rows. Then, we add an extra constraint to say that no false values are allowed. This gets us to where we want to be—only zero or one rows allowed in this table.

The next bit is wrapping all this up in a script, robustly. But that’s relatively trivial compared to the SQL. The main bit is ensuring that you use an END{} block (in Perl) or trap '...' EXIT (in a shell script) to ensure that you always remove the mutex on exit. Of course, it can never be as simple and reliable as a call to unlink, but it’s good enough for me.

Update 2018-09-12: Bono Stebler points out that there is a more idiomatic PostgreSQL solution, creating an index on a constant value:

CREATE UNIQUE INDEX ON table_name ((true));