A little permissions problem

So you're on Fedora 22 and you want to run the latest stable PostgreSQL release. You install the PGDG repository package and then install postgresql94-server. You decide to test it out first, so as your normal login user you run initdb and then try to start the server using pg_ctl, something you've done a hundred times before. It should "just work," right? Not this time. You examine the log file and you see it complaining like this:

could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

Ouch! I've never needed that before. These files have always gone in /tmp. OK, so why is it different now we are putting them elsewhere? It turns out that the directory has been created by the RPM with permissions different from /tmp, and only the postgres user can write there.

My solution (which I hope our RPM maintainers adopt) was to change the permissions on the directory like this, making them the same as those of /tmp:

sudo chmod 1777 /var/run/postgresql

After that when I try to start the database everything is as expected.

How to find what's causing that error

Tom Lane just posted a terrific piece of advice about how to find what's causing an error you don't know the source of, which is worth quoting:

What I would recommend is that you get the data onto a non-production
machine where you can play around a bit more. One thing you could do
then is run a build with debug symbols, attach to the backend process
with gdb, and set a breakpoint at "errfinish". Then provoke the error,
and capture a backtrace from the call to errfinish. That would greatly
narrow things down, though it might not be enough to isolate the bug
immediately.

I normally like to have debuginfo packages installed even on production machines so I can do this sort of thing quickly.

Another thing you can try in a non-production environment is to run your test case against a build with assertions enabled and see if it trips an assertion failure. That's often a quick way of finding where problems are occurring.

Testing on Fedora

One of the things that is nice about Fedora is how easy it is to test something very quickly. They provide a considerable number of cloud images suitable for spinning up instances fast. In my case I have a machine with Vagrant and VirtualBox installed, and when we got a complaint recently about PostgreSQL misbehaving with some glibc locales in fedora 23, I wanted to test it. After downloading the image I wanted I was able to get running with this:

vagrant box add --name=f23 Fedora-Cloud-Base-Vagrant-23_Beta-20150915.x86_64.vagrant-virtualbox.box
mkdir f23
cd f23
vagrant init f23
vagrant ssh

It took me about 5 minutes from the time I discovered the image I wanted to test to starting on setting up to build PostgreSQL on it, and most of that was download time.

Redis FDW doesn't suport Redis Cluster

Redis Cluster is a way of sharding data across a group of federated Redis nodes. I was asked recently if this is supported by the Redis Foreign Data Wrapper. Unfortunately, the answer is "No." The Redis site coyly states that "At this stage one of the problems with Redis Cluster is the lack of client libraries implementations." What they don't mention is that their own C client library, hiredis, has no support for it at all, happily reporting back redirects to the client rather than following them. It's rather as if a web client library refused to follow an HTTP redirect. The Redis FDW is based on hiredis, so as the library doesn't support Redis Cluster, neither do we. Of course, at the expense of very large amounts of code we could make the FDW handle it, but this seems quite wrong architecturally. The FDW should not have to know or care that the Redis server it's connected to is sharded.

Keep your configs under version control

A conversation yesterday with a client: "What changes were made in the PostgreSQL configs?" "I don't recall exactly." This is why you should keep all the configs under version control, and have a regular monitoring check that the controlled version is the same as the operating version, so if someone makes a manual change without checking it in the monitoring alarm will go off.

If you keep your config in the data directory, in the default PostgreSQL style, probably the simplest way to do this is to create a git repository with a symbolic link to the data directory and a .gitignore that ignores everything in the data directory but the config files. The add the config files and you're done. If you keep the config files separately, as Debian does, then you can probably just create a git repository right in the config directory if you don't already have one at a higher level.

I have been guilty of not doing this in the past, but I have come to the strong conclusion that this should be a part of standard best practice.

Update

My attention has been drawn to etckeeper, which makes handling this super simple when you're using Debian-style config setups. It's available both for Debian and for RedHat-ish systems, as well as possibly others.

When to use json, and when to use jsonb

As well as being able to store JSON in the database, either as plain text (json type) or as a decomposed binary format (jsonb type) PostgreSQL has quite a number of useful utility functions for creating JSON from non-json data. In many cases, clients don't want to store the json or process it in any way, they just want to get hold of it outside the database. There are subtle differences between the two types - jsonb eliminates whitespace and duplicate object keys, and mangles the order of object keys.

PostgreSQL 9.5 will introduce jsonb equivalents of pretty much all the json building functions, namely json_agg, json_object_agg, json_build_array, json_build_object, json_object and to_json. The exceptions are the legacy functions array_to_json and row_to_json, for which we provided no jsonb equivalents. These are covered by to_jsonb.

If object key order preservation is important to you, or you want to be able to have duplicate keys, then you definitely don't want to be using the jsonb functions. But beyond that, if you don't want to store the json or process it in any way in general the jsonb functions are overkill, and they are definitely slower. A recent benchmark I did for a client had a query using the jsonb functions to be slower by a factor of about 4. When you think about it, this isn't entirely surprising - not only is it more work to turn data into jsonb, but when we're done we have to turn it back into a piece of text to deliver to the client. With json, once you've constructed it it's already text, so there's no extra work.

So don't assume that the jsonb functions are always going to be faster - in many cases they are not. Other things being equal, you will often want to be using the json generating functions.

On the other hand, if you want to store the data for later processing, e.g. by examining the contents of a field inside the json, jsonb is probably the way to go.

And of course, if speed is an issue you need to benchmark you application.

Quick buildfarm recipe for CentOS and friends

I've been writing a little provisioning script for a vagrant Centos 7 machine that will be a buildfarm client.

Stripped of the vagrant stuff and some stuff that's special for the machine, here is what I did to get it running:

sudo yum install -y wget gcc make flex bison ccache git \
  perl-libwww-perl perl-Digest-SHA perl-devel perl-ExtUtils-Embed \
  zlib-devel openssl-devel readline-devel python-devel tcl-devel \
  libxml2-devel libxslt-devel openldap-devel

mkdir bf
cd bf
HERE=`pwd`
wget http://www.pgbuildfarm.org/downloads/latest-client.tgz
mv latest-client.tgz buildfarm-latest-client.tgz
tar -z --strip-components=1 -xf buildfarm-latest-client.tgz 
mkdir root
sed -i "s!build_root =>.*!build_root => '$HERE/root',!" build-farm.conf

At this stage the buildfarm client will run quite successfully, as can be tested using:

perl run_build.pl --test

Quick tips

Found in the last couple of days.

1. If you have more than one active database, and you're using text format logs, make sure you have %d (the database name) in your log line prefix. Not knowing which database the log line comes from can be confusing and makes life more difficult.

2. If you report on a possible database error, report from the Postgres logs, not from your client's logs. We need to see things from the database's point of view. In the case in point, the Postgres logs conatined no relevant errors at all, i.e. this wasn't a Postgres error.

3. Found when trying to resize a data volume on Amazon: certain Linux disk partitioning utilities don't optimally align partitions by default, notably sfdisk and cfdisk. Stay away from those, or make sure you explicitly choose proper alignment for partitions. Programs that do the right thing include fdisk and parted. In general you are probably best to stick to using parted.

Dropping columns on partitioned tables.

Say you have a partitioned table and you want to add a column. There is no problem - you just add the column to the parent table, and it is added to all the children. But what if you want to drop a column? Then things are no so straightforward. If the child's column was created before it was inherited then it won't be dropped just by dropping it on the parent. So it very much depends on how the child is set up. If you do:

create table child() inherits (parent);

then dropping a column in the parent drops it in the child too. But if you do:

create table child (like parent);
alter table child inherit parent;

then dropping a column in the parent won't drop it in the child. The pg_partman package follows this second pattern when setting up child partitions, as I discovered yesterday when a client ran into this problem. In this case you have to delete the column from the children yourself. I devised the following snippet of code to accomplish this after you have deleted the column from the parent:

do $$
      declare 
        child oid;
      begin
        for child in
            select inhrelid
            from pg_inherits
            where inhparent = 'parent'::regclass
        loop
            execute 'alter table ' || child::regclass ||
                    ' drop column if exists some_column';
        end loop;
      end;
   $$; 

Conference Slides - 9.5 Json improvements and Rotfang FDW

Here are my slides from pgCon. First, my slides and Dmitry Dolgov's slides from the presentation on 9.5 Json Features, and also those from my lightning talk on the Rotfang Foreign Data Wrapper.

Rotfang, the random data generator

The other day I gave a lightning talk at pgCon about a Foreign Data Wrapper called Rotfang, which is for generating arbitrary amounts of random data. This is intended for generating test cases. The software supports a small number of data types natively, but also allows you to use user-supplied functions to generate data. It's available on bitbucket. A short example:

CREATE EXTENSION rotfang_fdw;

CREATE FUNCTION random_tstz (typmod int)
RETURNS timestamptz
LANGUAGE SQL as
$$
    SELECT now() - (random() * 20.0) * interval '1 year'
$$;

CREATE FOREIGN TABLE rand2 (b boolean, ts timestamptz)
SERVER rotfang
OPTIONS (maxrows '10', func_ts 'random_tstz');

SELECT * FROM rand2;

What is a Set type?

Yesterday I proposed an Unconference talk about Set types, and I've had a couple of people ask me about what Set types are and what they would be for, so here is a brief rundown.

Say you have a table of things that you want to be able to apply some set of tags to. It might be blog posts, for example. The tags might be subject classifications, or reader ratings. Let's say for the sake of argument that it's going to be subject classifications, and that we actually have a (large) enum type to standardize that.

The classic way to do this is to have a table of {blog_post_id, tag} which will be unique on the combination. Up to now adding a tag is a nuisance - you need to make sure the tag doesn't already exist or you'll get a uniqueness violation. In 9.5 that's improved with INSERT ... ON CONFLICT IGNORE. Even then it's a bit more work that I really want to do. What I really want is something like this:

UPDATE blog_posts
SET subject_tags = subject_tags + 'my_new_subject'
WHERE post_id = 12345

and then I'll be able to do

SELECT * FROM blog_posts
WHERE subject_tags ? 'some subject'

It's also possible to do this, somewhat inefficiently, by using hstore or jsonb fields (in jsonb you'd just have a top level object). In both cases you would use dummy values - say make everything have a value of 1. But that's ugly, and error prone, and rather hackish.

Now I don't know if there is enough extra value here to justify the work and code maintenance involved. I've managed to live without it all these years without attacking what little hair I have left. But it's something where I have occasionally thought "I could do this so much more simply and elegantly if I had a set type." So I'd like to see if there is enough interest to make it into a real project.