Peter Eisentraut's Blog

Moving to C++

2013-05-02T06:13:00.000+03:00

GCC 4.8 was recently released. This is the first GCC release that is written in C++ instead of C. Which got me thinking ...

Would this make sense for PostgreSQL?

I think it's worth a closer look.

Much of GCC's job isn't actually that much different from PostgreSQL. It parses language input, optimizes it, and produces some output. It doesn't have a storage layer, it just produces code that someone else runs. Also note that Clang and LLVM are written in C++. I think it would be fair to say that these folks are pretty well informed about selecting a programming language for their job.

It has become apparent to me that C is approaching a dead end. Microsoft isn't updating their compiler to C99, advising people to move to C++ instead. So as long as PostgreSQL (or any other project, for that matter) wants to support that compiler, they will be stuck on C89 forever. That's a long time. We have been carefully introducing the odd post-C89 feature, guarded by configure checks and #ifdefs, but that will either come to an end, or the range of compilers that actually get the full benefit of the code will become narrower and narrower.

C++ on the other hand is still a vibrant language. New standards come out and get adopted by compiler writers. You know how some people require Java 7 or Python 2.7 or Ruby 1.9 for their code? You wish you could have that sort of problem for your C code! With C++ you reasonably might.

I'm also sensing that at this point there are more C++ programmers than C programmers in the world. So using C++ might help grow the project better. (Under the same theory that supporting Windows natively would attract hordes of Windows programmers to the project, which probably did not happen.)

Moving to C++ wouldn't mean that you'd have to rewrite all your code as classes or that you'd have to enter template hell. You could initially consider a C++ compiler a pickier C compiler, and introduce new language features one by one, as you had done before.

Most things that C++ is picky about are things that a C programmer might appreciate anyway. For example, it refuses implicit conversions between void pointers and other pointers, or intermixing different enums. Actually, if you review various design discussions about the behavior of SQL-level types, functions, and type casts in PostgreSQL, PostgreSQL users and developers generally lean on the side of a strict type system. C++ appears to be much more in line with that thinking.

There are also a number of obvious areas where having the richer language and the richer standard library of C++ would simplify coding, reduce repetition, and avoid bugs: memory and string handling; container types such as lists and hash tables; fewer macros necessary; the node management in the backend screams class hierarchy; things like xlog numbers could be types with operators; careful use of function overloading could simplify some complicated internal APIs. There are more. Everyone probably has their own pet peeve here.

I was looking for evidence of this C++ conversion in the GCC source code, and it's not straightforward to find. As a random example, consider gimple.c. It looks like a normal C source file at first glance. It is named .c after all. But it actually uses C++ features (exercise for the reader to find them), and the build process compiles it using a C++ compiler.

LWN has an article about how GCC moved to C++.

Thoughts?

Installing multiple PostgreSQL versions on Homebrew

2013-04-03T04:59:00.000+03:00

I was going to post this yesterday, but some might have thought that it was a joke. April 1st is always an annoying day to communicate real information.

If you have been fond of the way Debian and Ubuntu manage multiple PostgreSQL versions in parallel, you can now have the same on OS X with Homebrew:

brew tap petere/postgresql
brew install postgresql-9.2
# etc.
brew install --HEAD postgresql-common

postgresql-common is the same code as in Debian, only mangled a little.

Now you have all the client programs symlinked through pg_wrapper, and you can use the server management tools such as:

pg_createcluster 9.2 main
pg_ctlcluster 9.2 main start
pg_lsclusters

Let me know if you find this useful.

Links:

Lists and Tuples

2013-02-18T06:30:00.000+02:00

I had initially found the divide between lists and tuples in Python confusing. I came from a database background, so I have a certain expectation of what a tuple might be. If you read up on what the difference is in Python, you will find that a) tuples are immutable, and b) singleton tuples use a funny syntax. So just use lists, because it's easier to read and you can't go wrong that way. Oh, and they are both sequences, another overloaded term.

(Yes, there are some details omitted here, such as that since a tuple is immutable, it is hashable and can be used as a dictionary key. But I think that is used fairly seldomly.)

Then I came across Haskell and it dawned on me: Was this just a poorly mangled feature from Haskell? I don't know the history, but it looks a bit like it. You see, Haskell also has list and tuples. Lists are delimited by square brackets, and tuples are delimited by parentheses:

let alist = [1, 2, 3]
let atuple = (1, 2, 3)

(Technically, in Python, tuples are not delimited by parentheses, but they often appear that way.) But the difference is that Haskell does not use parentheses for any other purpose, such as delimiting function arguments. It uses spaces for that. (So it looks more like a shell script at times.)

Python: len([1, 2, 3])
Haskell: length [1, 2, 3]

But in Haskell, tuples are not mutable lists and lists are not mutable tuples. Tuples and lists are quite different but complementary things. A list can only contain elements of the same type. So you can have lists

[1, 2, 3, 4, 5]
["a", "b", "c", "d"]

but not

[1, 2, "a"]

A tuple, on the other hand, can contain values of different types

(1, 2, "a")
(3, 4, "b")

A particular type combination in a tuple creates a new type on the fly, which becomes interesting when you embed tuples in a list. So you can have a list

[(1, 2, "a"), (3, 4, "b")]

but not

[(1, 2, "a"), (3, 4, 5)]

Because Haskell is statically typed, it can verify this at compile time.

If you think in terms of relational databases, the term tuple in particular makes a lot of sense in this way. A result set from a database query would be a list of tuples.

The arrival of the namedtuple also supports the notion that tuples should be thought of as combining several pieces of data of different natures, but of course this is not enforced in either tuples or named tuples.

Now, none of this is relevant to Python. Because of duck typing, a database query result set might as well be a list of lists or a tuple of tuples or something different altogether that emulates sequences. But I found it useful to understand where this syntax and terminology might have come from.

Looking at the newer classes set and frozenset, it might also help to sometimes think of a tuple as a frozenlist instead, because this is closer to the role it plays in Python.

pgindent Jenkins job

2013-02-15T04:56:00.000+02:00

I have set up a Jenkins job that runs pgindent. Besides checking that the procedure of running pgindent works, it also provides a preview of what pgindent would do with the current source (pgindent.diff), which can be educational or terribly confusing.

Introducing the Pex package manager for PostgreSQL

2013-02-02T06:36:00.000+02:00

I have written a new light-weight package manager for PostgreSQL, called pex. It's targeted at developers, allows easy customization, and supports multiple PostgreSQL installations.

Here is how it works:

Installation:

git clone git://github.com/petere/pex.git
cd pex
sudo make install

Install some packages:

pex init
pex install plproxy
pex search hash
pex install pghashlib

Multiple PostgreSQL installations:

pex -g /usr/local/pgsql2 install plproxy
pex -p 5433 install pghashlib

Upgrade:

pex update
pex upgrade

It works a bit like Homebrew, except that it doesn't use Ruby or a lot of metaphors. ;-)

Check it out at https://github.com/petere/pex.

PostgreSQL and Jenkins

2013-01-01T22:24:00.000+02:00

A lot of places use Jenkins nowadays, including where I now work and have previously worked. I enjoy working with Jenkins, and so I always wanted try out how this would work with PostgreSQL. Obviously, there would be some overlap with the build farm, but that's OK. The point of the build farm, after all, is to build things in many different ways to find potential problems, and this would just support that overall effort.

So I have set this up now: http://pgci.eisentraut.org/jenkins/

It's already been very helpful during the last couple of weeks that I've run this. The main point behind the effort is to automate things. These are things I do just about every day and won't have to anymore:

build PostgreSQL
check for compiler warnings
run various test suites
do this for all supported branches

These are things I do every couple of weeks and have now automated:

check distribution building (make distcheck)
test build of additional documentation formats
cpluspluscheck
check external web links in the documentation (The job for that currently appears to be reporting false positives. Use with caution.)
test coverage reporting

Moreover, I have set up to build some extensions and external modules, which weren't regularly tested. (The build farm is making some efforts in this area, though.)

Actually, many of the checks I had set up immediately found problems: newly introduced compiler warnings, secondary documentation format builds broken, cpluspluscheck failing, broken links in the HTML documentation, various extensions no longer build with Postg reSQL 9.3devel.

But there is more cool stuff:

There are various RSS feeds for all builds or failed buids.
You can interact with the system on mobile devices. I use JenkinsMobi for iOS.
You can get up to date documentation builds on a more predictable schedule.

The one thing (just about) it doesn't do is test operating system and CPU architecture portability. Jenkins comes from a Java background, where this isn't much of an issue, and so there isn't good built-in support for that sort of thing. But anyway, we have the build farm for that.

You can get the code at http://bitbucket.org/petere/pgci. The entire setup is automated with Puppet. You can fork it and set up your own (or send me your changes), or you can run it locally using Vagrant (which is what I do to test changes).

If you have any ideas, let me know (file an issue on Bitbucket). I have plans for a number of enhancements already, foremost pg_upgrade testing. Also, let me know if there are additional extensions you want tested. I have just put in a few I use myself at the moment, but other can easily be added.

Happy New Year!

psqlrc files

2012-10-02T03:20:00.000+03:00

In PostgreSQL 9.2, you can use major-version-specific .psqlrc files, such as .psqlrc-9.2. PostgreSQL 9.2 also added the "include relative" command \ir to psql. Combining these two, you can set up psql initialization to take advantage of any new features you want without breaking the use of old psql releases.

For example, I'd like to set up psql to automatically use \x auto. But if I just put that into .psqlrc, older psql releases will complain about an unknown command. (I usually have multiple PostgreSQL versions installed, and I share dotfiles across hosts.) On the other hand, I don't want to have to duplicate the entire .psqlrc file to add one command, which is where \ir comes in.

Here is what I use, for example:

.psqlrc-9.2

\ir .psqlrc
\set QUIET yes
\set COMP_KEYWORD_CASE preserve-lower
\x auto
\unset QUIET

.psqlrc-9.3

\ir .psqlrc-9.2

pgxnclient supports tarballs and HTTP

2012-09-11T15:14:00.000+03:00

Need to install a PostgreSQL server add-on module? The devel branch of pgxnclient now supports this type of thing:

pgxnclient install http://pgfoundry.org/frs/download.php/3274/plproxy-2.4.tar.gz

This downloads, unpacks, builds, and installs. And the module doesn't need to be on PGXN. And of course you don't have to use HTTP; a file system location will work as well. I think this can be very useful, especially during development, when not everything is available in packaged form, or even for deployment, if you don't want to bother packaging everything and have been installing from source anyway.

Funny version numbers

2012-08-14T06:31:00.000+03:00

Often, I install a new Debian package using apt-get install, and as the progress output flies by, I wonder, Whoa!, should I really be using a piece of software with that version number?

It says a lot, after all. If I see

tool 2.4.1-2

then I (subconsciously) think, yeah, the upstream maintainers are obviously sane, the tool has been around for a while, they have made several major and minor releases, and what I'm using has seen about one round of bug fixing, and a minimal amount of tweaking by the Debian maintainer.

On the other hand, when I see

tool 7.0.50~6.5~rc2+0.20120405-1

I don't know what went on there. The original release version 7.0.50 was somehow wrong and had to be renamed 6.5? And then the 2nd release candidate of that? And then even that wasn't good enough, and some dated snapshot had to be taken?

Now, of course, there are often reasons for things like this, but it doesn't look good, and I felt it was getting out of hand a little bit.

I tried to look into this some more and find a regular expression for a reasonably sane version number. It's difficult. This is how far I've gotten: https://gist.github.com/3345974. But this still lists more than 1500 packages with funny version numbers. Which could be cause for slight concern.

Take a look at what this prints. You can't make some of that stuff up.

Tracing shell scripts with time stamps

2012-07-19T01:05:00.000+03:00

A random tip for shell script hackers. You know that with set -x you can turn on tracing, so that every command is printed before being executed. In bash, you can also customize the output prefix by setting the PS4 variable. The default is PS4='+ '. Here is an example. I wanted to "profile" a deployment script, to see why it took so long. Ordinarily, I might have sprinkled it with date calls. Instead, I merely added

set -x
PS4='+\t '

near the top. \t stands for time stamp. (The script was already using bash explicitly, as opposed to /bin/sh.) That way, every line is prefixed by a time stamp, and the logs could easily be analyzed to find a possible performance bottleneck.

Base backup compression options

2012-05-21T05:00:00.000+03:00

I've been looking at my PostgreSQL base backups. They are run using the traditional

tar -c -z -f basebackup.tar.gz $PGDATA/...

way (many details omitted). I haven't gotten heavily into using pg_basebackup yet, but the following could apply there just as well.

I had found some of the base backups to be pretty slow, so I dug a little deeper. I was surprised to find that the job was completely CPU bound. The blocking factor was the gzip process. So it was worth thinking about other compression options. (The alternative is of course no compression, but that would waste a lot of space.)

There are two ways to approach this:

Use a faster compression method.
Parallelize the compression.

For a faster compression method, there is lzop, for example. GNU tar has support for that, by using --lzop instead of -z. It gives a pretty good speed improvement, but the compression results are of course worse.

For parallelizing compression, there are parallel (multithreaded) implementations of the well-known gzip and bzip2 compression methods, called pigz and pbzip2, respectively. You can hook these into GNU tar by using the -I option, something like -I pigz. Alternatively, put them into a pipe after tar, so that you can pass them some options. Because otherwise they will bring your system to a screeching halt! If you've never seen a system at a constant 1600% CPU for 10 minutes, try these.

If you have a regular service window or natural slow time at night or on weekends, these tools can be quite useful, because you might be able to cut down the time for your base backup from, say 2 hours to 10 minutes. But if you need to be always on, you will probably want to qualify this a little, by reducing the number of CPUs used for this job. But it can still be pretty effective if you have many CPUs and want to dedicate a couple to the compression task for a while.

Personally, I have settled on pigz as my standard weapon of choice now. It's much faster than pbzip2 and can easily beat single-threaded lzop. Also, it produces standard gzip output, of course, so you don't need to install special tools everywhere, and you can access the file with standard tools in a bind.

Also, consider all of this in the context of restoring. No matter how you take the backup, wouldn't it be nice to be able to restore a backup almost 8 or 16 or 32 times faster?

I have intentionally not included any benchmark numbers here, because it will obviously be pretty site-specific. But it should be easy to test for everyone, and the results should speak for themselves.

My (anti-)take on database schema version management

2012-05-15T23:46:00.000+03:00

There were a number of posts recently about managing schema versions and schema deployment in PostgreSQL. I have analyzed these with great interest, but I have concluded that they are all more or less significantly flawed. (Of course, most of these solutions do in fact work for someone, but they are not general enough to become canonical go-to solutions for this problem class, in my opinion.) I have developed a list of elimination criteria by which I can evaluate future developments in this area. So here are some of the things that I don't want in my schema version management system:

Using schemas for distinguishing multiple versions (like this, but that's actually more about API versioning). That simply won't work for deploying objects that are not in schemas, such as casts, languages, extensions, and, well, schemas.
Using extensions (like this). Well, this could work. But extensions by themselves do nothing about the core problem. They are just an SQL wrapper interface around upgrade scripts. You still need to write the upgrade scripts, order them, test them, package them. The extension mechanism might replace the, say, shell script that would otherwise run the upgrade files in a suitable order. Another issue is that extensions require access to the server file system. Changing this is being discussed as "inline extensions", but there is no consensus. This is a smaller problem, but it needs to be thought about. Also, I do need to support PostgreSQL 9.0 and earlier for little while more.
Requiring naming each change (patch names, like this). Naming things is hard. Numbering things is easy. And how many good names are you going to still be able to come up with after 100 or so changes?

Take a lesson from file version control systems: versions are numbers or, if it must be, hashes or the like (UUIDs have been suggested).
Using a version control tool for tracking upgrade paths (like this). Sqitch, unlike the initial draft of this concept, doesn't actually require a version control tool for deployment, which wouldn't have worked for me, because what we ship is a tarball or a deb/rpm-type package. But it still requires you to maintain some kind of sanity in your version control history so that the tool can make sense out of it. That sounds fragile and inconvenient. The other choice appears to be writing the plan files manually without any VCS involvement, but then this loses much of the apparent appeal of this tool, and it's really no better than the "naming each change" approach mentioned above.
Taking snapshots or the like of a production or staging or central development system. Production systems and staging systems should be off limits for this sort of thing. Central development systems don't exist, because with distributed version control, every developer has their own setups, branches, deployments, and world views.

You could set it up so that every developer gets their own test database, sets up the schema there, takes a dump, and checks that in. There are going to be problems with that, including that dumps produced by pg_dump are ugly and optimized for restoring, not for developing with, and they don't have a deterministic output order.
Storing the database source code in a different place or in a different manner than the rest of the source code. This includes using a version control system like mentioned above (meaning storing part of the information in the version control meta information rather than in the files that are checked into the version control system in the normal way), using a separate repository like Post Facto, or using something like the mentioned staging server.

The source is the source, and it must be possible to check out, edit, build, test, and deploy everything in a uniform and customary manner.
Allowing lattice-like dependencies between change sets (like most examples cited above). This sounds great on paper, especially if you want to support distributed development in branches. But then you can have conflicts, for example where two upgrades add a column to the same table. Depending on the upgrade path, you end up with different results. As your change graph grows, you will have an exploding number of possible upgrade paths that will need to be tested.

There needs to be an unambiguous, canonical state of the database schema for a given source checkout.
Requiring running through all the upgrade scripts for a fresh deployment (like this). There are two problems with this. First, it's probably going to be very slow. Fresh deployments need to be reasonably fast, because they will be needed for automated tests, including unit tests, where you don't want to wait for minutes to set up the basic schema. Second, it's inefficient. Over time, you might drop columns, add new columns, delete rows, migrate them to different tables, etc. If you run through all those upgrade scripts, then a supposedly fresh database will already contain a bunch of rubble, dropped attributes, dead rows, and the like.

Therefore, the current version needs to be deployable from a script that will not end up replaying history.
Using metalanguages or abstraction layers (like Pyrseas or Liquibase or any of the metaformats included in various web frameworks). It'd probably a good idea to check some of these out for simple applications. But my concern is whether using an abstraction layer would prevent me from using certain features. For example, look at the Pyrseas feature matrix. It's pretty impressive. But it doesn't support extensions, roles, or grants. So (going by that list), I can't use it. (It's being worked on.) And in a previous version, when I looked at it for a previous project, it didn't support foreign-data functionality, so I couldn't use it then either. And those are just the top-level things the author thought of. Actually, the Pyrseas author has gone through some effort to have almost complete coverage of PostgreSQL DDL features, so give this tool a try. But it won't be for everyone.

So, I'm going to keep looking.

Time to retrain the fingers

2012-05-14T23:14:00.000+03:00

For years, no decades, I've typed tar tzf something, tar xzf something. Except when someone annoying sent an uncompressed tar file and I had to then go and take out the z in the middle.

Then came bzip2, and we learned tar tjf, tar xjf. OK, I could live with that. One emerging problem was that the tab completion now worked the wrong way around conceptually, because you had to pick and type the right letter first in order to see the appropriate set of files to unpack offered for completion.

Then came lzma, which was (quick, guess?), tar tJf, tar xJf. And then there was lzop, which was too boring to get its own letter, so you had to type out tar -x --lzop -f.

But lzma was short-lived, because then came xz, which was also J, because lzma was now too boring as well to get its own letter.

Oh, and there is also the old compress, which is Z, and lzip, which I'd never heard of.

But stop that. Now there is

 -a, --auto-compress
            use archive suffix to determine the compression program

This handles all the above compression programs, and no compression. So from now on, I always use tar taf and tar xaf. Awesome.

The finger movements will be almost the same on QWERTY and AZERTY, and easier than before on QWERTZ.

Actually, this option is already four years old in GNU tar. Funny I'd never heard of it until recently.

Setting the time zone on remote SSH hosts

2012-04-29T11:03:00.000+03:00

The tasks: I have one or more desktop/laptop machines with varying local time zones (because the persons using them are actually in different time zones, or because the one person using them travels). I also have a number of servers configured in some random time zones. (It could be the time zone where they are physically located, or the time zone of the person who installed it, or UTC for neutrality.)

Now what I would like to have happen is that if I log in using SSH from a desktop to a server, I see time on that server in my local time zone. For things like ls -l, for example. Obviously, this illusion will never be perfect. Nothing (except something very complicated) will adjust the time stamps in the syslog output, for example. But the ls -l case in particular seems to come up a lot, to check how long ago was this file modified.

This should be completely doable in principle, because you can set the TZ environment variable to any time zone you like, and it will be used for things like ls -l. But how do you get the TZ setting from here to there?

First, you have to make the remote SSH server accept the TZ environment variable. At least on Debian, this is not done by default. So make a setting like this in /etc/ssh/sshd_config:

# Allow client to pass locale environment variables
AcceptEnv LANG LC_* TZ

You also need to make the equivalent setting on the client side, either in /etc/ssh/ssh_config or in ~/.ssh/config:

SendEnv LANG LC_* TZ

Which leaves the question, how do you get your local time zone into the TZ variable to pass to the remote server? The actual time zone configuration is the file /etc/localtime, which belongs to glibc. In current Debian, this is (normally) a copy of some file under /usr/share/zoneinfo/. In the distant past, it was a symlink, which would have made things easier, but now it's a copy, so you don't know where it came from. But the name of the time zone is also written to /etc/timezone, so you can use that.

The format of the TZ environment variable can be found in the glibc documentation. If you skip past most of the text, you will see the relevant parts:

The third format looks like this:

:CHARACTERS

Each operating system interprets this format differently; in the GNU C library, CHARACTERS is the name of a file which describes the time zone.

So what you could do is set

TZ=":$(cat /etc/timezone)"

Better yet, for hopping through multiple SSH hosts in particular, make sure to preserve an already set TZ:

TZ=${TZ:-":$(cat /etc/timezone)"}

And finally, how does one hook this into ssh? The best I could think of is a shell alias:

alias ssh='TZ=${TZ:-":$(cat /etc/timezone)"} ssh'

Now this set up has a number of flaws, including:

Possibly only works between Linux (Debian?) hosts.
Only works if available time zone names match.
Only works when calling ssh from the shell.

But it practice it has turned out to be quite useful.

Comments? Improvements? Better ideas?

Related thoughts:

With this system in hand, I have resorted to setting the time zone on most servers to UTC, since I will see my local time zone automatically.
Important for the complete server localization illusion: some ideas on dealing with locales on remote hosts.

PostgreSQL and compiler warnings

2012-03-21T19:59:00.000+02:00

Recently, I did some work on backpatching a few commits from PostgreSQL master, and I noticed that with the current tools, the old branches create tons of compiler warnings. In PostgreSQL 8.3, the oldest currently supported branch, a make all with GCC 4.6.3 produces 231 warnings! (Also note that there are only 751 .c files, so that's a warning every three files.) We do a lot of work cleaning up any and all compiler warnings, at least those issued by the latest GCC. These kinds of noisy builds are quite troublesome to work with, because it is more difficult to check whether your changes introduced any new, more serious warnings.

Let's take a look at the current number of compiler warnings in different PostgreSQL branches with different compilers:

	gcc 4.4	gcc 4.5	gcc 4.6	gcc 4.7	clang
8.3	173	51	231	207	665
8.4	12	17	201	201	673
9.0	13	13	89	89	780
9.1	24	24	40	40	25
master	1	1	1	1	1

Obviously, GCC 4.6 introduced many new warnings. If you use the compiler that was current around the time the branch was originally released, you'll be better off. But even then, you should expect a few surprises. (8.3 would probably require gcc 4.3, but I don't have that available anymore.)

Fortunately, it looks as though GCC 4.7, which is currently in release candidate state, will spare us of new warnings. Also note that clang (version 3.0) is now as good as GCC, as far as noise is concerned.

PostgreSQL make install times

2012-03-06T07:35:00.000+02:00

I have decided that make install is too slow for me. Compare: A run of make install takes about 10 seconds (details below), but a run of make all with the tree mostly up to date and using ccache for the rest usually takes about 1 or 2 seconds. You can end up wasting a lot of time if you need to do many of these build and install cycles during development. In particular, make check includes a run of make install, so all this time is added to the time it takes for tests to complete.

So let's optimize this. The times below are all medians from 5 consecutive runs, writing over an existing installation, so they all had to do the same amount of work.

This is the baseline:

make install — 10.493 s

The first change is to use a faster shell. This system is using bash as /bin/sh. Many Linux distributions now use dash instead, but for some reason I haven't changed this system during the upgrade.

make install SHELL=/bin/dash — 6.344 s

I guess I'll be switching this system soon as well then!

The next thing is to avoid installing the translation files. This exploded the number of files that need to be installed. Instead of, say, one program file, you end up installing one program file and a dozen or so translation files.

make install SHELL=/bin/bash enable_nls=no — 6.890 s
make install SHELL=/bin/dash enable_nls=no — 4.482 s

(In practice you would use configure --disable-nls, which is the default. The above is just a way to do this without reconfiguring.) Now I have in the past preferred to build with NLS support to be able to catch errors in that area, but considering this improvement and the availability of the make maintainer-check target, I might end up building without it more often.

Another tip I remembered from the past was to use the make -s option to avoid screen output. Depending on the operating system and whether you are logged in locally or remotely, this can be a big win. On my system, this got lost in the noise a bit, but it appeared to make a small difference over many runs.

make install SHELL=/bin/bash -s — 10.511 s
make install SHELL=/bin/dash -s — 6.146 s

Do add this to your arsenal anyway if you want to get maximum performance.

Next, let's replace the install-sh script that does the actual file copying. For obscure reasons, PostgreSQL always uses that shell script, instead of the /usr/bin/install program that an Autoconf-based build system would normally use. But you can override the make variables and sustitute the program you want:

make install SHELL=/bin/bash INSTALL=install — 5.418 s
make install SHELL=/bin/dash INSTALL=install — 3.995 s

Interestingly, the choice of shell still makes a noticeable difference, even though it's no longer used to execute install-sh.

Finally, you can also use parallel make for the installation step:

make install SHELL=/bin/bash -j2 — 6.538 s
make install SHELL=/bin/dash -j2 — 4.158 s

You can gather from these numbers that the installation process appears to be mostly CPU-bound. This system has 4 cores, so let's add some more parallelization:

make install SHELL=/bin/dash -j3 — 3.330 s
make install SHELL=/bin/dash -j4 — 2.944 s
make install SHELL=/bin/dash -j5 — 2.930 s
make install SHELL=/bin/dash -j6 — 2.952 s

That's probably enough.

Now let's put everything together:

make install SHELL=/bin/dash enable_nls=no INSTALL=install -s -j4 — 1.708 s

Or even:

make install SHELL=/bin/dash enable_nls=no INSTALL=install -s -j3 — 1.654 s

That's a very nice improvement from 10.493 s!

The problem is, it is not all that easy to pass these options to the make install calls made in make check runs. If you can and want to change your system shell, and you configure without NLS support, then you will probably already be more than half way there. Then again, I suspect most readers already have that setup anyway. For the other options, to take down the installation time to almost instantaneous, you have to do ad hoc surgery in various places. I'm looking into improving that.

git whoami

2011-11-22T21:48:00.001+02:00

My favorite feature in bzr (Bazaar) is the bzr whoami command, which prints what your current identity (name and email) is, as far as the repository is concerned. You can tell I haven't used bzr much if that's as far as I have gotten. But seriously, with so many Git repositories around, several project identities, directory-specific shell configuration, and so on, it's easy to get confused, and it's annoying to have to check and repair commits for correct user name and email all the time. So here is git whoami. This has already saved me so many headaches.

plpydbapi: DB-API for PL/Python

2011-11-22T15:55:00.001+02:00

One thing that's weird about PL/Python is that its database access API is completely different from the standard Python DB-API. It is similar to PL/Perl and PL/Tcl, and the C "SPI" API, from which they are all derived, but that's little help for a Python programmer. (The reasons for this are lost in history. Probably laziness.) Moreover, the two APIs use the same function names for different purposes.

So I set out to develop a DB-API compatible layer on top of PL/Python: plpydbapi

Example:

CREATE FUNCTION test() RETURNS void
LANGUAGE plpythonu
AS $$
import plpydbapi

dbconn = plpydbapi.connect()
cursor = dbconn.cursor()
cursor.execute("SELECT ... FROM ...")
for row in cursor.fetchall():
    plpy.notice("got row %s" % row)
dbconn.close()
$$;

Granted, it's more verbose than the native PL/Python syntax, so you might not want to use it after all. But it can be helpful if database calls are nested in some other modules, or you just don't want to learn another database access API.

This started out more as an experiment, but it turns out that with the many improvements in PL/Python in PostgreSQL 9.1, it's possible to do this. (Subtransaction control and exception handling were the big issues.) The one gaping hole is that there is apparently no way to get metadata out of a query result. Something to address in PostgreSQL 9.2, perhaps.

Thanks go to the DB-API compliance test suite, which was extremely helpful in making this happen. (Nonetheless, the test suite is quite incomplete in some regards, so treat the result with care anyway.)

Another thing that I found neat about this project is that I managed to get the unit tests based on Python's unittest module to run in the PL/Python environment inside the PostgreSQL server. That's the power of unittest2.

Switching desktop environments

2011-11-12T17:27:00.001+02:00

So, like many people, I have received GNOME 3 in the latest upgrade on Debian testing, and switched away to Xfce very soon after. What's amazing (to me) about this whole experience, however, is how easy it is to switch desktop environments in Debian. Two years ago, I fled KDE in very similar circumstances. I have since used Openbox, LXDE, GNOME, Xfce, as well as a few more obscure window managers in short-lived experiments. All of this was easy to do and worked flawlessly. Most of my settings, my preferred programs, and startup applications were preserved, and if I didn't like it, I could switch back without a problem. (Ironically, the upgrades from KDE 3 to KDE 4 and from GNOME 2 to GNOME 3 lost far more settings than changes to other environments.) And of course you can mix and match applications from different environments, and these days this doesn't result in a visual crime. I don't know whom to thank for this, probably a combination of upstream developers, freedesktop.org guys, and Debian maintainers. Keep it up.

ccache and clang, part 2

2011-09-16T06:38:00.000+03:00

There's more funny business when using ccache in combination with clang. Last time I suggested that you use the invocation

./configure CC='ccache clang -Qunused-arguments -fcolor-diagnostics'

to get rid of the "argument unused during compilation" warnings.

But you still might get loads of warnings that you wouldn't normally get without ccache, such as this example (from the PostgreSQL source code):

extension.c:382:35: warning: equality comparison with extraneous parentheses [-Wparentheses]
 if (( (((control->directory)[0]) == '/') ))
        ~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~
extension.c:382:35: note: use '=' to turn this equality comparison into an assignment

(This is the opposite of the warning that tells you to put two pairs of parentheses around an assignment used as a truth value.) Or:

path.c:727:7: warning: explicitly assigning a variable of type 'char *' to itself [-Wself-assign]
 path = (path);
 ~~~~ ^  ~~~~

The problem is, these come from macro expansions, so wouldn't normally see them, because (I guess) the compiler driver is smart enough not to warn about such things when they come from macro expansions.

The way ccache works is approximately

preprocess the input file
look for it in the cache
if not found, compile the preprocessed file

What would be better in this situation is

preprocess the input file
look for it in the cache
if not found, compile the original file

And indeed you can turn on that second behavior by setting the obscure environment variable CCACHE_CPP2 (as in, run cpp twice):

export CCACHE_CPP2=yes

Then all these extra warnings disappear.

(The ccache man page is worth a read. There are a few interesting settings to play with.)

I'm currently playing around with a shell script ccache-clang that looks like this:

CCACHE_CPP2=yes exec ccache clang -Qunused-arguments `test -t 2 && echo -fcolor-diagnostics` "$@"

Beta work

2011-09-10T23:13:00.000+03:00

PostgreSQL 9.1.0 was wrapped the yesterday (or the day before yesterday, depending on from where you see it). It is now in a special place, where it can rest and let the flavor soak in, before it's released into the wild next week.

Time to reflect.

One thing that is sometimes criticized about the PostgreSQL release process is that there is too much time between the start of beta (or the end of the last commit fest) and the final release, and that it's not clear what ordinary contributors can do during that time to speed things up. This is now worked around to some degree by starting the next development cycle during the beta period of the previous one. (Evaluating the success of that approach is another matter.) But because this matter had also bothered me, I have taken notes this time, first of all so I don't forget anything next time, but perhaps also to provide some inspiration for others.

So here is what I'd been up to during the 9.1 beta period:

Documentation work

It is well understood that the documentation needs a general review during beta time. As features are patched in, chunks of documentation are added here and there, and at the end of it all you just need to stare at it for a bit and see whether the order and flow still make sense. I didn't do much of that this time, but I know others did. Other things to do are:

Various bits of SGML markup usually need to be cleaned up, e.g., alignment of verbatim environments, linking to formal objects.
I always run the whole documentation through a spell checker, which always results in a bunch of corrections.
The SQL features list in the documentation needs to be updated.
The SQL key words list in the documentation needs to be updated.
All generated man pages should be looked through manually for formatting artifacts.
Non-ASCII characters in the SGML source need to be checked. They should be converted to entities, and ideally they should fit into the Latin 1 set.

Other people also spent significant time on preparing the release notes, which is a huge job. I didn't even get into that, except that the spell checking and automated cleanups apply to the release notes as well, since they are part of the documentation build.

Source code cleanup

We run pgindent during beta, but there are also a number of other things to do to clean up the source code. A lot of these I didn't plan. Mostly, one thing led to another.

Check for non-ASCII characters in source. These should either be in UTF-8 or replaced by an ASCII equivalent.
Spell checking: You can't run the whole source through a spell checker, but there are also a few typical misspellings or variant spellings that are easy to clean up over the entire source tree, such as GIST -> GiST or cancelled -> canceled.
Check the --help and --version output of all programs to see whether it follows conventions.
In particular, check that the --help output is aligned, not too wide, and that the options are ordered sensibly. (Very often a new option is just added at the end or at some random place in the middle.)
Diff the --help outputs between the previous and the current version to find out about all new command-line options and check whether they are sensibly named and properly documented.
And the order in which options are presented in the --help output and on the man pages should not be gratuitously different.
Similarly, the backslash command help output in psql (\?) should be checked that it looks ok.
Go through the configuration file samples and check them for sanity, up-to-dateness, and proper formatting.
Remove literal tabs from string constants. How did they ever get in there?
Compile the code with a bunch of extra warning options. GCC has a lot of those, try them all out. A lot of them generate a lot of noise, but going through them about once a release does find the occasional trouble spot, or at least provides inspiration for future code cleanup projects. As of 9.1, you can also throw Clang into the mix.
Also, check building against cutting edge versions of library dependencies. Good candidates are Perl and Python, which introduce small breakage with each new release. If there is a new release candidate of those guys out while we prepare the PostgreSQL release, our users will be building against the final versions soon, so we need to be prepared.

Translation work

Besides the translation work itself and orchestrating the translator contributors, this is also the best chance to read through every single user-visible message that was introduced in the new version. So there are occasional spelling fixes, some messages are poorly worded or incomprehensible to a normal user, or sometimes the code can be restructured so that some error condition doesn't happen at all. A lot of this feeds back into source code cleanup items.

Help the packagers

We have a great bunch of packagers who prepare PostgreSQL for installation on various operating systems. Help them out. Make sure all new features are properly integrated, package descriptions are up-to-date, upgrades work.

And then of course you field submissions from testers and other contributors who will submit countless bug reports, failures on unusual platforms, documentation fixes.

So that can keep you busy for a month or three. The goal ought to be to automated most of this. But much of it can't be. So we really do need to spend the time to do these things, especially polishing the user-facing things. We don't want the first program output or documentation page the user sees of the new release to be ugly or wrong.

Undefined symbol

2011-07-19T23:10:00.000+03:00

Here is a small DBA tale from a few months ago. I wanted to upgrade a few servers to the latest minor release of PostgreSQL. It was the upgrade from 8.4.4 to 8.4.5. To minimize downtime, I had the following obvious idea: I'd install all the packages first, and once everything was in place and an opportune time for a service disruption had arrived, I'd just restart the services, and everything would be backup in less than a minute. (If you just let the package manager do its thing, it's always a bit unpredictable when the services are stopped and restarted, which might result several minutes of downtime.)

This operation had dramatic consequences. As soon as I had the new packages installed, I got massive amounts of these errors:

ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: PinPortal

Since a lot of things use PL/pgSQL, this meant that the database system was pretty much dead at this point.

What had happened?

Well, version 8.4.5 introduced some new functions in the server that PL/pgSQL made use of. As soon as you install the new plpgsql module, the next session that starts will load the new libary, but unless you restarted the server, the running server process won't have that symbol.

It was easy to fix this by restarting the server, but that's not how things were planned.

Consequence: Unless shown otherwise and a lot of care is taken, you must upgrade the server and all loadable modules to exactly matching versions and at the same time, and you must restart the server at the same time.

It might be useful as a preventive measure to compare symbol tables before upgrading. It could also be tracked more carefully at the source level, by explicitly mentioning the addition of meant-to-be-called-externally functions to the server in the release notes. (I suppose we don't usually remove functions, but who knows.)

Bootstrapping Debian packages

2011-06-23T22:08:00.000+03:00

I read something about dh_make again a while ago, which made me wonder, besides dh_make, how do people generally bootstrap new Debian packages. I don't think I've ever actually used dh_make for anything but experimenting. For the longest time, copying over the packaging files from some similar package worked pretty well.

Recently, however, I have applied a completely new strategy: I start with nothing, call debuild, fix the error, retry, until it works. This might sound pretty bizarre, but it works pretty well and enhances your understanding of how the packaging tools work.

Here is about how it works:

Start with nothing, call debuild, it complains:

cannot find readable debian/changelog anywhere!

Call dch --create, which says:

Cannot find debian directory!

OK, mkdir debian, call dch --create again, fill in the template. Call debuild again, now it says:

but there's no debian/rules there!

Create a debian/rules, that's easy to do by heart nowadays, at least as a start:

#!/usr/bin/make -f

%:
        dh $@

Call debuild again, now dpkg-buildpackage actually starts, but stops with:

dpkg-source: error: cannot read mypackage/debian/control: No such file or directory

At this point I'm too lazy to figure out what is necessary to put into debian/control, so let's just keep it simple and touch debian/control. At this point dpkg-source gets into a bit of trouble:

Use of uninitialized value $sourcepackage in sprintf at /usr/bin/dpkg-source line 290.

So let's put that in and also follow the subsequent advice to add debian/source/format and the Maintainer and Standards-Version fields. So far we have:

Source: mypackage
Maintainer: Peter Eisentraut <petere@debian.org>
Standards-Version: 3.9.2

The next hint of a message is:

dh: No packages to build.

This means we need a binary package stanza, so I'll just add

Package: mypackage
Architecture: any

Now debhelper complains:

dh: Compatibility levels before 5 are deprecated.

Not sure why it didn't complain about that earlier. Let's stick 8 in there.

At this point I had to do actual work and mess around with debian/rules a bit to get the package to actually build, but a few minutes later I had a functioning provisional package.

The next step is to clean up the warnings from the various dpkg-* workers:

dpkg-gencontrol: warning: missing information for output field Description
dpkg-gencontrol: warning: package mypackage: unused substitution variable ${shlibs:Depends}
dpkg-deb: warning: parsing file 'debian/mypackage/DEBIAN/control' near line 6 package 'mypackage': missing description
dpkg-genchanges: warning: missing Section for binary package mypackage; using '-'
dpkg-genchanges: warning: missing Priority for binary package mypackage; using '-'
dpkg-genchanges: warning: missing Section for source files
dpkg-genchanges: warning: missing Priority for source files

So we add Description, Depends, Section, and Priority.

And finally we have a list of complaints from Lintian to address:

W: mypackage source: debhelper-but-no-misc-depends mypackage
E: mypackage source: package-uses-debhelper-but-lacks-build-depends
W: mypackage source: package-needs-versioned-debhelper-build-depends 8
W: mypackage source: debhelper-overrides-need-versioned-build-depends (>= 7.0.50~)
E: mypackage source: temporary-debhelper-file debhelper.log
E: mypackage: no-copyright-file

The only things I added manually after that were Vcs-*, Homepage, and Enhances.

Now the only things left to do are running the thing through cowbuilder a few times and putting in all the necessary build dependencies, and writing a nice changelog entry.

Note, this method does not replace putting in some thought. But it's an interesting way to get a relatively clean package.

Enabling core files for PostgreSQL on Debian

2011-06-02T00:00:00.000+03:00

The other day, I was a bit puzzled over a seemingly simple task: Enable core files to be generated from a PostgreSQL instance running on Debian. That instance has unfortunately been segfaulting on occasion, but never left a core file.

Now in principle it is clear that

ulimit -c unlimited

is the incantation to get this done. But where do you put this? You could hack it into the init script, but that seemed a bit ugly, and I wanted a sustainable solution.

A useful thing in the meantime is to check the current settings. That information is available in /proc/$PID/limits with the PID of the postmaster process (or any child process, really), and it looked like this to begin with:

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            ms        
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
...

Use sudo grep core /proc/$(sudo cat /var/run/postgresql/8.4-main.pid)/limits if you want it automated.

So it's good to know that we only need to set the soft limit.

One way to configure this properly would appear to be in /etc/security/limits.conf. There you can add a line like

*               soft    core            unlimited

to enable core dumps globally. I'm not actually sure whether that would work if the service is started during the boot without PAM. In any case, I didn't want to enable core files globally; who knows what that would lead to.

One could replace the * by a user name, such as postgres, and then enable pam_limits.so in /etc/pam.d/su. But the postgresql init script in Debian is nested about four levels deep, so it wasn't clear whether it called su at all.

Now as it turns out, the init script ends up changing the user using this Perl code:

$) = $groups;
$( = $gid;
$> = $< = $uid;

(see change_ugid in /usr/share/postgresql-common/PgCommon.pm), so the whole PAM line of thought wasn't going to work anyway. (Other packages such as pgbouncer and slony1 do got through su, so that would be a solution for those.)

The way to solve this is the pg_ctl -c option, which sets the soft limit for core files to unlimited. And the way to pass this option through the init script maze is the file /etc/postgresql/8.4/main/pg_ctl.conf, which should contain a line like this:

pg_ctl_options = '-c'

Then restart postgresql, and check /proc/$PID/limits again:

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            ms        
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        unlimited            unlimited            bytes

OK.

Another thing that's recommendable in this context is to change the core file names to have a unique element, so that if multiple backends crash before you can take a look, they don't overwrite each other's core files. The core(7) man page explains the configuration options; I went with this sysctl setting:

kernel.core_pattern = core.%e.%p

which includes process name and PID. The PID file still ends up in the data directory of the PostgreSQL instance, which could also be changed, but I didn't find it necessary.

Stick the above line in /etc/sysctl.d/local.conf and reload with

service procps force-reload

I actually use a setting like that on all machines now; it's just nicer.

OK, and now I'll wait for the next core file. Or not.

ccache and clang

2011-05-05T22:38:00.000+03:00

Just a note for the Internet: When you use ccache and clang together, you will probably get a lot of warnings like these:

clang: warning: argument unused during compilation: '-c'
clang: warning: argument unused during compilation: '-I .'

These are harmless, but if you want to get rid of them, use the clang option -Qunused-arguments, which will hide them. (The first one is already fixed in ccache.)

The reason for this is that ccache splits the compilation into separate calls to the preprocessor and the compiler proper, and it tries to sort out which of the options that you called it with go with which call. But since gcc doesn't complain about passing -c to the preprocessor or -I to the compiler, ccache doesn't bother about sorting this out (bug). That's why you don't lose any information relative to using gcc if you use the -Qunused-arguments option.

Also, if you like clang's colored diagnostics messages, you'll have to turn them on explicitly with -fcolor-diagnostics, because when running through ccache, clang doesn't think it's printing to a terminal and turns off the color by default.

So a complete invocation might look like this:

./configure CC='ccache clang -Qunused-arguments -fcolor-diagnostics'