Monday, September 24

Andrew’s PlanetPostgreSQL

[Video] Introduction to JSON data types in PostgreSQL

Andrew's PlanetPostgreSQL, PostgreSQL
The video of my presentation below walks you through the major features of the native JSON data type in PostgreSQL 9.3 and beyond. This presentation covers the following topics: What is JSON? How is it available in PostgreSQL? What's the difference between JSON and JSONB? Accessing JSON values Creating JSON from table data Creating table data from JSON Crosstabs with JSON Indexing and JSON When to use JSON, when to use JSONB, and when neither should be used (more…)

Keeping our perl code clean

Andrew's PlanetPostgreSQL
Recently I have been refining and adding utilities to look after our Perl code.  You might be surprised to learn that as well as 1.3 million or so lines of C code, there are about 30,000 lines of Perl code in our sources. This a sizeable body of code, even if it's dwarfed by our C code. What does it do? Well, lots of things. It runs some very critical code in building from source, so the code to set up our catalogs is created by some Perl code.  All the new data setup for catalogs is in fact Perl code. That's another 20,000 lines or so of code on top of the 30,000 mentioned above. We also use Perl to run TAP tests, such as testing initdb and pg_dump. And it runs building and testing when we're building with the Microsoft tool-sets on Windows. So, what changes have been made? First, we (more…)

Road test your patch in one command

Andrew's PlanetPostgreSQL
If you have Docker installed on your development machine, there is a simple way to road test your code using the buildfarm client, in a nicely contained environment. These preparatory steps only need to be done once. First clone the repository that has the required container definitions: git clone bf-docker Then build a container image to run the command (in this example we use the file based on Fedora 28): cd bf-docker docker build --rm=true -t bf-f28 -f Dockerfile.fedora-28 . Make a directory to contain all the build artefacts: mkdir buildroot-f28 That's all the preparation required. Now you can road test your code with this command: docker run -v buildroot-f28:/app/buildroot \ -v /path/to/postgres/source:/app/ (more…)

PostgreSQL Buildfarm Client Release 8

Andrew's PlanetPostgreSQL
I have just release version 8 of the PostgreSQL Buildfarm client It can be downloaded from or This release contains a number of small changes to make using the --from-source feature a bit nicer. It also contains a good deal of code cleanup to be perlcritic clean, with some exceptions, down to severity level 3, and also to remove some out of date code that referred to Postgresql branches we no longer support. It also contains the following features: --tests and --schedule now apply to the check step as well as the installcheck step a new --delay-check switch delays the check step until after install. This helps work around a bug or lack of (more…)

Buildfarm Client Bugfix Release 6.1

Andrew's PlanetPostgreSQL
I have released version 6.1 of the PostgreSQL Buildfarm client. It is available at This release fixes a couple of bugs that became apparent in yesterday's release. The first was a relatively minor one where the verbosity was always set if using the script. The second was a portability issue where some versions of Perl and its Time::HiRes module behaved unexpectedly and as a result log files were not sorted in correct order, leading to very strange timing results. The second one is sufficiently bad to warrant this point release. Thanks to Tom Lane for identifying and helping to diagnose and patch these bugs. Also, one other small bug is fixed in some utility scripts, and the BlackholeFDW module (more…)

Announcing Release 6 of PostgreSQL Buildfarm client

Andrew's PlanetPostgreSQL
PostgreSQL Buildfarm client Release 6 is now available and can be downloaded from Features use a persistent cache for configure, leading to a substantial performance improvement for that step, based on an idea from Tom Lane improve installcheck security, mirroring earlier changes on this in Postgres itself allow skipping individual TAP tests, e,g, "--skip_steps=pg_dump-check" a use_installcheck_parallel setting that uses the parallel schedule rather than the serial schedule for the installcheck step (not for MSVC) use_valgrind and valgrind_options settings that will cause the installed postmaster to be run under valgrind and report any output. This doesn't apply to the check step, TAP tests, or any (more…)

Setting up a build machine for Visual Studio 2017

Andrew's PlanetPostgreSQL
Recently we've had a patch submitted to support the latest incarnation of the Microsoft build tools, Visual Studio 2017. I didn't have a spare Windows machine available to test with, so I set up Windows machine on Amazon AWS to test with. I chose Windows Server 2016, a t2.medium instance with a 50 GB root disk (The default 30Gb is a bit tight.) This costs about US$0.065 per hour to run, so it's pretty cheap. The first things I did, as I always do with these machines, were to turn off Internet Enhanced Security, which has a habit of getting in the way, and then install the Firefox web browser. Then I installed the Chocolatey package manager for Windows. This is a pretty useful tool, somewhat similar to yum, dnf and apt. You need to install this via an administrative command shell. Once (more…)

Announcing Release 5 of the PostgreSQL Buildfarm Client

Andrew's PlanetPostgreSQL
Release 5 of the PostgreSQL Buildfarm Client has been released and can be downloaded from In a similar move to PostgreSQL version numbering, with this release we move to a one part numbering system. In addition to a number of bug fixes and very minor changes, this release has the following features / changes: Cross-version pg_upgrade module is no longer experimental - see below TAP tests now run the "check" target, but in most cases redundant installs are avoided Improved and expanded TAP test running on MSVC animals - these now run the same tests as other animals Automatic garbage collection of git repositories, once a week by default. This should improve the speed of git operations, especially on (more…)

Up to date access to postgres logs

Andrew's PlanetPostgreSQL
Some of my Italian colleagues have made a nifty little gadget called redislog for pushing postgres logs into Redis, the distributed in-memory cache. From there it can be fed into things like logstash. I thought it would be interesting instead to make the logs available via the Redis Foreign Data Wrapper as a Postgres table. That way we would have easy access to the running logs from Postgres with almost no effort. Here's what I did. First I built and installed redislog and redis_fdw. Then I added redislog to my server's shared_preload_libraries,  set log_min_duration_statement to 0 and restarted. Then I created a database called logger and did this in it: create extension redis_fdw; create server localredis foreign data wrapper redis_fdw; create foreign table (more…)

Out of tree builds

Andrew's PlanetPostgreSQL
Recently I was asked "what's a vpath build?" If you regularly build postgres from source it's something you should know about. A vpath build is one where the build tree is kept completely separate from the source tree, so you can completely remove the build tree and your source tree is still there, clean and pristine. It's very easy to set up unless you're building with the Microsoft toolset. Essentially what you do is create the root of your build tree, change directory into that directory, and then call configure in your source tree from there.  configure knows all about setting up the vpath tree and does all the work for you. After that, you just run make etc just like you would normally. So it looks like this: mkdir mybuild cd mybuild /path/to/postgresql-source/configure make (more…)