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.

Blackhole FDW updated

There have been some new things added to the FDW API in 9.5, so I have just updated the blackhole Foreign Data Wrapper to reflect the changes, so it now has documented skeleton functions for every FDW callback.

Goodbye jsonb_replace, hello jsonb_set

After a little controversy on the PostgreSQL Hackers mailing list, I got the idea loud and clear that some users weren't happy with the new jsonb features, and in particular the fact that they weren't getting a way to add values to structures nested well inside the jsonb object. Eventually I came up with the idea that we should do this via an enhanced jsonb_replace function. This function gained an additional boolean parameter, defaulting to true, and when it is true the function will not only replace existing items but if the last element of the path designates an object key or array element that is not present, it will be created with the new value. Since this is no longer just replacement of existing values, the function has been renamed jsonb_set. These changes are based in some original code from Dmitry Dolgov, heavily modified by be, and they have just been committed for inclusion in release 9.5. Here are a few examples:

andrew=# select jsonb_set('[]','{0}','"a value"');
  jsonb_set  
-------------
 ["a value"]

andrew=# select jsonb_set('{}','{x}','"a value"',false);
 jsonb_set 
-----------
 {}

andrew=# select jsonb_set('{"f1":{"f2":{"y":1}}}','{f1,f2,x}','"a value"');
                jsonb_set                 
------------------------------------------
 {"f1": {"f2": {"x": "a value", "y": 1}}}

Although this is actually a fairly small change, in terms of code, its impact could be quite big. I believe it will add significantly to our compelling JSON story.

Running pgindent on non-core code, or development code

Running pgindent is not nearly as hard as some people seem to think it is. The hardest part of getting a workable set of typedefs to use. That's why the buildfarm now constructs these lists automatically for each live branch.

But that doesn't help if you're working on non-core code. Here's what I did to get a working typedefs list for the Redis FDW code:

objdump -W redis_fdw.so |\
 egrep -A3 DW_TAG_typedef |\
 perl -e ' while (<>) { chomp; @flds = split;next unless (1 < @flds);\
     next if $flds[0]  ne "DW_AT_name" && $flds[1] ne "DW_AT_name";\
     next if $flds[-1] =~ /^DW_FORM_str/;\
     print $flds[-1],"\n"; }'  |\
 sort | uniq > redis_fdw.typedefs

This is a slight adaptation of what the buildfarm code does on Linux to get a typedefs list.

After that, indenting the code was a matter of just doing this:

pgindent --typedefs=redis_fdw.typedefs redis_fdw.c

What if you're developing a piece of core code and you'd like to run pgindent on it, but you've introduced some new typedefs, so pgindent mucks up the indentation by adding extraneous spaces. You have a couple of options. Let's assume that what you're working on is backend code. Then you could run the above extraction on the built backend - it doesn't have to be installed, just run it against src/backend/postgres. Then use that to run pgindent against each of the files you're working on. You don't have to run it separately for each file - you can name as many files to indent as you like on the command line.

If you do that, look at the results carefully. It's possible that the absence of some platform-dependent typedef has mucked up your file. So a safer procedure is to grab the latest typedefs list from the buildfarm server and combine it with the typedefs list you just constructed, like this:

wget -q -O - "http://www.pgbuildfarm.org/cgi-bin/typedefs.pl?branch=HEAD" |\
 cat - mytypedefs | sort | uniq > mynewtypedefs

and then use that file to pgindent your code.

None of this is as easy as it might be. But none of it is very hard either.

Addendum

If you only have a handful of new typedefs, you can pass them on the command line to pgindent, like this:

pgindent --typedefs=mytypedefs --list-of-typedefs="typedef1 typedef2" myfile1.c myfile2.c

Placeholder blues

There has been some debate going on on the hackers list about problems certain drivers have with operators that contain the ? character. Since drivers such as the JDBC driver consider any such character outside of quote marks to be a parameter placeholder, its use as part of an operator makes life difficult for them. Part of the problem has been that we allow almost every available non-alphanumeric ASCII character to be a part of an operator. Why, for example, do we allow `? I've never seen it that I can recall, but the fact means we can't use it for something else (such as marking an operator). That makes doing anything about the problem hard. Some people want to deprecate the use of ? in operators. I think it's far too late for that - its use is far too widespread.

Yet another data recovery job

One of the things I like about data recovery jobs is that each one is different. Right now I'm wrestling with this problem: how can I recover whatever is possible from a backup that's missing a handful of xlog files. We know this will result in a probably corrupt database, but we still want to see what data is available.

My first two approaches didn't work too well. I tried mangling the control file, and creating dummy xlog files to replace the handful that are missing.

Right now I'm trying to follow a suggestion from Andres Freund to drive the process via the debugger, moving it on to the next available log file when it gets to where it would want the first missing log file.  The trouble is it's hard to find what to set exactly and where to set it (this is Postgres 9.3). Every time I try something I get errors like "unexpected page_addr". If I were more familiar with this section of the code it might be easier, but it's something I've pretty much never dabbled in.

It all makes for an exciting life.