Peter Eisentraut's Blog

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-02T21:42: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'

Emacs fill column for PostgreSQL

2011-04-23T00:54:00.000+03:00

Since it's pgindent season, here's a small related tip for Emacs users.

(setq fill-column 79)

This will set the maximum line length for filling commands such as M-q to the same length that pgindent uses. That is especially useful when you want to reformat block comments nicely. The default is 70, which will cause perpetual conflicts between Emacs and pgindent.

The commit shows various other ways to include this in your Emacs configuration. (I use the c-add-style approach, personally.)

My new Git pre-commit hook

2011-03-10T22:43:00.000+02:00

This appears to be kind of useful:

#!/bin/sh

output=$(find . -name '.#*' -print)
if [ -n "$output" ]; then
        echo "unsaved Emacs files:" 1>&2
        echo "$output" 1>&2
        exit 1
fi

Had that kind of problem a few times. :-)

Now what would be really handy are system-wide Git hooks that apply to all repositories, like ~/.gitignore complements .git/info/exclude.

Squeeze + PostgreSQL = Broken

2011-02-06T23:26:00.000+02:00

The PostgreSQL package in Debian squeeze, just released, is linked with libedit instead of libreadline. This has two interesting properties:

If either of these is a concern, think carefully before you upgrade.

Is there a way to at least configure libedit to accept non-ASCII characters?

Going

2011-01-16T17:31:00.000+02:00

me too

Perception vs. Reality

2011-01-12T22:55:00.000+02:00

So this is interesting: In the Stack Overflow Annual User Survey, 62.2% of respondents claim they are "proficient" in SQL. This tops all the languages listed. This is perhaps not that surprising, but I had at the same time — subjectively — noticed an abundance of let's say clueless questions and suboptimal answers on SQL and RDBMS topics in the StackExchange network. Quite clearly SQL is somewhat different from algorithmic programming languages in that there is a gap between being familiar with the language and really understanding its effects.

Git commit mode

2011-01-08T00:40:00.001+02:00

Hardly anything ruins a glorious day of coding like fat-fingering the commit message late at night as you doze off, and then pushing it out for the world to see. To prevent that, I have equipped my Emacs configuration with a few little tools now.

First, I found the git-commit-mode, a special mode for Git commit messages. This helps you format the commit messages according to convention, and will use ugly colors if, for example, you write lines that are too long or you do not keep the second line blank. It also allows the use of things like M-q without shredding the whole file template.

Second, I integrated on-the-fly spell checking into the git-commit-mode. It won't stop you from writing nonsense, but it will catch the silly mistakes.

Here's a simple configuration snippet:

(require 'git-commit)
(add-hook 'git-commit-mode-hook 'turn-on-flyspell)
(add-hook 'git-commit-mode-hook (lambda () (toggle-save-place 0)))

The last line is handy if you have save-place on by default. When you make a new commit, it would then normally place the cursor where a previously edited commit message was finished, because to the save-place functionality, it looks as though it's the same file.

Development time

2010-12-02T22:05:00.000+02:00

Let's say you want to contribute to PostgreSQL development and want to play by the rules (which are actually not rules, but guidelines or encouragements), such as:

While a release is in beta, you work on finalizing the release, not on future projects.
During a commitfest, you work on testing and integrating the proposals submitted for the commitfest, not on new features.
Major features should not be submitted for the first time at the last commitfest.

The final release of PostgreSQL 9.0.0 was on 2010-09-20. By that time you already missed the first commitfest (2010-07), and the second commitfest (2010-09) was already under way. The second commitfest was actually slightly delayed and ended on 2010-10-26, whereas the third and next-to-last-for-9.1 commitfest (2010-11) started punctually on 2010-11-15.

That means if, while being a team player on all the community efforts, you wanted to develop a major new feature for PostgreSQL 9.1, you had a total of about 20 days to do it. (That is, if you didn't spend several days in early November at PgWest.) And that is within a one-year release cycle.

pipefail

2010-11-04T23:06:00.000+02:00

It is widely considered good style to include

set -e

near the beginning of a shell script so that it aborts when there is an uncaught error. The Debian policy also recommends this.

Unfortunately, this doesn't work in pipelines. So if you have something like

some_command | sed '...'

a failure of some_command won't be recognized.

By default, the return status of a pipeline is the return status of the last command. So that would be the sed command above, which is usually not the failure candidate you're worried about. Also, the definition of set -e is to exit immediately if the return status of the last pipeline is nonzero, so the exit status of some_command isn't considered there.

Fortunately, there is a straightforward solution, which might not be very well known. Use

set -o pipefail

With pipefail, the return status of a pipeline is "the value of the last (rightmost) command to exit with a non-zero status, or zero if all commands exit successfully". So if some_command fails, the whole pipeline fails, and set -e kicks in. So you need to use set -o pipefail and set -e together to get this effect.

This only works in bash, so if you're trying to write scripts that conform to POSIX or some other standard, you can't use it. (There are usually other ways to discover failures in pipelines in other shells, but none are as simple as this one, it appears.) But if you are writing bash anyway, you should definitely use it. And if you're not using bash but use a lot of pipelines in your scripts, you should perhaps consider using bash.

(Hmm, it looks like there could be a number of latent bugs in the existing Debian package maintainer scripts, because this issue appears to be widely ignored.)

Git User's Survey 2010 Results

2010-11-01T21:28:00.000+02:00

The results of the Git User's Survey 2010 are up.

Not many surprises, but I can see how this sort of survey is very useful for the developers of Git.

Git User's Survey 2010

2010-10-05T20:16:00.000+03:00

The Git User's Survey 2010 is up. Please devote a few minutes of your time to fill out the simple questionnaire; it'll help the Git community understand your needs, what you like about Git (and what you don't), and help improve it.

The survey is open from 1 September to 15 October, 2010.

Go to https://git.wiki.kernel.org/index.php/GitSurvey2010 for more information.

PostgreSQL and Flymake

2010-07-28T22:49:00.000+03:00

Flymake is an on-the-fly syntax checker for Emacs. Here is how you can use it for editing the PostgreSQL source code. Add this to your src/Makefile.custom:

check-syntax:
        $(COMPILE.c) -fsyntax-only $(CHK_SOURCES)

Then you can activate the flymake minor mode either manually using M-x flymake-mode, or by adding it to your Emacs customization for PostgreSQL. I use:

(defun pgsql-c-mode ()
  "C mode adjusted for PostgreSQL project"
  (interactive)
  (c-mode)
  (flymake-mode)

  ; .. and the rest ...
)

Here is a screenshot:

Notice the marked line with the syntax error and the tooltip/popup with the error message, which appears when the mouse hovers over the marked line.

Note, however, that since activating flymake will essentially cause a compiler to be running continuously in the background, this is not what you want to use when hacking PostgreSQL on the road. ;-)

Increasing the priority of Debian experimental

2010-07-03T07:23:00.000+03:00

Many people run Debian testing or unstable or some mix thereof. This works pretty well for a development system or a general desktop system if you know a bit about what you're doing (note: nonetheless officially not recommended). Sometimes you throw packages from experimental into the mix, if you want to get the latest stuff that isn't yet fully integrated into the rest of Debian.

The default APT priority of the Debian experimental release is 1, which ensures that it is never automatically installed or upgraded. This is not always ideal, in my experience. Of course, you don't want a package from experimental to take precedence over a package from stable, testing, or unstable by default. But I think when you have in the past installed a package from experimental, you probably want to pull in upgrades to that package from experimental as well. Otherwise, you will end up with completely unmaintained packages on your system. That is because in practice many packages in experimental are not actually experimental or broken or unmaintained, but just an advance branch of some software that is just for some reason not ready to go down the unstable-testing-stable road.

To make this work better, I have set the priority of experimental to 101 in my /etc/apt/preferences:

Package: *
Pin: release experimental
Pin-Priority: 101

Now the following will happen: If you just apt-get install a package, it will come from whatever "normal" release you have in your sources.list, say stable or testing. You can override that using -t experimental as usual. If you install a package from experimental and later an upgrade is available in experimental, apt-get upgrade will install that automatically. Also, if an upgrade in a "normal" release appears that has a higher version number, that version will be installed.

Of course, caveats apply. Some software in experimental is really experimental and should only be installed under close supervision. If a package is available only in experimental, this setup will install it when you ask for the package, even if you might not have actually wanted it if you had known that it was in experimental. Figure it out yourself. :)

Similar considerations apply to backports. I use

Package: *
Pin: release a=lenny-backports
Pin-Priority: 102

On the system I have in mind here, the standard distribution is stable, testing is 101, and backports is 102, taking precedence over testing. Because for some architecture-independent packages you don't need backports, so you can pull them directly from testing that way.

In general, the APT priority business is relatively powerful and often a good alternative to, say, manually downloading packages from various distributions, installing them manually, forgetting where they came from, and never upgrading them.

An Update On Views

2010-07-01T05:50:00.001+03:00

A long time ago, in a text book far away, someone described the following (relational) database design approach: Put all your data in tables, normalized and so on, as you'd expect. An top of each table, create a view. The database clients only access the views. As the application evolves, tables might change, more views will be added, but the interface that the existing views present to the outside stays the same. Sounds nice; everything stays backward compatible.

In order for this to work, views need to be updatable. Updatable views have been standardized in SQL as far back as 1992, and have been implemented (partially, possibly) in some of the popular/successful SQL implementations. Curiously, however, this feature has evaded PostgreSQL altogether so far. Conventional wisdom has held it that views can be made updatable by hand using the rewrite rules system, and a proper implementation of automatically updatable views would only need to create the rules automatically. Debate and coding around this idea has been taking place for many years, mostly inspired by the heroic efforts of Bernd Helmle. But I think the conclusion of those in the know by now is that the rewrite rules system is inappropriate for this task (and in fact others have argued that the rewrite rules system is inappropriate for any task and should be ripped out; perhaps harsh but not far from the truth in my opinion). So the updatable views effort is at square one (or zero) at the moment.

In the meantime, updatable views have been conspicuously absent from any of the recent top-needed features list for PostgreSQL. Which basically means no one really cares (anymore). Why is that?

My observation is that the typical use of views has changed quite a bit of the years (decades), and has decreased altogether. The idea of using views as transparent interfaces on top of tables never really took off to begin with. Using views to encapsulate complex queries is still reasonable, but not terribly common. Part of the reason is that many queries are generated, either by ORMs or by custom code. The other reason is that server-side functions provide a much more powerful abstraction mechanism. They can't do everything, in particular inlining of queries doesn't alway work, but they can do a lot more with parameters and general procedural programming. Views are also used for access control, either for columns or for rows. Column security can now be had with explicit column privileges in PostgreSQL, and one could argue that using views for this was a workaround all along. And using views for row access control doesn't actually really work, as it turns out. Proper row security might be an upcoming feature, but it will need to work differently. And anyway, superfinely granular access control is rather rare in SQL databases overall, I guess. And in all of these cases, the views don't need to be updatable.

Views are occasionally used as a thin wrapper around a table-returning function, so the function can be access like a table. But this only works when reading. Another use, somewhat related actually, is defining views for monitoring the database system or the database application. Because those views are often used interactively, it is
important that they are convenient to access, and so hugely complex queries combined with custom functions or possibly query generation are inappropriate.

On the other hand, materialized views are all the rage now, appearing at the top of the requested PostgreSQL feature list, and having been fixtures in the feature lists of other SQL implementations for some time. While traditional views are a tool for organizing code and interfaces, materialized views are purely a performance optimization tool (which, by the way, is the reason materialized views are not
specified in the SQL standard). Depending on how you implement materialized views, they might even be usable implicitly, if the query matches the materialized query. Then, materialized views wouldn't really be views at all anymore, they're just query caches.

So, what do you think? Are traditional views an obsolescent, marginal feature? Is the matter of updatable views obsolete?

System-Versioned Tables

2010-05-28T23:24:00.000+03:00

After my report on the upcoming SQL:2011, some people had asked me about the system-versioned table feature that is going to be the arguably only major new feature there. Here is how it works:

CREATE TABLE tab (
    useful_data int,
    more_data varchar,
    start timestamp GENERATED ALWAYS AS SYSTEM VERSION START,
    end timestamp GENERATED ALWAYS AS SYSTEM VERSION END
) WITH SYSTEM VERSIONING;

(This hilariously verbose syntax arises because this is defined so that it fits into the more general generated columns feature, e. g., GENERATED ALWAYS AS IDENTITY, similar to PostgreSQL's serial type.)

INSERT INTO tab (useful_data, more_data) VALUES (...);

This sets the "start" column to the current transaction timestamp, and the "end" column to the highest possible timestamp value.

UPDATE tab SET useful_data = something WHERE more_data = whatever;

For each row that would normally be updated, set the "end" timestamp to the current transaction timestamp, and insert a new row with the "start" timestamp set to the current transaction timestamp. DELETE works analogously.

SELECT * FROM tab;

This only shows rows where current_timestamp is between "start" and "end". To show the non-current data, the following options are
possible:

SELECT * FROM tab AS OF SYSTEM TIME timestamp;
SELECT * FROM tab VERSIONS BEFORE SYSTEM TIME timestamp;
SELECT * FROM tab VERSIONS AFTER SYSTEM TIME timestamp;
SELECT * FROM tab VERSIONS BETWEEN SYSTEM TIME timestamp AND timestamp;

There's also the option of

CREATE TABLE tab ( ... ) WITH SYSTEM VERSIONING KEEP VERSIONS FOR interval;

to automatically delete old versions.

That's more or less it. It's pretty much xmin/xmax/vacuum on a higher level with timestamps instead of numbers. And it's a revival of the old time travel feature. Obviously, you can do most or all of this with triggers already.

PostgreSQL Package Management

2010-05-19T22:00:00.000+03:00

Database development sucks, and I would like to fix it. I don't mean database system software development; that's awesome. ;-) I mean database development as in writing the tables, views, functions, and other code that make up your database. We have come further in recent years in PostgreSQL land. We have had the PL/pgSQL debugger, there is Piggly for PL/pgSQL code coverage, we have pgTAP for unit testing support, we have in Post Facto a version control system running inside the database, although I'll try to explain below why I think that that is not the right solution. My problem is that getting database code from the editor to the database server in a safe manner is pretty difficult. This already starts with deploying simple database code to a single server for the first time (as shown in this entry), and gradually gets more complicated when you need to update existing installations, manage multiple servers, or even multiple versions of that code.

My answer to that problem is an old friend: package management. Package managers such as dpkg and rpm are pretty well-established solutions and have shown over the years that managing software deployments can be easy and occasionally even fun.

Consider how the development and deployment of normal software proceeds:

You edit some files in your text editor.
You execute some kind of build step (could be compilation or more trivial).
Maybe you run a test suite.
You check your files into version control.

And then you or someone else, perhaps a release manager or system administratior does something like this:

Tag a release.
Export the tag from version control.
Build the software.
Run tests.
Build a package.
Push package to repository.
Install/upgrade package on deployment machines.

There are of course sites that do this differently. One could, for example, just copy the built files onto the target machines. But if you do that, then you might as well be happy with the current state of SQL deployment. Many sites are going for continuous deployment nowadays, in which case you might not need a way to track what version of the code is currently running, but you will still want a way to make deployments and upgrades in a safe manner. My premise is that the development cycle of database software should work in pretty much the same way as for other software. But we are only slowly getting the tools to support this work flow. We have editors that allow editing SQL, of course. We don't really need any new build tools; the existing ones appear to serve just fine. We have testing support, getting better all the time. At this point it was once thought that perhaps a database-based version control system such as Post Facto would address the remaining issues. After considering that for a while I have figured out that it doesn't. The main problems with that approach are:

Abuses version control system as a deployment tool. Some people deploy code by svn checkout, but most don't.
Does not allow for a build step (in a straightforward manner). The database is the source code. You can't generate your database from some XML source, for example.
I consider it preferable to put database code and related non-database code in the same version control system, so they can be developed, browsed, deployed, etc. together.

A database-based version control system might actually be the thing for some people, just like other version control systems serve different needs. (Except, note that mine is better than yours! ;-) )

So I think what the world needs is a package manager for databases, that manages database objects just like a package manager for operating systems manages files. And it should work in much the same ways, like this:

Conceptually, there are two major modes of operation: building a package and installing a package (think rpmbuild and rpm, or dpkg-buildpackage and dpkg).
Building a package is controlled by a special instruction file (think RPM spec file or debian/ directory).
Building a package involves running that software's normal build routine and installing it into a temporary staging area. In simple cases, this is running psql -f somefile.sql, but it could be anything. You could have an ORM tool generate the database.
The package file is an archive file containing the contents of that staging area plus some metadata.
Installing a package unpacks the archive file and places the contents into the designated destination, while sensibly dealing with existing installations.

There are additional features of well-known package managers that could be of interest in a database environment: pre/post scripts for additional setup, relocations (perhaps for selecting installation schemas), integration with a repository manager (like yum or apt).

So I have set out to write such a tool and have arrived at a small prototype. Here is how it works:

$ pgpkg build
 pgpgk/rules clean
make: Nothing to be done for `clean'.
 pgpgk/rules build
make: Nothing to be done for `build'.
 pgpgk/rules binary
psql -X -q -f install_foo.sql
pgpkg create
wrote ../foo_1.0.pgpkg

This build process is currently driven by a Debian-style rules and control file. An RPM-style spec file might also be possible.

$ pgpkg contents ../foo_1.0.pgpkg
{'Client-Encoding': 'UTF8', 'Version': 1.0, 'Format': '0.0', 'Package': 'foo'}
CREATE FUNCTION "public"."test1"(integer) RETURNS integer LANGUAGE "sql" VOLATILE AS $$select 1$$;
CREATE FUNCTION "public"."test2"(integer) RETURNS integer LANGUAGE "sql" VOLATILE AS $$select 2$$;
CREATE FUNCTION "public"."test3"(integer, integer) RETURNS integer LANGUAGE "sql" VOLATILE AS $$select 3$$;

$ pgpkg install -d 'dbname=bar' ../foo_1.0.pgpkg

$ pgpkg remove -d 'dbname=bar' foo

There are more features planned, such as diffing packages, but I haven't gotten to those yet. Also, don't get too excited yet, it only handles a very small subset of SQL objects. Much more code needs to be added to handle everything, but that will mostly be a typing exercise (famous last words).

Ah, but what about this extension packaging business that has been discussed for the last couple of years? Well, I have been staring at the various design documents for that same couple of years now, and it's not going anywhere and it's not really set out to solve my problem. Partially by coincidence, partially by intent, as I'm posting this the guys are sitting in Ottawa discussing extension packaging yet again, so at least take this as a challenge to get something going. :-)

Note that I am calling this thing a "package", because that's what it is. It's not (necessarily) an "extension" (although an extension could be packaged as a package), and it's not a "module" (that term is reserved by SQL; what Oracle calls a package is actually a module).

Also note that this is implemented as an external program, not in the database server. I think the operating system shell is a much more usable and versatile environment. Think of all the things you might want to do: build many packages, compare packages, install some/many/all packages, grep package contents, copy, download, or upload packages. This makes most sense from the shell. If you think about it, neither dpkg nor rpm require extensions in the kernel or the file system to do their work; the same applies here. Another advantage is that the implementation can evolve faster and doesn't need to wait for say PostgreSQL 9.1 to be out.

Anyway, for the moment, forget about extensions. Think about the database application code that you need to move around. Does the above approach make sense? Could it be useful? Ideas, suggestions?

Visual Explain Reloaded

2010-05-18T08:44:00.000+03:00

One of the new features in PostgreSQL 9.0 is EXPLAIN output in different markup formats (XML, JSON, YAML). The idea is that this makes it easier for programs (as opposed to humans) to parse the output.

Using one of the standard teaching examples, compare the outputs:

regression=> EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=227.95..700.46 rows=101 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..434.00 rows=10000 width=244)
   ->  Hash  (cost=226.68..226.68 rows=101 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.03..226.68 rows=101 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.01 rows=101 width=0)
                     Index Cond: (unique1 < 100)
(8 rows)

regression=> EXPLAIN (FORMAT JSON) SELECT * FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                     QUERY PLAN
-----------------------------------------------------
 [                                                  +
   {                                                +
     "Plan": {                                      +
       "Node Type": "Hash Join",                    +
       "Join Type": "Inner",                        +
       "Startup Cost": 227.95,                      +
       "Total Cost": 700.46,                        +
       "Plan Rows": 101,                            +
       "Plan Width": 488,                           +
       "Hash Cond": "(t2.unique2 = t1.unique2)",    +
       "Plans": [                                   +
         {                                          +
           "Node Type": "Seq Scan",                 +
           "Parent Relationship": "Outer",          +
           "Relation Name": "tenk2",                +
           "Alias": "t2",                           +
           "Startup Cost": 0.00,                    +
           "Total Cost": 434.00,                    +
           "Plan Rows": 10000,                      +
           "Plan Width": 244                        +
         },                                         +
         {                                          +
           "Node Type": "Hash",                     +
           "Parent Relationship": "Inner",          +
           "Startup Cost": 226.68,                  +
           "Total Cost": 226.68,                    +
           "Plan Rows": 101,                        +
           "Plan Width": 244,                       +
           "Plans": [                               +
             {                                      +
               "Node Type": "Bitmap Heap Scan",     +
               "Parent Relationship": "Outer",      +
               "Relation Name": "tenk1",            +
               "Alias": "t1",                       +
               "Startup Cost": 5.03,                +
               "Total Cost": 226.68,                +
               "Plan Rows": 101,                    +
               "Plan Width": 244,                   +
               "Recheck Cond": "(unique1 < 100)",   +
               "Plans": [                           +
                 {                                  +
                   "Node Type": "Bitmap Index Scan",+
                   "Parent Relationship": "Outer",  +
                   "Index Name": "tenk1_unique1",   +
                   "Startup Cost": 0.00,            +
                   "Total Cost": 5.01,              +
                   "Plan Rows": 101,                +
                   "Plan Width": 0,                 +
                   "Index Cond": "(unique1 < 100)"  +
                 }                                  +
               ]                                    +
             }                                      +
           ]                                        +
         }                                          +
       ]                                            +
     }                                              +
   }                                                +
 ]
(1 row)

For just reading the plan, the new formats are probably not better, although some might prefer them. The real win comes when you can feed this to a program to create a visualization. With the old format, parsing the output was complicated and error prone. With the new formats, it is easy. In fact, it was so easy that I couldn't resist writing a small visual explain program that renders plans through the graphviz library. Here is how you can use it:

regression=> \a\t
regression=> EXPLAIN (FORMAT JSON) SELECT * FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 \g |veung

Result:

Simple and useful. Get it from GitHub.