Monday, March 25

Andrew’s PlanetPostgreSQL

PostgreSQL Buildfarm Client Release 9

Andrew's PlanetPostgreSQL
Announcing Release 9 of the PostgreSQL Buildfarm client. Along with numerous fixes of minor bugs and a couple of not so minor bugs, this release has the following features: new command line parameter --run-parallel for runs all branches in parallel, possibly across animals as well new config setting max_load_avg inhibits a run if the load average is higher than the setting new config_option archive_reports saves that number of generations of the report sent to the server new command line parameter --show-error-log which outputs the error log if any on stdout automatically rerun 3 hours after a git failure, useful on back branches where commits can be infrequent automatically convert old URLs to better (more…)

Adding new table columns with default values in PostgreSQL 11

2ndQuadrant, Andrew's PlanetPostgreSQL
In PostgreSQL version 10 or less, if you add a new column to a table without specifying a default value then no change is made to the actual values stored. Any existing row will just fill in a NULL for that column. But if you specify a default value, the entire table gets rewritten with the default value filled in on every row. That rewriting behavior changes in PostgreSQL 11. In a new feature I worked on and committed, the default value is just stored in the catalog and used where needed in rows existing at the time the change was made. New rows, and new versions of existing rows, are written with the default value in place, as happens now. Any row that doesn't have that column must have existed before the table change was made, and uses this value stored in the catalog when the row is (more…)

Managing Freezing in PostgreSQL

Andrew's PlanetPostgreSQL, PostgreSQL
Postgres contains a moving event horizon, which is in effect about 2 billion transactions ahead of or behind the current transaction id. Transactions more than 2 billion ahead of or more than 2 billion behind the current transaction id are considered to be in the future, and will thus be invisible to current transactions. Postgres avoids this catastrophic data loss by specially marking old rows so that no matter where they are in relation to the current transaction id they will be visible. Freezing is this process of marking old live tuples (i.e. database rows) so that they don't get run over by the moving event horizon that would otherwise make them appear to be in the future. This is in contrast to vacuuming, which is the freeing up of space consumed by old dead tuples that are no (more…)

[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…)