Andrew's PostgreSQL blog

A little while ago I whipped up a quick buildfarm module to run basic pg_upgrade testing using the builtin "make check" target for pg_upgrade, or its MSVC equivalent. There's only one problem with this: it doesn't check what pg_upgrade is really for, which is upgrading from different versions. And I am seeing pg_upgrade used more and more, so testing cross-version upgrade is really quite important, and not having it is a serious hole in our testing regime.

Our standard testing framework isn't really able to cope with cross-version testing. But the buildfarm knows all about handling different versions. Almost all buildfarm animals regularly build and test multiple versions of postgres. The only trouble is that they throw away all their work product at the end of each run.

So some time ago I started creating a framework that would keep the work product from the latest run on each branch, and then a week or so ago I started reworking that into a module that would both do that and use it to test upgrading to different versions. There is still some work to go, but it's basically working, using logic similar to the builtin pg_upgrade test. Here is sample output from a run on my test animal this morning:

Tue Nov 27 10:33:01 2012: buildfarm run for crake:HEAD starting
[10:33:01] checking out source ...
[10:33:01] checking out FileTextArrayFDW
checkout complete
[10:33:01] checking if build run needed ...
[10:33:01] creating vpath build dir pgsql.31025 ...
[10:33:01] copying source to  ...file_text_array_fdw.31025
[10:33:01] running configure ...
[10:33:20] running make ...
[10:34:29] running make check ...
[10:34:55] running make contrib ...
[10:35:02] running make install ...
[10:35:08] running make contrib install ...
[10:35:09] building FileTextArrayFDW
[10:35:10] installing FileTextArrayFDW
[10:35:10] setting up db cluster (C)...
[10:35:13] starting db (C)...
[10:35:14] running make installcheck (C)...
[10:35:37] saving files for cross-version upgrade check
[10:35:42] checking upgrade from REL9_0_STABLE to HEAD ...
***SUCCESS!
[10:36:06] checking upgrade from REL9_1_STABLE to HEAD ...
***SUCCESS!
[10:36:31] checking upgrade from REL9_2_STABLE to HEAD ...
***SUCCESS!
[10:37:07] install-checking FileTextArrayFDW
[10:37:08] stopping db (C)...
[10:37:09] OK

The complete module will be published in an upcoming buildfarm client release. The downside is that to use it you need to have a bit of extra disk space to spare - probably a couple of gigabytes or more to be on the safe side.

The module is limited to testing upgrades from PostgreSQL 9.0 and up. The reason for this is the extra_float_digits mess (see pg_upgrade's TESTING document for details).

One problem is that we check for success by comparing the diff size between pre-upgrade and post-upgrade dumps. At the moment the expected sizes (numbers of lines) are hard coded. That means that a change in the code that works could well lead to a break in the buildfarm, which is rather against our principles, so I'm thinking of publishing the expected diff sizes on the buildfarm server, just as we do for the list of branches of interest, and having the module fetch it. That poses a few problems, especially for the Mingw environment, but I think it can be solved.

Version 4.9 of the PostgreSQL buildfarm client has been released and is available to download at https://github.com/downloads/PGBuildFarm/client-code/build-farm-4_9.tgz.

Changes since version 4.8:

• adjust git status checking to allow for messages from recent git releases
• add a module to test FileTextArray_FDW as an example of checking an extension
• a few small bug fixes and tweaks

You will need this release if you are using or likely to use a fairly recent git version to avoid getting spurious git-Dirty failures.

Enjoy

One of the things for us to note as database professionals about the Obama victory is that it has been incredibly data driven. Obama set new standards with his use of social media and data driven technology four years ago, and this year took it to new levels. Like it or not, this is the future of campaigning, and databases are going to be right at the centre of it. Databases often aren't seen as being all that sexy, but it's exciting for what we do to be at the heart of big events.

Recently someone asked the old question about selecting data from different databases. This commonly comes up from users of MySQL, and the usual response is that they should put all the data in a single database in different schemas, and that a MySQL database is like a schema as the term is used by the SQL standard, whereas a Postgres database is like what the standard calls a catalog, containing many schemas. This answer is true and reasonable, as far as it goes. But it got me thinking a bit. One of my clients has a database that makes extensive use of schemas. They have a handful of huge tables that are public, and then a schema for each customer with some more customer-specific tables. The common data comprises about 80% or more of the total database size, even though it's a tiny number of actual tables. The way we have this set up works fairly well, but as their customer base grows the number of tables is getting bigger and bigger. It's doubled in the last couple of years, and the number of tables is getting to be a bit of an issue. It would be really nice if we could give each customer their own database, but we can't, because they all need this common pool of data. If somehow we could easily and efficiently access data from sibling databases we could do this nicely. So it's not just refugees from other databases who would like something along these lines.

Today I discovered that there can be dire consequences from having an improperly formatted Certificate Revocation List file. The consequence seems to be that if you do then all connections are rejected. With a little help from Magnus that pointed me in the right direction, I got a CRL file that was formatted correctly, and then certificates that were signed by the CA contained in the CRL were rejected, while other certificates signed by a different CA were accepted. I'm not sure if this behaviour is desirable, but it's essentially out of our control. The best advice is to make sure the format is correct before you install the file. The best way to do that is something like:

openssl crl -in my_crl.pem -text

The other day some problems were observed with inherited constraints, and the following test case failed:

CREATE TABLE test (a float check (a > 10.2));
CREATE TABLE test_child() INHERITS(test); 
ALTER TABLE test ALTER COLUMN a TYPE numeric;

with an error like this:

ERROR:  constraint must be added to child tables too

So, where did this originate? There seemed to be a bit of confusions about it. One way to find out is to make git find it for you. You need a little script to hand git, and a tiny bit of setup and away you go. Unfortunately the script took a little more getting right than it should have, so I ran out of time on Friday before I had to leave town for the weekend. But I got it working this morning. Here's what I did. First, I set up the bisect parameters. This was known to work OK in release 8.3 and to have broken some time after that. So the setup looked like this:

git bisect start HEAD REL8_3_0

Then I needed a script to build postgres and run the test. This script needed to exit with a status of 125 if we didn't get to the test stage, to tell git bisect to skip this particular commit, and exit with a zero status if the test passed and non-zero if it failed. Here's what I used:

#!/bin/env  bash
start=`pwd`

function error_exit {
	cd $start
	rm -rf $start/testme.$$
	git clean -dfx src/interfaces/ecpg/preproc
	exit 125
}

exec 1>log.$$ 2>&1

mkdir testme.$$
cd testme.$$
../configure --with-pgport=58241 --prefix=`pwd`/inst || error_exit
make || error_exit
make install || error_exit
cd inst
bin/initdb data
bin/pg_ctl -D data -l logfile -w start || error_exit
bin/createdb test
echo '
\set ON_ERROR_STOP
CREATE TABLE test (a float check (a > 10.2));
CREATE TABLE test_child() INHERITS(test); 
ALTER TABLE test ALTER COLUMN a TYPE numeric;
' > testscript
bin/psql -f testscript test
worked=$?
bin/pg_ctl -D data -l logfile stop
cd ../..
rm -rf testme.$$
git clean -dfx src/interfaces/ecpg/preproc
exit $worked

Then I ran:

git bisect run find_error.sh

And let it run while I went on with other stuff. Eventually, after around 22 script invocations (including skips) it came back with this:

cd902b331dc4b0c170e800441a98f9213d98b46b is the first bad commit
commit cd902b331dc4b0c170e800441a98f9213d98b46b
Author: Tom Lane 
Date:   Fri May 9 23:32:05 2008 +0000

    Change the rules for inherited CHECK constraints to be essentially the same
    as those for inherited columns; that is, it's no longer allowed for a child
    table to not have a check constraint matching one that exists on a parent.
    This satisfies the principle of least surprise (rows selected from the parent
    will always appear to meet its check constraints) and eliminates some
    longstanding bogosity in pg_dump, which formerly had to guess about whether
    check constraints were really inherited or not.
    
    The implementation involves adding conislocal and coninhcount columns to
    pg_constraint (paralleling attislocal and attinhcount in pg_attribute)
    and refactoring various ALTER TABLE actions to be more like those for
    columns.
    
    Alex Hunsaker, Nikhil Sontakke, Tom Lane

:040000 040000 fa425986df22897d6ca41835955890b3abcad3e8 d1b0f57696679ccd08e977380d53ed0f01db5a37 M	doc
:040000 040000 4b72e596c62f52ef841375cbe9289fab31089cfd 7ba72386373a9ed05b10ca6188a4f6497e08b207 M	src
bisect run success

I've used this bisect facility a couple of times in the past looking for obscure needles in the postgres haystack of commits (although not usually looking back so far as this). It's pretty neat.

A client today complained that they couldn't get SSL connections working with certificates signed with by non-root CA certs. I've never tried this before, although I have more than once successfully used my own generated root CA certificates. And when I tried I couldn't get it working either. Either something is broken in what I did, or the mechanism is somehow broken. The intermediate CA cert does show the root cert as its Authority, so I think I have done it right:

        X509v3 extensions:
            X509v3 Subject Key Identifier: 
                03:E8:CD:AA:4E:C6:04:A9:B4:6C:CB:A2:50:1E:A3:FB:1C:E4:88:AF
            X509v3 Authority Key Identifier: 
                keyid:67:55:5B:92:3F:2E:AD:79:7E:50:0C:A5:D3:77:E0:2F:24:F3:76:57

            X509v3 Basic Constraints: 
                CA:TRUE

The second key ID shown here is indeed that of the root CA key.

If anyone has a good working example, I'd greatly appreciate it.