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.

Set types

Every so often I've wanted a SET metatype in Postgres.

We do have bit fields, which can be used as sets with a little work, and this is how MySQL does it's SET types as I understand it. But what if you want a set of enums (our enums can have very large lists of values), or a set of text values, or of integers? Bit fields won't work for these - we would need some sort of sparse representation.

One obvious way would be to store the set as a one-dimensional array, kept in sorted order for efficient searching. For text values, we could probably use some smart form of sorting such as is used by jsonb and hstore to store object keys, to reduce the need for string comparisons.

Obviously, I have done no work on this, but it seems like it would be a worthwhile project.

In the meantime, you can use hstore more or less in this way. Just treat it as a mapping from whatever values you have to 1.  It's a bit of a hack, but it should work. You'll just need to cast your values to text.

New jsonb features for 9.5.

Yesterday I committed some new features for jsonb. These are:

• jsonb_pretty(jsonb) produces nicely indented json output.
• jsonb || jsonb concatenates two jsonb values.
• jsonb - text removes a key and its associated value, or a matching array element, from the json
• jsonb - int removes the designated array element
• jsonb - text[] removes a key and associated value or array element at the designated path
• jsonb_replace(jsonb,text[],jsonb) replaces the array element designated by the path or the value associated with the key designated by the path with the given value.

This was based Dmitry Dolgov's jsonbx extension for 9.4, which in turn was based on earlier work by Oleg Bartunov, Teodor Sigaev and Alexander Korotkov on nested hstore. Petr Jelinek also did some useful review and tidy up work on the core features. So, a good team effort.

Reminder: checking Windows builds is incredibly easy.

Most PostgreSQL developers don't use Windows, and many fairly openly regard the Windows port as a nuisance. But we've had it for more than a decade, now, and it's long since time we got over that. And checking that at least your changes compile is easy. It doesn't need a Windows box at all - Postgres builds for Windows quite happily via a cross-compiler. Here's exactly what I did on a Fedora 20 machine this morning:

sudo yum install mingw64-gcc mingw64-zlib
git clone git://git.postgresql.org/git/postgresql.git
cd postgresql
./configure --host=x86_64-w64-mingw32
make world

And it just worked. I tested it on Windows, and it might even be possible to test it using WINE, but even without testing it this procedure is useful to see if there are compilation errors.

I assume all this could be (and possibly has been) built for other platforms such as OSX and FreeBSD. If not, it might be worth putting together some packages for those platforms - it shouldn't be too hard.