Perl Blog Archive

Localize $@ in DESTROY

I have been conditioned now for many years in Perl to trust the relationship of $@ to its preceding eval. The relationship goes something like this: if you have string or block eval, immediately after its execution, $@ will either be false or it will contain the die message of that eval (or the generic "Died at ..." message if none is provided). Implicit here is that evals contained within an eval have their effects on $@ concealed, unless the containing eval "passes on" the inner eval's die.

To quickly demonstrate:

  1 use strict;
  2 use warnings;
  3 
  4 eval {
  5     print "Some stuff\n";
  6     eval {
  7         die 'Oops. Bad inner eval';
  8     };
  9 
 10     printf '$@ in outer eval: %s', $@;
 11 };
 12 
 13 printf '$@ after outer eval: %s', $@;
 14 print $/;

produces the following output:

[mark@sokt ~]$ perl demo.pl 
Some stuff
$@ in outer eval: Oops. Bad inner eval at demo.pl line 7.
$@ after outer eval: 
[mark@sokt ~]$ 

Only if the containing eval itself dies do we find any data in $@:

  1 use strict;
  2 use warnings;
  3 
  4 eval {
  5     print "Some stuff\n";
  6     eval {
  7         die 'Oops. Bad inner eval';
  8     };
  9 
 10     printf '$@ in outer eval: %s', $@;
 11     die 'Uh oh. Bad outer eval, too';
 12 };
 13 
 14 printf '$@ after outer eval: %s', $@;
 15 print $/;

which produces:

[mark@sokt ~]$ perl demo.pl 
Some stuff
$@ in outer eval: Oops. Bad inner eval at demo.pl line 7.
$@ after outer eval: Uh oh. Bad outer eval, too at demo.pl line 11.

[mark@sokt ~]$ 

Why am I covering this, well known to any serious Perl programmer? Because I was caught off guard troubleshooting for a client last week when the result of an inner eval "leaked" through, affecting $@ of the containing eval. Because I was so conditioned to the stated relationship between eval and $@, it took me quite some time before I even opened up to the possibility.

It turned out the hitch had to do with garbage collection. The key was that the inner eval in question was initially called from a routine within an object's DESTROY method. As I discovered, at least in Perl 5.10, if a containing eval dies, causing an object to go out of scope, and that object's DESTROY itself executes an eval, $@ reflects the exit condition of the eval from within DESTROY, and not that of the containing eval. Even more strange, this is only true if the containing eval dies. If instead the containing eval completes, then that same dying eval within DESTROY does not affect the condition of $@ after the containing eval. It will still be false, as (IMO) it should be.

So, some code demonstrating each situation. We have 3 conditions:

• Containing eval dies, eval within DESTROY dies
• Containing eval dies, eval within DESTROY does not die
• Containing eval does not die, and eval is called within DESTROY, die or not.

Sample code demonstrating 1st condition:

  1 use strict;
  2 use warnings;
  3 
  4 package Obj;
  5 
  6 sub DESTROY {
  7     eval { die 'in DESTROY' };
  8 }
  9 
 10 package main;
 11 
 12 eval {
 13     my $obj = {};
 14 
 15     bless $obj, 'Obj';
 16     die 'in main eval';
 17 
 18     print "Super important stuff that must finish or we really need to know about it!\n";
 19 
 20     return 1;
 21 };
 22 
 23 if ($@) {
 24     printf '$@ comes from code %s', $@;
 25 }
 26 else {
 27     print "Happy days! Our eval code ran to completion. Woot!\n";
 28 }

Output as follows:

[mark@sokt ~]$ perl test1.pl 
$@ comes from code in DESTROY at test1.pl line 7.
[mark@sokt ~]$ 

Demo of 2nd condition:

  1 use strict;
  2 use warnings;
  3 
  4 package Obj;
  5 
  6 sub DESTROY {
  7     eval { 1 };
  8 }
  9 
 10 package main;
 11 
 12 eval {
 13     my $obj = {};
 14 
 15     bless $obj, 'Obj';
 16     die 'in main eval';
 17 
 18     print "Super important stuff that must finish or we really need to know about it!\n";
 19 
 20     return 1;
 21 };
 22 
 23 if ($@) {
 24     printf '$@ comes from code %s', $@;
 25 }
 26 else {
 27     print "Happy days! Our eval code ran to completion. Woot!\n";
 28 }

Output as follows:

[mark@sokt ~]$ perl test2.pl 
Happy days! Our eval code ran to completion. Woot!
[mark@sokt ~]$ 

Notice how particularly insidious the above is. We not only don't know what the error was from the eval block that immediately precedes the evaluation of $@, but we actually think it succeeds!

Finally, the 3rd condition:

  1 use strict;
  2 use warnings;
  3 
  4 package Obj;
  5 
  6 sub DESTROY {
  7     eval { die 'in DESTROY' };
  8 }
  9 
 10 package main;
 11 
 12 eval {
 13     my $obj = {};
 14 
 15     bless $obj, 'Obj';
 16 
 17     print "Super important stuff that must finish or we really need to know about it!\n";
 18 
 19     return 1;
 20 };
 21 
 22 if ($@) {
 23     printf '$@ comes from code %s', $@;
 24 }
 25 else {
 26     print "Happy days! Our eval code ran to completion. Woot!\n";
 27 }

Output as follows:

[mark@sokt ~]$ perl test3.pl 
Super important stuff that must finish or we really need to know about it!
Happy days! Our eval code ran to completion. Woot!
[mark@sokt ~]$ 

So, fortunately, case 3 contains the leak when the outer eval completes successfully. We don't introduce the worst possible situation: a successful eval that is subsequently treated as a failure. However, cases 1, and especially 2, are bad enough.

Now that I know all this, the solution is thankfully simple. When constructing objects, if they include a supplied DESTROY, always localize $@. It doesn't matter whether I execute any evals or not; if the code calls any other routines that do, anywhere in the stack, the problem is introduced. A local $@ provides full protection.

A rerun of test1 but with localization provides a much more expected result:

  1 use strict;
  2 use warnings;
  3 
  4 package Obj;
  5 
  6 sub DESTROY {
  7     local $@;
  8     eval { die 'in DESTROY' };
  9 }
 10 
 11 package main;
 12 
 13 eval {
 14     my $obj = {};
 15 
 16     bless $obj, 'Obj';
 17     die 'in main eval';
 18 
 19     print "Super important stuff that must finish or we really need to know about it!\n";
 20 
 21     return 1;
 22 };
 23 
 24 if ($@) {
 25     printf '$@ comes from code %s', $@;
 26 }
 27 else {
 28     print "Happy days! Our eval code ran to completion. Woot!\n";
 29 }

Output as follows:

[mark@sokt ~]$ perl test1.pl 
$@ comes from code in main eval at test1.pl line 17.
[mark@sokt ~]$ 

and test2, which now doesn't lie to us about the success of the eval of interest:

  1 use strict;
  2 use warnings;
  3 
  4 package Obj;
  5 
  6 sub DESTROY {
  7     local $@;
  8     eval { 1 };
  9 }
 10 
 11 package main;
 12 
 13 eval {
 14     my $obj = {};
 15 
 16     bless $obj, 'Obj';
 17     die 'in main eval';
 18 
 19     print "Super important stuff that must finish or we really need to know about it!\n";
 20 
 21     return 1;
 22 };
 23 
 24 if ($@) {
 25     printf '$@ comes from code %s', $@;
 26 }
 27 else {
 28     print "Happy days! Our eval code ran to completion. Woot!\n";
 29 }

Output as follows:

[mark@sokt ~]$ perl test2.pl 
$@ comes from code in main eval at test2.pl line 17.
[mark@sokt ~]$ 

Mock Testing with Perl

I'll start by saying that I probably should have started with Test::MockObject and saved myself all of this trouble. But sometimes things don't work out that way.

So, I'm building unit tests in Perl the hard way. By the hard way, I mean that I am constructing ever more elaborate, interdependent, complex, and brittle test data sets to test the functions that I am hacking on. The data model is moderately complex, so there really isn't any way around it (since I'm doing it the hard way, after all).

At one point, one function (which I am not testing) returns a result that I need for the function I am testing. The problem is that it reaches pretty far away into a section of the data model that I'd rather not set up test data for at the moment just to get that one value. This is where I'm sitting there wishing I had mock objects more than usual, since this would be a perfect place to mock the method. Since I couldn't be bothered to see if someone had written such a handy module, I looked for a hard way to do it. Turns out that there is one.

Its not actually hard, but it could be considered complex if you are not familiar with typeglobs and the workings of the symbol table in Perl. A good discussion can be found in the Perl Cookbook in ch10.14.

In the following example, the function Base::Shipping::Package::weight is called at some point in create_shipment. Being able to call it is imperative to completing create_shipment. In my case, I have to have a successful result from create_shipment in order to test process_shipment.

{
  local *Base::Shipping::Package::weight = \&test_weight
  my $shipment = $class->create_shipment($shipment);
  my $result = $class->process_shipment($shipment);
  test $result;
}

sub test_weight { 4.0 }

In here then, the local call redefines the weight function inside the scope of the block. This turns out to be fairly convenient given that I already had the structure in place to test things this way. There are possibly other cases where something like this might make more sense than using Test::MockObject in the first place although I am somewhat skeptical.

PostgreSQL UTF-8 Conversion

It's becoming increasingly common for me to be involved in conversion of an old version of PostgreSQL to a new one, and at the same time, from an old "SQL_ASCII" encoding (that is, undeclared, unvalidated byte soup) to UTF-8.

Common ways to do this are to run pg_dumpall and then pipe the output through iconv or recode. When your source encoding is all pure ASCII, you don't need to do even that. When it's really all Windows-1252 (a superset of Latin-1 aka ISO-8859-1) it's easy.

But often, the data is stored in various unknown encodings from several sources over the course of years, including some that's already in UTF-8. When you convert with iconv, it dies with an error at the first problem, whereas recode will let you ignore encoding problems, but that leaves you with junk in your output.

The case I'm often encountering is fairly easy, but not perfect: Lots of ASCII, some Windows-1252, and some UTF-8. Since both pure ASCII and UTF-8 can be mechanistically detected, I put together this script to do the detection. It's Perl and uses the nice IsUTF8 module to do its character encoding detection:

Pipe input to the script. It handles one line at a time. When run with any arguments (such as --test) it will swallow pure ASCII lines, write lines it thinks are valid UTF-8 to stderr, and will convert the remaining presumed Windows-1252 lines to stdout, for manual examination.

If its guesses look correct, run it again with no arguments, and it will write all 3 types of encoding to stdout, ready for input to psql in your new UTF-8 encoded database.

(Don't forget to munge your pg_dump file to remove any hardcoded declarations of "SQL_ASCII" encoding from CREATE DATABASE statements, or otherwise make sure your database actually is created with UTF-8 encoding!)

Splitting Postgres pg_dump into pre and post data files

I've just released a small Perl script that has helped me solve a specific problem with Postgres dump files. When you use pg_dump or pg_dumpall, it outputs things in the following order, per database:

1. schema creation commands (e.g. CREATE TABLE)
2. data loading command (e.g. COPY tablename FROM STDIN)
3. post-data schema commands (e.g. CREATE INDEX)

The problem is that using the --schema-only flag outputs the first and third sections into a single file. Hence, if you load the file and then load a separate --data-only dump, it can be very slow as all the constraints, indexes, and triggers are already in place. The split_postgres_dump script breaks the dump file into two segments, a "pre" and a "post". (It doesn't handle a file with a data section yet, only a --schema-only version)

Why would you need to do this instead of just using a full dump? Some reasons I've found include:

• When you need to load the data more than once, such as debugging a data load error.
• When you want to stop after the data load step (which you can't do with a full dump)
• When you need to make adjustments to the schema before the data is loaded (seen quite a bit on major version upgrades)

Usage is simply ./split_postgres_dump.pl yourdumpfile.pg, which will then create two new files, yourdumpfile.pg.pre and yourdumpfile.pg.post. It doesn't produce perfectly formatted files, but it gets the job done!

It's a small script, so it has no bug tracker, git repo, etc. but it does have a small wiki page at http://bucardo.org/wiki/Split_postgres_dump from which you can download the latest version.

Future versions of pg_dump will allow you to break things into pre and post data sections with flags, but until then, I hope somebody finds this script useful.

Update: There is now a git repo:
git clone git://bucardo.org/split_postgres_dump.git

Gathering server information with boxinfo

I've just publicly released another Postgres-related script, this one called "boxinfo". Basically, it gathers information about a box (server), hence the catchy and original name. It outputs the information it finds into an HTML page, or into a MediaWiki formatted page.

The goal of boxinfo is to have a simple, single script that quickly gathers important information about a server into a web page, so that you can get a quick overview of what is installed on the server and how things are configured. It's also useful as a reference page when you are trying to remember which server was it that had Bucardo version 4.5.0 installed and was running pgbouncer.

As we use MediaWiki internally here at End Point (running with a Postgres backend, naturally), the original (and default) format is HTML with some MediaWiki specific items inside of it.

Because it is meant to run on a wide a range of boxes as possible, it's written in Perl. While we've run into a few boxes over the years that did not have Perl installed, the number that had any other language you choose (except perhaps sh) is much greater. It requires no other Perl modules, and simply makes a lot of system calls.

Various information about the box is gathered. System wide things such as mount points, disk space, schedulers, packaging systems are gathered first, along with versions of many common Unix utilities. We also gather information on some programs where more than just the version number is important, such as puppet, heartbeat, and lifekeeper. Of course, we also go into a great amount of detail about all the installed Postgres clusters on the box as well.

The program tries its best to locate every active Postgres cluster on the box, and then gathers information about it, such as where pg_xlog is linked to, any contrib modules installed, any interesting configuration variables from postgresql.conf, the size of each database, and lots of detailed information about any Slony or Bucardo configurations it finds.

The main page for it is on the Bucardo wiki at http://bucardo.org/wiki/Boxinfo. That page details the various command line options and should be considered the canonical documentation for the script. The latest version of boxinfo can be downloaded from that page as well. For any enhancement requests or problems to report, please visit the bug tracker at http://bucardo.org/bugzilla/.

What exactly does the output look like? We've got an example on the wiki showing the sample output from a run against my laptop. Some of the items were removed, but it should give you an idea of what the script can do, particularly with regards to the Postgres information: http://bucardo.org/wiki/Boxinfo/Example

The script is still a little rough, so we welcome any patches, bug reports, requests, or comments. The development version can be obtained by running: git clone git://bucardo.org/boxinfo.git

Monitoring Postgres log files with tail_n_mail

We've just publically released a useful script named tail_n_mail that keeps an eye on your Postgres log files and mails interesting lines to one or more addresses. It's released under a BSD license and is available at:

http://bucardo.org/wiki/Tail_n_mail

Complete documentation is available at the above, but here's a quick overview. First, it figures out the current log file (it actually works for any file, but it's primarily aimed at Postgres log files). Then, it finds any lines that match based on the INCLUDE lines in the config file, and finally removes any that do not match the EXCLUDE lines in the config files. It summarizes the results and sends a report to one or more emails.

To use, just specify a a configuration file as the first argument. Typically, the script is run from cron, either for instant reports (e.g. FATAL or PANIC errors), or for daily reports (e.g. all interesting ERRORs in the last 24 hours).

Here's what a typical config file looks like. In this example, we'll look for any FATAL or PANIC notices from Postgres, while ignoring a few known errors that we don't care about.

 ## Config file for the tail_n_mail.pl program
 ## This file is automatically updated
 EMAIL: greg@endpoint.com, postgres@endpoint.com
 
 FILE: /var/log/pg_log/postgres-%Y-%m-%d.log
 INCLUDE: FATAL:  
 INCLUDE: PANIC:  
 EXCLUDE: database ".+" does not exist
 EXCLUDE: database "template0" is not currently accepting connections
 MAILSUBJECT: HOST Postgres fatal errors (FILE)

It should be setup to run often from cron:

  */5 * * * * perl bin/tail_n_mail.pl bin/tnm/tnm.fatals.config

The resulting mail message will look like this:

Matches from /var/log/pg_log/postgres-2010-01-01.log: 42
Date: Fri Jan  1 10:34:00 2010
Host: pollo

[1] Between lines 123005 and 147976, occurs 39 times.
First:  Jan  1 00:00:01 rojogrande postgres[4306]
Last:   Jan  1 10:30:00 rojogrande postgres[16854]
Statement:  user=root,db=rojogrande FATAL:  password authentication failed for user "root"

[2] Between lines 147999 and 148213, occurs 2 times.
First:  Jan  1 10:31:01 rojogrande postgres[3561]
Last:   Jan  1 10:31:10 rojogrande postgres[15312]
Statement: FATAL  main: write to worker pipe failed -(9) Bad file descriptor

[3] (from line 152341)
PANIC:  could not locate a valid checkpoint record

There may be false positives, but it's not designed to be a complete log parser. There are some other command line flags and options for the config file: see the documentation for the full list. This script has been watching over a number of production systems for a while now, but improvements, ideas, and patches are always welcome. It's tracked via git; you can clone it by running:

  git clone git://bucardo.org/tail_n_mail.git

Bugs and feature requests can be filed and tracked at:

http://bucardo.org/bugzilla/

ImageMagick EPS bug workaround

Sometimes software is buggy, and even with the malleability of open source software, upgrading to fix a problem may not be an immediate option due to lack of time, risks to production stability, or problems caused by other incompatible changes in a newer version of the software.

ImageMagick is a widely used open source library and set of programs for manipulating images in many ways. It's very useful and I'm grateful it exists and has become so powerful. However, many longtime ImageMagick users like me can attest that it has had a fair number of bugs, and upgrades sometimes don't go very smoothly as APIs change, or new bugs creep in.

Recently my co-worker, Jeff Boes, had the misfortune, or opportunity, of encountering just such a scenario. Our friends at CityPass have several site features that use ImageMagick for resizing, rotating, and otherwise manipulating or gathering data about images.

The environment specifics (skip if you're not troubleshooting an ImageMagick problem of your own!): RHEL 5 with its standard RPM of ImageMagick-6.2.8.0-4.el5_1.1.x86_64. The application server is Interchange, running on our local-perl-5.10.0 nonthreaded Perl build, using the local-ImageMagick-perl-6.2.8.0-4.1 library. Those custom builds are available in the packages.endpoint.com endpoint Yum repository.

CityPass reported problems with some EPS (Encapsulated PostScript) images failing to process correctly by ImageMagick. In fact, the bug prevented any subsequent image processing jobs from completing in the same OS process. Upgrading ImageMagick would fix the bug, but we can't currently do that on the production server due to other compatibility problems.

After some trial and error, Jeff determined that the ImageMagick bug only kicks in when the first image processed is an EPS file. If it's any other image type, it works fine. This explained why code that had been unchanged in a year or so suddenly stopped working: Before now, no EPS file had happened to come first.

At first Jeff hacked the system to process the non-EPS files first, then sorted the results as originally desired. Then we realized there may be some rare scenarios where no non-EPS files at all were in the batch, which would trigger the bug. Jeff then had ImageMagick always first process a trivial small JPEG file which was known to work.

That worked, but Jeff then came across the idea of processing an empty image file so we didn't have a dependency on an image that might later be deleted. He tinkered a bit and came up with something suprising but even better. This is his Perl code:

my $first_im = Image::Magick->new;
$first_im->read('');
# (then process all images in any order as originally intended)

I wouldn't have expected an initial read of an empty string filename to solve the problem, but it did. Accompanied by a suitable comment noting the history of the kludge for future software archaeologists, closed the case.

Software's funny, but it's nice when there's a simple -- if counterintuitive -- solution to work around a bug. And I think Jeff has mostly recovered his sanity in the meantime!

Migrating Postgres with Bucardo 4

Bucardo just released a major version (4). The latest version, 4.0.3, can be found at the Bucardo website. The complete list of changes is available on the new Bucardo wiki.

One of the neat tricks you can do with Bucardo is an in-place upgrade of Postgres. While it still requires application downtime, you can minimize your downtime to a very, very small window by using Bucardo. We'll work through an example below, but for the impatient, the basic process is this:

1. Install Bucardo and add large tables to a pushdelta sync
2. Copy the tables to the new server (e.g. with pg_dump)
3. Start up Bucardo and catch things up (e.g. copy all rows changes since step 2)
4. Stop your application from writing to the original database
5. Do a final Bucardo sync, and copy over non-replicated tables
6. Point the application to the new server

With this, you can migrate very large databases from one server to another (or from Postgres 8.2 to 8.4, for example) with a downtime measured in minutes, not hours or days. This is possible because Bucardo supports replicating a "pre-warmed" database - one in which most of the data is already there.

Let's test out this process, using the handy pgbench utility to create a database. We'll go from PostgreSQL 8.2 (the original database, called "A") to PostgreSQL 8.4 (the new database, called "B"). The first step is to create and populate database A:

  initdb -D testA
  echo port=5555 >> testA/postgresql.conf
  pg_ctl -D testA -l a.log start
  createdb -p 5555 alpha
  pgbench -p 5555 -i alpha
  psql -p 5555 -c 'create user bucardo superuser'

At this point, we have four tables:

  $ psql -p 5555 -d alpha -c '\d+'
                          List of relations
   Schema |   Name   | Type  |  Owner   |    Size    | Description
  --------+----------+-------+----------+------------+-------------
   public | accounts | table | postgres | 13 MB      |
   public | branches | table | postgres | 8192 bytes |
   public | history  | table | postgres | 0 bytes    |
   public | tellers  | table | postgres | 8192 bytes |

For the purposes of this example, let's make believe that accounts table is actually 13 TB. :) The next step is to prepare the 8.4 database:

  initdb -D testB
  echo port=5566 >> testB/postgresql.conf
  pg_ctl -D testB -l b.log start

We'll copy everything except the data itself to the new server:

  pg_dumpall --schema-only -p 5555 | psql -p 5566 -f -

Because the other tables are very small, we're only going to use Bucardo to copy over the large "accounts" table. So let's install Bucardo and add a sync to do just that:

  sudo yum install perl-DBIx-Safe
  tar xvf Bucardo-4.0.3.tar.gz
  cd Bucardo-4.0.3
  perl Makefile.PL
  sudo make install

(That's a very quick overview - see the Installation page for more information.)

Let's install bucardo on the new database:

  mkdir /tmp/bctest
  bucardo_ctl install --dbport=5566 --piddir=/tmp/bctest

Set the port so we don't have to keep typing it in:

  echo dbport=5566 > .bucardorc

Now teach Bucardo about both databases:

  bucardo_ctl add db alpha name=oldalpha port=5555
  bucardo_ctl add db alpha name=newalpha port=5566

Finally, create a sync to copy from old to new:

  bucardo_ctl add sync pepper type=pushdelta source=oldalpha targetdb=newalpha tables=accounts ping=false

This adds a new sync named "pepper" which is of type pushdelta (master-slave: copy changes from the source table to the target(s).). The source is our old server, named "oldalpha" by Bucardo. The target database is our new server, named "newalpha". The only table in this sync is "accounts", and we set ping as false, which means that we do NOT create a trigger on this table to signal Bucardo that a change has been made, as we will be kicking the sync manually.

At this point, the accounts table has a trigger on it that is capturing which rows have been changed. The next step is to copy the existing table from the old database to the new database. There are many ways to do this, such as a NetApp snapshot, using ZFS, etc., but we'll use the traditional way of a slow but effective pg_dump:

  pg_dump alpha -p 5555 --data-only -t accounts | psql -p 5566 -d alpha -f -

This can take as long as it needs to. Reads and writes can still happen against the old server, and changes can be made to the accounts tables. Once that is done, here's the situation:

• The old server is still in production
• The new server has a full but outdated copy of 'accounts'
• The new server has empty tables for everything but 'accounts'
• All changes to the accounts table on the old server are being logged.

Our next step is to start up Bucardo, and let it "catch up" the new server with all changes that have occurred since we created the sync:

  bucardo_ctl start

You can keep track of how far along the sync is by tailing the log file (syslog and ./log.bucardo by default) or by checking on the sync itself:

  bucardo_ctl status pepper

Once it has caught up (how long depends on how busy the accounts table is, of course), the only disparity should be any rows that have changed since the sync last ran. You can kick off the sync again if you want:

  bucardo_ctl kick pepper 0

The final 0 there will allow you to see when the sync has finished.

For the final step, we'll need to move the remainder of the data over. This begins our production downtime window. First, stop the app from writing to the database (reading is okay). Next, once you've confirmed nothing is making changes to the database, make a final kick:

  bucardo_ctl kick pepper 0

Next, copy over the other data that was not replicated by Bucardo. This should be small tables that will copy quickly. In our case, we can do it like this:

  pg_dump alpha -p 5555 --data-only -T accounts -N bucardo | psql -p 5566 -d alpha -f -

Note that we excluded the schema bucardo, and copied all tables *except* the 'accounts' one.

That's it! You can now point your application to the new server. There are no Bucardo triggers or other artifacts on the new server to clean up. At this point, you can shutdown Bucardo itself:

  bucardo_ctl stop

Then shutdown your old Postgres and start enjoying your new 8.4 server!

File test comparison table for shell, Perl, Ruby, and Python

A few days ago, my co-worker Richard asked how in Python you would do the -x Bourne shell and Perl file test that checks whether a file is executable. This is (for me, at least) a really commonly used function but one I hadn't needed to do yet in Python, so I looked it up.

That wasn't so hard to find, but then I wondered about the other shell and Perl file tests that I use all the time. Finding equivalents for those was harder than I expected. A web search didn't turn much up aside from language holy wars and limited answers, but I didn't find any exhaustive list.

So I made my own. Below is a table comparing file test operators in the original Bourne shell-compatibles bash, ksh, and zsh; Perl's expanded set; Ruby's which was derived first from Perl; and equivalent Python code.

There are still some blanks where I didn't find a good equivalent. Of course I'm sure it's possible with enough custom logic to achieve the same end, but I have tried to stick with relatively simple formulations using built-in functions for now. I'll be happy to fill in the blanks if any readers make suggestions.

Performance notes on avoiding multiple stats of the same file:

• Starting with Perl 5.9.1, file tests can be "stacked" and will use a single stat for all tests, e.g. -f -x file. In older versions of Perl you can do -f file && -x _ instead.
• Ruby's File::Stat class can be used to cache a stat for multiple tests.
• Python's os.stat(file).st_mode can be stored and used for multiple tests.

Unless otherwise specified, these tests follow symbolic links and operate on the target of the link, rather than the link itself.

All tests return boolean true or false unless otherwise noted.

Complete details are in the manuals for each language:

• bash: `man bash` and search for "CONDITIONAL EXPRESSIONS"
• ksh: `man pdksh` and search for "test expression"
• zsh: `man zshmisc` and search for "CONDITIONAL EXPRESSIONS"
• Perl: `perldoc -f -f`, filetest pragma
• Ruby: File class (summary version) and test() built-in, IO class docs
• Python: os.stat, stat() results, os.path

Perl's Scalar::Util::dualvar

I just came across this fun Perl function that I can't think of a (good) use for, but have to share.

In the Scalar::Util module is the function dualvar:

    $foo = dualvar 10, "Hello";
    $num = $foo + 2;                    # 12
    $str = $foo . " world";             # Hello world

Using that in the right place could lead a future programmer down some fun debugging paths!

Text sequences

Somebody recently asked on the Postgres mailing list about "Generating random unique alphanumeric IDs". While there were some interesting solutions given, from a simple Pl/pgsql function to using mathematical transformations, I'd like to lay out a simple and powerful solution using Pl/PerlU

First, to paraphrase the original request, the poster needed a table to have a text column be its primary key, and to have a five-character alphanumeric string used as that key. Let's knock out a quick function using Pl/PerlU that solves the generation part of the question:

DROP FUNCTION IF EXISTS nextvalalpha(TEXT);
CREATE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $_$
  use strict;
  my $numchars = 5;
  my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
  my $value = join '' => @chars[map{rand @chars}(1..$numchars)];
  return $value;
$_$;

Pretty simple: it simply pulls a number of random characters from a string (with some commonly confused letters and number removed) and returns a string:

greg=# SELECT nextvalalpha('foo');
 nextvalalpha
--------------
 MChNf
(1 row)

greg=# SELECT nextvalalpha('foo');
 nextvalalpha
--------------
 q4jHm
(1 row)

So let's set up our test table. Since Postgres can use many things column DEFAULTS, including user-defined functions, this is pretty straightforward:

DROP TABLE IF EXISTS seq_test;
CREATE TABLE seq_test (
  id    VARCHAR(5) NOT NULL DEFAULT nextvalalpha('foo'),
  city  TEXT,
  state TEXT
);

A quick test shows that the id column is auto-propagated with some random values:

greg=# PREPARE abc(TEXT,TEXT) AS INSERT INTO seq_test(city,state) 
greg-# VALUES($1,$2) RETURNING id;

greg=# EXECUTE abc('King of Prussia', 'Pennsylvania');
  id
-------
 9zbsd
(1 row)

INSERT 0 1

greg=# EXECUTE abc('Buzzards Bay', 'Massachusetts');
  id
-------
 4jJ5D
(1 row)

INSERT 0 1

So far so good. But while those returned values are random, they are not in any way unique, which a primary key needs to be. First, let's create a helper table to keep track of which values we've already seen. We'll also track the 'name' of the sequence as well, to allow for more than one unique set of sequences at a time:

DROP TABLE IF EXISTS alpha_sequence;
CREATE TABLE alpha_sequence (
  sname TEXT,
  value TEXT
);
CREATE UNIQUE INDEX alpha_sequence_unique_value ON alpha_sequence(sname,value);

Now we tweak the original function to use this new table.

CREATE OR REPLACE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
  use strict;
  my $sname = shift;
  my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
  my $numchars = 5;
  my $toomanyloops = 10000; ## Completely arbitrary pick
  my $loops = 0;

  my $SQL = 'SELECT 1 FROM alpha_sequence WHERE sname = $1 AND value = $2';
  my $sth = spi_prepare($SQL, 'text', 'text');

  my $value = '';
  SEARCHING:
  {
    ## Safety valve
    if ($loops++ >= $toomanyloops) {
      die "Could not find a unique value, even after $toomanyloops tries!\n";
    }
    ## Build a new value, then test it out
    $value = join '' => @chars[map{rand @chars}(1..$numchars)];
    my $count = spi_exec_prepared($sth,$sname,$value)->{processed};
    redo if $count >= 1;
  } 

  ## Store it and commit the change
  $SQL = 'INSERT INTO alpha_sequence VALUES ($1,$2)';
  $sth = spi_prepare($SQL, 'text', 'text');
  spi_exec_prepared($sth,$sname,$value);
  return $value;
$_$;

Alright, that seems to work well, and prevents duplicate values. Or does it? Recall that one of the properties of sequences in Postgres is that they live outside of the normal MVCC rules. In other words, once you get a number via a call to nextval(), nobody else can get that number again (even you!) - regardless of whether you commit or rollback. Thus, sequences are guaranteed unique across all transactions and sessions, even if used for more than one table, called manually, etc. Can we do the same with our text sequence? Yes!

For this trick, we'll need to ensure that we only return a new value if we are 100% sure it is unique. We also need to record the value returned, even if the transaction that calls it rolls back. In other words, we need to make a small 'subtransaction' that commits, regardless of the rest of the transaction. Here's the solution:

CREATE OR REPLACE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
  use strict;
  use DBI;
  my $sname = shift;
  my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
  my $numchars = 5;
  my $toomanyloops = 10000;
  my $loops = 0;

  ## Connect to this very database, but with a new session
  my $port = spi_exec_query('SHOW port')->{rows}[0]{port};
  my $dbname = spi_exec_query('SELECT current_database()')->{rows}[0]{current_database};
  my $dbuser = spi_exec_query('SELECT current_user')->{rows}[0]{current_user};
  my $dsn = "dbi:Pg:dbname=$dbname;port=$port";
  my $dbh = DBI->connect($dsn, $dbuser, '', {AutoCommit=>1,RaiseError=>1,PrintError=>0});

  my $SQL = 'SELECT 1 FROM alpha_sequence WHERE sname = ? AND value = ?';
  my $sth = $dbh->prepare($SQL);

  my $value = '';
  SEARCHING:
  {
    ## Safety valve
    if ($loops++ >= $toomanyloops) {
      die "Could not find a unique value, even after $toomanyloops tries!\n";
    }
    ## Build a new value, then test it out
    $value = join '' => @chars[map{rand @chars}(1..$numchars)];
    my $count = $sth->execute($sname,$value);
    $sth->finish();
    redo if $count >= 1;
  } 

  ## Store it and commit the change
  $SQL = 'INSERT INTO alpha_sequence VALUES (?,?)';
  $sth = $dbh->prepare($SQL);
  $sth->execute($sname,$value); ## Does a commit

  ## Only now do we return the value to the caller
  return $value;
$_$;

What's the big difference between this one and the previous version? Rather than examine the alpha_sequence table in our /current/ session, we figure out who and where we are, and make a completely separate connection to the same database using DBI. Then we find an unused value, INSERT that value into the alpha_sequence table, and commit that outside of our current transaction.Only then can we return the value to the caller.

Postgres sequences also have a currval() function, which returns the last value returned via a nextval() in the current session. The lastval() function is similar, but it returns the last call to nextval(), regardless of the name used. We can make a version of these easy enough, because Pl/Perl functions have a built-in shared hash named '%_SHARED'. Thus, we'll add two new lines to the end of the function above:

...
  $sth->execute($sname,$value); ## Does a commit
  $_SHARED{nva_currval}{$sname} = $value;
  $_SHARED{nva_lastval} = $value;
...

Then we create a simple function to display that value, as well as throw an error if called too early - just like nextval() does:

DROP FUNCTION IF EXISTS currvalalpha(TEXT)
CREATE FUNCTION currvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
  my $sname = shift;
  if (exists $_SHARED{nva_currval}{$sname}) {
    return $_SHARED{nva_currval}{$sname};
  }
  else {
    die qq{currval of text sequence "$sname" is not yet defined in this session\n};
  }
$_$;

Now the lastval() version:

DROP FUNCTION IF EXISTS lastvalalpha();
CREATE FUNCTION lastvalalpha()
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
  if (exists $_SHARED{nva_lastval}) {
    return $_SHARED{nva_lastval};
  }
  else {
    die qq{lastval (text) is not yet defined in this session\n};
  }
$_$;

For the next tests, we'll create a normal (integer) sequence, and see how it acts compared to our newly created text sequence:

DROP SEQUENCE IF EXISTS newint;
CREATE SEQUENCE newint STARTS WITH 42;

greg=# SELECT lastval();
ERROR: lastval is not yet defined in this session

greg=# SELECT currval('newint');
ERROR:  currval of sequence "newint" is not yet defined in this session

greg=# SELECT nextval('newint');
 nextval
---------
      42
(1 row)

greg=# SELECT currval('newint');
 currval
---------
      42

greg=# SELECT lastval();
 lastval
---------
      42

greg=# SELECT lastvalalpha();
ERROR: error from Perl function "lastvalalpha": lastval (text) is not yet defined in this session

greg=# SELECT currvalalpha('newtext');
ERROR:  error from Perl function "currvalalpha": currval of text sequence "newtext" is not yet defined in this session

greg=# SELECT nextvalalpha('newtext');
 nextvalalpha
--------------
 rRwJ6

greg=# SELECT currvalalpha('newtext');
 currvalalpha
--------------
 rRwJ6

greg=# SELECT lastvalalpha();
 lastvalalpha
--------------
 rRwJ6

There is one more quick optimization we could make. Since the %_SHARED hash is available across our session, there is no need to do anything in the function more than once if we can cache it away. In this case, we'll cache away the server information we look up, the database handle, and the prepares. Our final function looks like this:

CREATE OR REPLACE FUNCTION nextvalalpha(TEXT)
RETURNS TEXT
SECURITY DEFINER
LANGUAGE plperlu
AS $_$
  use strict;
  use DBI;
  my $sname = shift;
  my @chars = split // => qw/abcdefghijkmnpqrstwxyzABCDEFGHJKLMNPQRSTWXYZ23456789/;
  my $numchars = 5;
  my $toomanyloops = 10000;
  my $loops = 0;

  ## Connect to this very database, but with a new session
  if (! exists $_SHARED{nva_dbi}) {
    my $port = spi_exec_query('SHOW port')->{rows}[0]{port};
      my $dbname = spi_exec_query('SELECT current_database()')->{rows}[0]{current_database};
    my $dbuser = spi_exec_query('SELECT current_user')->{rows}[0]{current_user};
    my $dsn = "dbi:Pg:dbname=$dbname;port=$port";
    $_SHARED{nva_dbi} = DBI->connect($dsn, $dbuser, '', {AutoCommit=>1,RaiseError=>1,PrintError=>0});
    my $dbh = $_SHARED{nva_dbi};
    my $SQL = 'SELECT 1 FROM alpha_sequence WHERE sname = ? AND value = ?';
    $_SHARED{nva_sth_check} = $dbh->prepare($SQL);
    $SQL = 'INSERT INTO alpha_sequence VALUES (?,?)';
    $_SHARED{nva_sth_add} = $dbh->prepare($SQL);
  }


  my $value = '';
  SEARCHING:
  {
    ## Safety valve
    if ($loops++ >= $toomanyloops) {
      die "Could not find a unique value, even after $toomanyloops tries!\n";
    }
    ## Build a new value, then test it out
    $value = join '' => @chars[map{rand @chars}(1..$numchars)];
    my $count = $_SHARED{nva_sth_check}->execute($sname,$value);
    $_SHARED{nva_sth_check}->finish();
    redo if $count >= 1;
  } 

  ## Store it and commit the change
  $_SHARED{nva_sth_add}->execute($sname,$value); ## Does a commit
  $_SHARED{nva_currval}{$sname} = $value;
  $_SHARED{nva_lastval} = $value;
  return $value;
$_$;

Having the ability to reach outside the database in Pl/PerlU - even if simply to go back in again! - can be a powerful tool, and allows us to do things that might otherwise seem impossible.

Debugging prepared statements

I was recently tasked with the all-too-familiar task for DBAs of "why is this script running so slow?". After figuring out exactly which script and where it was running from, I narrowed down the large number of SQL commands it was issuing to one particularly slow one, that looked something like this in the pg_stat_activity view:

current_query 
-------------
SELECT DISTINCT id
FROM containers
WHERE code LIKE $1

Although the query ran too quick to really measure a finite time just by watching pg_stat_activity, it did show up quite often. So it was likely slow *and* being called many times in a loop somewhere. The use of 'LIKE' always throws a yellow flag, so those factors encouraged me look closer into the query.

While the table in question did have an index on the 'code' column, it was not being used. This is because LIKE (on non-C locale databases) cannot work against normal indexes - it needs a simpler character by character index. In Postgres, you can achieve this by using some of the built in operator classes when creating an index. More details can be found at the documentation on operator classes. What I ended up doing was using text_pattern_ops:

SET maintenance_work_mem = '2GB';

CREATE INDEX CONCURRENTLY containers_code_textops
  ON containers (code text_pattern_ops);

Since this was on a production system (yes, I tested on a QA box first!), the CONCURRENTLY phrase ensured that the index did not block any reads or writes on the table while the index was being built. Details on this awesome option can be found in the docs on CREATE INDEX.

After the index was created, the following test query went from 800ms to 0.134ms!:

EXPLAIN ANALYZE SELECT * FROM containers WHERE code LIKE 'foobar%';

I then created a copy of the original script, stripped out any parts that made changes to the database, added a rollback to the end of it, and tested the speed. Still slow! Recall that the original query looked like this:

SELECT DISTINCT id
FROM containers
WHERE code LIKE $1

The $1 indicates that this is a prepared query. This leads us to the most important lesson of this post: whenever you see that a prepared statement is being used, it's not enough to test with a normal EXPLAIN or EXPLAIN ANALYZE. You must emulate what the script (e.g. the database driver) is really doing. So from psql, I did the following:

PREPARE foobar(text) AS SELECT DISTINCT id FROM containers WHERE code LIKE $1;
EXPLAIN ANALYZE EXECUTE('foobar%');

Bingo! This time, the new index was *not* being used. This is the great trade-off of prepared statements - while it allows you to prepare and rewrite the query only once, the planner cannot anticipate what you might pass in as a possible argument, so it makes the best generic plan possible. Thus, your EXPLAIN of the same query using literals or placeholders via PREPARE may look very different.

While it's possible to make workarounds at the database level for the problem of prepared statements using the "wrong" plan, in this case it was simply easier to tell the existing script not to use prepared statements at all for this one query. As the script was using DBD::Pg, the solution was to simply use the pg_server_prepare attribute like so:

$dbh->{pg_server_prepare} = 0;
my $sth = $dbh->prepare('SELECT DISTINCT id FROM containers WHERE code LIKE ?');
$dbh->{pg_server_prepare} = 1;

The effect of this inside of DBD::Pg is that instead of using PQprepare and then PQexecPrepared for each call to $sth->execute(), DBD::Pg will, for every call to $sth->execute(), quote the parameter itself, build a string containing the original SQL statement and the quoted literal, and send it to the backend via PQexec. Normally not something you want to do, but the slight overhead of doing it that way was completely overshadowed by the speedup of using the new index.

The final result: the script that used to take over 6 hours to run now only takes about 9 minutes to complete. Not only are the people using the script much happier, but it means less load on the database.

Perl+Postgres: changes in DBD::Pg 2.15.1

DBD::Pg, the Perl interface to Postgres, recently released version 2.15.1. The last two weeks has seen a quick flurry of releases: 2.14.0, 2.14.1, 2.15.0, and 2.15.1. Per the usual versioning convention, the numbers on the far right (in this case the "dot one" releases) were simply bug fixes, while 2.14.0 and 2.15.0 introduced API and/or major internal changes. Some of these changes are explained below.

From the Changes file for 2.15.0:

CHANGE:
 - Allow execute_array and bind_param_array to take oddly numbered items, 
   such that DBI will make missing entries undef/null (CPAN bug #39829) [GSM]

The Perl Database Interface (DBI) has a neat feature to allow you to execute many sets of items at one time, known as execute_array. The basic format is to pass in an list of arrays, in which each array contains the placeholders needed to execute the query. For example:

## Create a simple test table with two columns
$dbh->do('DROP TABLE IF EXISTS people');
$dbh->do('CREATE TABLE people (id int, fname text)');

## Pass in all ids as a single array
my @numbers = (1,2,3);

## Pass in all names as a single array
my @names = ("Garrett", "Viktoria", "Basso");

## Prepare the statement
my $sth = $dbh->prepare('INSERT INTO people VALUES (?, ?)');

## Execute the statement multiple times (three times in this case)
$sth->execute_array(undef, \@numbers, \@names);
## (the first argument is an optional argument hash which we don't use here)

## Pull back and display the rows from our new table
$SQL = 'SELECT id, fname FROM people ORDER BY fname';
for my $row (@{$dbh->selectall_arrayref($SQL)}) {
    print "Found: $row->[0] : $row->[1]\n";
}

$ perl testscript.pl
Found: 3 : Basso
Found: 1 : Garrett
Found: 2 : Viktoria

In 2.15.0, we loosened the requirement that the number of placeholders in each array match up with the expected number. Per the DBI spec, any "missing" items are considered undef, which maps to a SQL NULL. Thus:

$dbh->do('DROP TABLE IF EXISTS people');
$dbh->do('CREATE TABLE people (id int, fname text)');

## Note that this time there are only two ids given, not three:
my @numbers = (1,2);
my @names = ("Garrett", "Viktoria", "Basso");
my $sth = $dbh->prepare("INSERT INTO people VALUES (?, ?)");

$sth->execute_array(undef, \@numbers, \@names);

## Show a question mark for any null ids
$SQL = q{
SELECT CASE WHEN id IS NULL THEN '?' ELSE id::text END, fname 
FROM people ORDER BY fname
};
for my $row (@{$dbh->selectall_arrayref($SQL)}) {
    print "Found: $row->[0] : $row->[1]\n";
}

$ perl testscript2.pl
Found: ? : Basso
Found: 1 : Garrett
Found: 2 : Viktoria

Also note that bind_param_array is an alternate way to add the list of arrays before the execute is called. This is similar in concept to a regular execute: if you bind the values first, you can call execute without any arguments:

...
$sth->bind_param_array(1, \@numbers);
$sth->bind_param_array(2, \@names);
$sth->execute_array(undef);
...

CHANGE:
 - Use PQexecPrepared even when no placeholders (CPAN bug #48155) [GSM]

Sending queries to Postgres via DBD::Pg usually involves two steps: prepare and execute. The prepare is done one time, while the execute can be called many times, often times with different arguments. Previously, DBD::Pg would call PQexec for queries that had no placeholders. However, the ability to handle placeholders smoothly is only one advantage of using server-side prepares in Postgres. The other advantage is that Postgres only has to parse the query a single time, in the initial prepare. In 2.15.0, we use PQexecPrepared for all queries, whether they have placeholders or not. The upshot of this is that multiple calls to the execute() function will be a little bit faster, and that we only use PQexec when we really have to.

CHANGE:
 - Fix quoting of booleans to respect more Perlish variants (CPAN bug #41565) [GSM]

In previous versions, the mapping of Perl vars to booleans was very simple, and did only simple 0/1 mapping. However, Perl's values of "truth" is richer than that. We can now do things like this:

for my $name ('0', '1', '0E0', '0 but true', 'F', 'T', 'TRUE', 'false') {
  printf qq{Value '%s' is %s\n}, $name, $dbh->quote($name, {pg_type => PG_BOOL});
}

$ perl testscript3.pl
Value '0' is FALSE
Value '1' is TRUE
Value '0E0' is TRUE
Value '0 but true' is TRUE
Value 'F' is FALSE
Value 'T' is TRUE
Value 'TRUE' is TRUE
Value 'false' is FALSE

CHANGE:
  - Return ints and bools-cast-to-number from the db as true Perlish numbers.
    (CPAN bug #47619) [GSM]

This one is a little more subtle. When a value is returned from the database, it gets mapped back to a string. So even if the value in the database came from an INTEGER column, by the time it made it's way back to your Perl script it was a string that happened to hold an integer value. DBD::Pg now attempts to cast some types to their Perl equivalent. This is normally hard to see without peering inside Perl internals, but using Data::Dumper can show you the difference:

## Ask Postgres to return a string and an integer
$SQL = 'SELECT 123::text, 123::integer';
$info = $dbh->selectall_arrayref($SQL)->[0];
print Dumper $info;

## Older versions of DBD::Pg give:
$VAR1 = [
          '123',
          '123'
        ];

## The new and improved version gives:
$VAR1 = [
          '123',
          123
        ];

A small difference, but not unimportant - this change came about through a bug request, as it was causing problems when DBD::Pg was interacting with JSON::XS. Special thanks to Tim Bunce, (author of DBI, maintainer of the amazing NYTProf, and all around Perl guru) who found an important bug regarding this solution in 2.14.0, which led to the quick release of 2.14.1. Lesson learned: don't try converting ints to floats via sv_setnv.

Most of the other changes to 2.14 and 2.15 are bug fixes of one sort or another. To keep up on the changes or to talk about the project more, please join the mailing list

Comparing databases with check_postgres

One of the more recent additions to check_postgres, the all-singing, all-dancing Postgres monitoring tool, is the "same_schema" action. This was necessitated by clients who wanted to make sure that their schemas were identical across different servers. The two use cases I've seen are servers that are being replicated by Bucardo or Slony, and servers that are doing horizontal sharding (e.g. same schema and database on different servers: which server you go to depends on (for example) your customer id). Oft times a new index fails to make it to one of the slaves, or some function is tweaked on one server by a developer, who then forgets to change it back or propagate it. This program allows a quick and automatable check for such problems.

The idea behind the same_schema check is simple: we walk the schema and check for any differences, then throw a warning if any are found. In this case, we're using the term "schema" in the classic sense of a description of your database objects. Thus, one of the things we check is that all the schemas (in the classic RDBMS sense of a container of other database objects) are the same, when running the "same_schema" check. Only slightly confusing. :)

Not only is this program nice for monitoring (e.g. as a Nagios check), but if you pass in a --verbose argument, you get a simple not-all-on-one-line breakdown of all the differences between the two databases. Let's do a quick example.

First, we download and install check_postgres. We'll pull straight from a git repository for check_postgres. While we have our own repo at bucardo.org, we also are keeping it in sync with a tree at github.org, so we'll use that one:

git clone git://github.com/bucardo/check_postgres.git
cd check_postgres
perl Makefile.PL
make
make test
sudo make install

Let's create a Postgres cluster with the initdb command, start it up, then create two new databases to compare to each other.

initdb -D cptest
echo port=5555 >> cptest/postgresql.conf
pg_ctl -D cptest -l cp.log start
psql -p 5555 -c 'CREATE DATABASE yin'
psql -p 5555 -c 'CREATE DATABASE yang'

We're ready to run the script. By default, it outputs things in a Nagios-friendly manner. We should see an 'OK' because the two databases are identical:

./check_postgres.pl --action=same_schema --dbport=5555 --dbname=yin --dbport2=5555 --dbname2=yang

POSTGRES_SAME_SCHEMA OK: DB "yin" (port=5555 => 5555) Both databases have identical items | time=0.01

The message could be clearer and show both database names, but the check worked and showed that things are exactly the same. Let's throw some differences in and run it again:

psql -p 5555 -d yin -c 'create table foobar(a int primary key, b text, c text)'
psql -p 5555 -d yang -c 'create table foobar(a int, b text, c varchar(99))'
psql -p 5555 -d yin -c 'create schema yinonly'
psql -p 5555 -d yang -c 'create table pineapple(id int)'

./check_postgres.pl --action=same_schema --dbport=5555 --dbname=yin --dbport2=5555 --dbname2=yang

POSTGRES_SAME_SCHEMA CRITICAL: DB "yin" (port=5555 => 5555) Databases were different. Items not matched: 5 | time=0.01
Schema in 1 but not 2: yinonly  Table in 2 but not 1: public.pineapple  Column "a" of "public.foobar": nullable is NO on 1, but YES on 2.  Column "c" of "public.foobar": type is text on 1, but character varying on 2.  Table "public.foobar" on 1 has constraint "public.foobar_pkey", but 2 does not. 

It works, but a little messy for human consumption. Nagios requires everything to be in a single line, but we'll add a --verbose argument to ask the script for prettier formatting:

./check_postgres.pl --action=same_schema --dbport=5555 --dbname=yin --dbport2=5555 --dbname2=yang

POSTGRES_SAME_SCHEMA CRITICAL: DB "yin" (port=5555 => 5555) Databases were different. Items not matched: 5 | time=0.01
Schema in 1 but not 2: yinonly
Table in 2 but not 1: public.pineapple
Column "a" of "public.foobar": nullable is NO on 1, but YES on 2.
Column "c" of "public.foobar": type is text on 1, but character varying on 2.
Table "public.foobar" on 1 has constraint "public.foobar_pkey", but 2 does not.

There are also ways to filter the output, for times when you have known differences. For example, to exclude any tables with the word 'bucardo' in them, you could add this argument:

--warning="notable=bucardo"

The online documentation has more details about all the filtering options.

So what kind of things do we check for? Right now, we are checking:

• users (existence and powers, i.e. createdb, superuser)
• schemas
• tables
• sequences
• views
• triggers
• constraints
• columns
• functions (including volatility, strictness, etc.)

Got something else we aren't covering? Send in a patch, or a quick request, to the mailing list.

Bucardo and truncate triggers

Version 8.4 of Postgres was recently released. One of the features that hasn't gotten a lot of press, but which I'm excited about, is truncate triggers. This fixes a critical hole in trigger-based PostgreSQL replication systems, and support for these new triggers is now working in the Bucardo replication program.

Truncate triggers were added to Postgres by Simon Riggs (thanks Simon!), and unlike other types of triggers (UPDATE, DELETE, and INSERT), they are statement-level only, as truncate is not a row-level action.

Here's a quick demo showing off the new triggers. This is using the development version of Bucardo - a major new version is expected to be released in the next week or two that will include truncate trigger support and many other things. If you want to try this out for yourself, just run:

$ git clone git-clone http://bucardo.org/bucardo.git/

Bucardo does three types of replication; for this example, we'll be using the 'pushdelta' method, which is your basic "master to slaves" relationship. In addition to the master database (which we'll name A) and the slave database (which we'll name B), we'll create a third database for Bucardo itself.

$ initdb -D bcdata
$ initdb -D testA 
$ initdb -D testB 

(Technically, we are creating three new database clusters, and since we are doing this as the postgres user, the default database for all three will be 'postgres')

Let's give them all unique port numbers:

$ echo port=5400 >> bcdata/postgresql.conf
$ echo port=5401 >> testA/postgresql.conf 
$ echo port=5402 >> testB/postgresql.conf 

Now start them all up:

$ pg_ctl start -D bcdata -l bc.log
$ pg_ctl start -D testA -l A.log
$ pg_ctl start -D testB -l B.log

We'll create a simple test table on both sides:

$ psql -d postgres -p 5401 -c 'CREATE TABLE trtest(id int primary key)'
$ psql -d postgres -p 5402 -c 'CREATE TABLE trtest(id int primary key)'

Before we go any further, let's install Bucardo itself. Bucardo is a Perl daemon that uses a central database to store its configuration information. The first step is to create the Bucardo schema. This, like almost everything else with Bucardo, is done with the 'bucardo_ctl' script. The install process is interactive:

$ bucardo_ctl install --dbport=5400

This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser

We will create a new superuser named 'bucardo',
and make it the owner of a new database named 'bucardo'

Current connection settings:
1. Host:          
2. Port:          5400
3. User:          postgres
4. PID directory: /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: P

Version is: 8.4
Attempting to create and populate the bucardo database and schema
Database creation is complete

Connecting to database 'bucardo' as user 'bucardo'
Updated configuration setting "piddir"
Installation is now complete.

If you see any unexpected errors above, please report them to bucardo-general@bucardo.org

You should probably check over the configuration variables next, by running:
bucardo_ctl show all
Change any setting by using: bucardo_ctl set foo=bar

Because we don't want to tell the bucardo_ctl program our custom port each time we call it, we'll store that info into the ~/.bucardorc file:

$ echo dbport=5400 > ~/.bucardorc

Let's double check that everything went okay by checking the list of databases that Bucardo knows about:

$ bucardo_ctl list db
There are no entries in the 'db' table.

Time to teach Bucardo about our two new databases. The format for the add commands is: bucardo_ctl add [type of thing] [name of thing within the database] [arguments of foo=bar format]

$ bucardo_ctl add database postgres name=master port=5401
Database added: master

$ bucardo_ctl add database postgres name=slave1 port=5402
Database added: slave1

Before we go any further, let's look at our databases:

$ bucardo_ctl list dbs
Database: master   Status: active
Conn: psql -h  -p 5401 -U bucardo -d postgres

Database: slave1   Status: active
Conn: psql -h  -p 5402 -U bucardo -d postgres

Note that by default we connect as the 'bucardo' user. This is a highly recommended practice, for safety and auditing. Since that user obviously does not exist on the newly created databases, we need to add them in:

$ psql -p 5401 -c 'create user bucardo superuser'
$ psql -p 5402 -c 'create user bucardo superuser'

Now we need to teach Bucardo about the tables we want to replicate:

$ bucardo_ctl add table trtest db=master herd=herd1
Created herd "herd1"
Table added: public.trtest

A herd is simply a named connection of tables. Typically, you put tables that are linked together by foreign keys or other logic into a herd so that they all get replicated at the same time.

The final setup step is to create a replication event, which in Bucardo is known as a 'sync':

$ bucardo_ctl add sync willow source=herd1 targetdb=slave1 type=pushdelta
NOTICE:  Starting validate_sync for willow
CONTEXT:  SQL statement "SELECT validate_sync('willow')"
Sync added: willow

This command actually did quite a bit of work behind the scenes, including creating all the supporting schemas, tables, functions, triggers, and indexes that Bucardo will need.

We are now ready to start Bucardo up. Simple enough:

$ bucardo_ctl start
Checking for existing processes
Starting Bucardo

Let's add a row to the master table and make sure it goes to the slave:

$ psql -p 5401 -c 'insert into trtest(id) VALUES (1)'
INSERT 0 1
$ psql -p 5402 -c 'select * from trtest'
 id
----
  1
(1 row)

Looks fine, so let's try out the truncate. On versions of Postgres less than 8.4, there was no way for Bucardo (or Slony) to know that a truncate had been run, so the rows were removed from the master but not from the slave. We'll do a truncate and add a new row in a single operation:

$ psql -p 5401 -c 'begin; truncate table trtest; insert into trtest values (2); commit'
COMMIT
$ psql -p 5402 -c 'select * from trtest'
 id
----
  2
(1 row)

It works! Let's clean up our test environment for good measure:

$ bucardo_ctl stop
$ pg_ctl stop -D bcdata
$ pg_ctl stop -D testA
$ pg_ctl stop -D testB

As mentioned, there are three types of syncs in Bucardo. The other type that can make use of truncate triggers is the 'swap' sync, aka "master to master". I've not yet decided on the behavior for such syncs, but one possibility is simply:

• Database A gets truncated at time X
• Bucardo truncates database B, then discards all delta rows older than X for both A and B, and all delta rows for B
• Everything after X gets processed as normal (conflict resolution, etc.)
• The same thing for a truncate on database B (truncate A, discard all older rows).

Second proposal:

• Database A gets truncated at time X
• We populate the delta table with every primary key in the table before truncation (assuming we can get at it)
• That's it! Bucardo does its normal thing as if we just deleted a whole bunch of rows on A, and in theory deletes them from B as well.

Comments on this strategy welcome!

Update: Clarified initdb cluster vs. database per comment #1 below, and added new truncation handling scheme for multi-master replication per comment #2.

In Interchange, You Might Need to [try] [goto]. What's the [catch]?

Interchange provides tags that allow error trapping and handling within ITL--[try] and [catch]--that can be thought of as analogous to perl's eval {} followed by if ($@) {}. However, as I discovered the hard way, the analogy is not perfect.

I set up a block of ITL within [try] that had two major actions, with the 2nd depending on the success of the first. In particular, these two actions were a credit card authorization, followed by a capture of that auth as long as (a) the authorization succeeded, and (b) the merchant's internal rules for analyzing order content compared to AVS results "passed". (b) was necessary as a fraud-protection measure, tightening up the impact of AVS results based on the historic tendency of certain products to be targeted by crooks. In the event that the auth succeeded, but the tests from (b) failed, it is very important that the capture never be attempted because, to the gateway, the auth is entirely valid and the catpure attempt would succeed.

The code that assesses whether AVS passes is done in its own [calc]. From within the code, if the assessment does not pass, the code issues a die(), which in fact does trigger [try] to log the error that becomes accessible in [catch] via the $ERROR$ token, and thus does trigger [catch] to execute its body contents. In that way, the [try] did trap the error, and the error was handled in [catch], but of course that's not the end of the story or this post wouldn't exist.

After the code had been in production for some time, David Christensen brought to my attention that he noticed in development a test order attempt, where the order attempt failed, but both the auth and the capture succeeded. I was highly dubious of this claim and went over in great detail just what he had done. We narrowed down the condition that produced the problem to (b) above: a successful auth, but abort the order attempt anyway because a high-value product in the cart was coupled with a questionable AVS. When I went to the logs, I could see the result spelled out, but the result made no sense to my understanding:

### starting credit card processing ###
Real-time full auth succeeded. ID=***
0
Real-time full capture succeeded. ID=***
Error detected for order xxx in the credit card charge.

The 0 indicated the [calc] had failed (died), yet the capture later in the [try] was still executing. The only conclusion was that, unlike eval {}, when [try] trapped an error, it just kept right on processing the continuing ITL. [try] always went forward and processed all its ITL to the end, and whatever happened to be the last die() called within that batch of ITL would be the thing that [catch] caught and displayed.

To resolve the problem, I introduced [goto] into the block, which stops the instance of interpolate_html() running at the point of encounter and returns. Continuing to use the die() call to populate the error code from [try], immediately after the [calc] test block I called [goto] conditionally on whether the [calc] block, in fact, died. The [goto] call then terminated the instance of interpolate_html() that [try] had invoked on its body, which had the effect of stopping ITL execution at the point of the die().

This approach to emulating eval {}/if ($@) {} has the significant flaw of developers needing to know ahead of time exactly where in the [try] block such failures are expected. If such is unknowable, it leaves developers in the unenviable position of having to follow each tag call with a conditional [goto] that has to know when the previous tag "failed" (i.e., triggered a die() somewhere).

Test::Database Postgres support

At our recent company meeting, we organized a 'hackathon' at which the company was split into small groups to work on specific projects. My group was Postgres-focused and we chose to add Postgres support to the new Perl module Test::Database.

This turned out to be a decent sized task for the few hours we had to accomplish it. The team consisted of myself (Greg Sabino Mullane), Mark Johnson, Selena Deckelmann, and Josh Tolley. While I undertook the task of downloading the latest version and putting it into a local git repository, others were assigned to get an overview of how it worked, examine the API, and start writing some unit tests.

In a nutshell, the Test::Database module allows an easy interface to creating and destroying test databases. This can be a non-trivial task on some systems, so putting it all into a module make sense (as well as the benefits of preventing everyone from reinventing this particular wheel). Once we had a basic understanding of how it worked, we were off.

While all of our tasks overlapped to some degree, we managed to get the job done without too much trouble, and in a fairly efficient manner. We made a new file for Postgres, added in all the required API methods, wrote tests for each one, and documented everything as we went along. The basic method to create a test database is to use the initdb program to create a new Postgres cluster, then modify the cluster to use a local Unix socket in the newly created directory (this side-stepping completely the problem of using an already occupied port). Then we can start up the new cluster via the pg_ctl command, and create a new database.

At the end of the day, we had a working module that passed all of its tests. We combined our git patches into a single one mailed it to the author of the module, so hopefully you'll soon see a new version of Test::Database with Postgres support!

Perl 5 now on Git

It's awesome to see that the Perl 5 source code repository has been migrated from Perforce to Git, and is now active at http://perl5.git.perl.org/. Congratulations to all those who worked hard to migrate the entire version control history, all the way back to the beginning with Perl 1.0!

Skimming through the history turns up some fun things:

• The last Perforce commit appears to have been on 16 December 2008.
• Perl 5 is still under very active development! (It seems a lot of people are missing this simple fact, so I don't feel bad stating it.)
• Perl 5.8.0 was released on 18 July 2002, and 5.6.0 on 23 March 2000. Those both seem so recent ...
• Perl 5.000 was released on 17 October 1994.
• Perl 4.0.00 was released 21 March 1991, and the last Perl 4 release, 4.0.36, was released on 4 February 1993. For having an active lifespan of only 4 or so years till Perl 5 became popular, Perl 4 code sure kicked around on servers a lot longer than that.
• Perl 1.0 was announced by Larry Wall on 18 December 1987. He called Perl a "replacement" for awk and sed. That first release included 49 regression tests.
• Some of the patches are from people whose contact information is long gone, rendered in Git commits as e.g. Dan Faigin, Doug Landauer <unknown@longtimeago>.
• The modern Internet hadn't yet completely taken over, as evidenced by email addresses such as isis!aburt and arnold@emoryu2.arpa.
• The first Larry Wall entry with email address larry@wall.org was 28 June 1988, though he continued to use his jpl.nasa.gov after that sometimes too.
• There are some weird things in the commit notices. For example, it's hard to believe the snippet of Perl code in the following change notice wasn't somehow mangled in the conversion process:

commit d23b30860e3e4c1bd7e12ed5a35d1b90e7fa214c
Author: Larry Wall <lwall@scalpel.netlabs.com>
Date:   Wed Jan 11 11:01:09 1995 -0800

   duplicate DESTROY
  
   In order to fix the duplicate DESTROY bug, I need to remove [the
   modified] lines from sv_setsv.
  
   Basically, copying an object shouldn't produce another object without an
   explicit blessing.  I'm not sure if this will break anything.  If Ilya
   and anyone else so inclined would apply this patch and see if it breaks
   anything related to overloading (or anything else object-oriented), I'd
   be much obliged.
  
   By the way, here's a test script for the duplicate DESTROY.  You'll note
   that it prints DESTROYED twice, once for , and once for .  I don't
   think an object should be considered an object unless viewed through
   a reference.  When accessed directly it should behave as a builtin type.
  
   #!./perl
  
    = new main;
    = '';
  
   sub new {
       my ;
       local /tmp/ssh-vaEzm16429/agent.16429 = bless $a;
       local  = ;      # Bogusly makes  an object.
       /tmp/ssh-vaEzm16429/agent.16429;
   }
  
   sub DESTROY {
       print "DESTROYED\n";
   }
  
   Larry

sv.c |    4 ----
1 files changed, 0 insertions(+), 4 deletions(-)

Yes, it really is that weird. Check it out for yourself.

The Easy Git summary information from eg info has some interesting trivia:

Total commits: 36647
Number of contributors: 926
Number of files: 4439
Number of directories: 657
Biggest file size, in bytes: 4176496 (Changes5.8)
Commits: 31178

And there's a nice new POD document instructing how work with the Perl repository using Git: perlrepository.

In other news, maintenance release Perl 5.8.9 is out, expected to be the last 5.8.x release. The change log shows most bundled modules have been updated.

Finally, use Perl also notes that Booking.com is donating $50,000 to further Perl development, specifically Perl 5.10 development and maintenance. They're also hosting the new Git master repository. Thanks!

Creating a PL/Perl RPM linked against a custom Perl build

My and everyone else's Planet PostgreSQL blogs have been down for some time now. I recently needed to refer to a post I made on March 7, 2007, showing how to build a PL/Perl RPM linked against a custom Perl build. Of course it wasn't there! The archive.org Wayback Machine came to my rescue.

A few things have changed since that time, so I've reworked it here, updated for local Perl 5.10.0 built into RPMs:

We sometimes have to install a custom Perl build without thread support, and to have some specific newer and/or older versions of CPAN modules, and we don't want to affect the standard distribution Perl that lives in /usr/bin/perl and /usr/lib/perl5. We use standard PGDG RPMs to install PostgreSQL. We also use PL/Perl, and want PL/Perl to link against our custom Perl build in /usr/local/bin and /usr/local/lib/perl5.

It's easy to achieve this with a small patch to the source RPM spec file:

--- postgresql-8.3.spec 2008-10-31 17:34:34.000000000 +0000
+++ postgresql-8.3.custom.spec  2008-11-30 02:10:09.000000000 +0000
@@ -315,6 +315,7 @@
 CFLAGS=`echo $CFLAGS|xargs -n 1|grep -v ffast-math|xargs -n 100`
 
 export LIBNAME=%{_lib}
+export PATH=/usr/local/bin:$PATH
 %configure --disable-rpath \
 %if %beta
    --enable-debug \
@@ -322,6 +323,7 @@
 %endif
 %if %plperl
    --with-perl \
+   --with-libraries=/usr/local/lib64/perl5/5.10.0/x86_64-linux/CORE/libperl.so \
 %endif
 %if %plpython
    --with-python \

Since we build RPMs of our local Perl, we want this PL/Perl RPM to depend on them, so we make this additional patch before building:

--- postgresql-8.3.spec 2008-10-31 17:34:34.000000000 +0000
+++ postgresql-8.3.custom.spec  2008-11-30 02:10:09.000000000 +0000
@@ -100,7 +100,7 @@
 Patch6:        postgresql-perl-rpath.patch
 Patch8:        postgresql-prefer-ncurses.patch
 
-Buildrequires: perl glibc-devel bison flex 
+Buildrequires: local-perl = 4:5.10.0, glibc-devel, bison, flex
 Requires:  /sbin/ldconfig initscripts
 
 %if %plpython
@@ -227,7 +227,7 @@
 %package plperl
 Summary:   The Perl procedural language for PostgreSQL
 Group:     Applications/Databases
-Requires:  postgresql-server = %{version}-%{release}
+Requires:  postgresql-server = %{version}-%{release}, local-perl = 4:5.10.0
 %ifarch ppc ppc64
 BuildRequires:  perl-devel
 %endif

After applying the patch(es) (adjusted for your own custom Perl build, of course), rebuild the RPM, and install the postgresql-plperl (8.2 or newer) or postgresql-pl (8.1 and earlier) RPM. With a "service postgresql restart", you're ready to go.

For pre-built PostgreSQL 8.3 RPMs that link against unthreaded local Perl 5.10.0, for Red Hat Enterprise Linux 5 x86_64, see the packages.endpoint.com Yum repositories.

PostgreSQL Conference West 2008 Report

I attended the PostgreSQL Conference West and had a great time again this year. My photos of the event are up here:

http://db.endpoint.com/pg-conf-08

In addition, I shot some footage of the event in an attempt to highlight the benefits of the conference, Postgres itself, and the community strengths. I'm looking for a talented Editor willing to donate time; if none volunteer then I'll probably do it in January. My guess is that there will be several web sites willing to host it for free when it's done.

The Code Sprint was really interesting. Selena Deckelmann gave everyone a lot of ideas to get the most out of the time available for hacking code. At regular intervals, each team shared the progress they made and recieved candy as a reward. It was neat to see other people hacking on and committing changes to the Postgres source tree in meatspace.

Bruce Momjian's Postgres training covered a wide gamut of information about Postgres. He polled everyone in the room for their particular needs, which varied from administration to performance, then tailored the training to cover information relating to those needs in particular detail. Those who attended reported that they learned a great deal of new information from the training. From here, a lot of folks went out to continue interacting with Postgres people, but I headed for home.

Windowing Functions were covered by David Fetter in a talk that addressed ways to make OLAP easier with new features coming to Postgres 8.4. Functionality that used to be slow and difficult in client-side applications can be handled easily right in the database. I made a note to check this out when 8.4 hits the streets.

Jesse Young spoke about using Linux-HA + DRBD to build high availability Postgres clusters. It is working very well for him in over 30 different server installations; he proved this by taking down a production server in the middle of the presentation and demonstrating the rapid transition to the failover server. Just set-it-and-forget-it. I was able to weigh the advantages and disadvantages compared to other clustering options such as shared disk (e.g. GFS) and Postgres-specific replication options (Slony, Postgres Replicator, Bucardo, etc.).

In his talk, PostgreSQL Optimizer Exposed, Tom Raney delved into a variety of interesting topics. He described the general workings of the optimizer, then showed a variety of interesting plans that are evaluated for the example query, how each plan was measured for cost, and why the cost varied. He uncovered several interesting facts, such as demonstrating that the Materialization step (pushing sorts to disk that are too large for memory) doesn't increase the cost associated with that plan. Tom Lane explained that this would rarely, if ever, affect real world results, but that is the kind of information made obvious in the Visual Planner, but hidden by textual EXPLAIN ANALYZE. Tom Raney also demonstrated the three-fold difference (in one case) between the cost of the clustered index and the rest. Optimizing query performance is one of my favorite pastimes, so I enjoyed this talk a lot.

I learned a bit about what was going in Postgres community organizations during Joshua Drake's talk, "PostgreSQL - The happening since EAST". The PostgreSQL.US and other organizations are doing a lot to increase awareness of Postgres among education, government, business, and other developers. The point was made that we should do as much as we can to reach out to widely prevalent PHP applications and web hosting providers.

Common Table Expressions (CTE) were given a good explanation by David Fetter in his talk about doing Trees and More in SQL. Having worked on Nested Set and Adjacency List models, I was very interested in this new feature coming to 8.4. Starting with a simple recursive example, David built on it slide-by-slide until he had built and executed a brute force solution to a Traveling Salesman Problem (for a small number of cities in Italy) using only plain SQL. I'm excited to try this out and measure the performance.

Mark Wong & Gabrielle Roth presented the results of testing that they completed. Selena also covered that information in her post about Testing filesystems and RAID. After that we talked Perl on the way to the Portland Paramount for the party.

On Sunday, I sat in on "Developing a PL (Procedural Language) for PostgreSQL", by Joshua Tolley, as he carefully explained the parts and steps involved. LOLCODE humor peppered the useful information on the related Postgres libraries, Bison usage, and pitfalls.

I was glad to see Catalyst boosted in Matt Trout's presentation. He very quickly covered the design and advantages of Catalyst, DBIx::Class, and Postgres as they related to the implementation of a high profile and complex web site. It was very informative to see the Class structure for the View model, which gave me several ideas to take use for my own development. He demonstrated how a complex 56-way join was coded in very brief and comprehensible perl code relying on the underlying modules to provide the underlying support. The explain tree is so large that it couldn't fit on the screen even in microscopic font, and even with very large data sets, the Postgres optimizer found a way to return the results in one tenth of a second. Matt also demonstrated several flaws in his design, such as how his use of rules to implement updatable views caused multi-row updates to be slower than the equivalent trigger-based system. I use Catalyst for several projects, but I think Interchange still has more advantages. I'm definitely going to take another look at DBIx::Class.

Before lunch, I asked if I could shoot a group photo, so we went to the park. Several people were not in attendance, and I didn't want to take more than a minute or two, so the shots are not as good as I would have liked. Next time I'll ask if we can plan some time for arranging the group. At lunch I had a great time talking to fellow Postgres developers and learning more about their work.

Lightning Talks followed lunch and included a variety of interesting topics. One of my favorites was "I can't do that" by Matt Trout. He explained how wrong it is to believe you can't contribute something to Postgres or any other open source project. If you think your code will be incomplete or buggy, do it anyway, because it may prompt someone else to work on it, or scrap yours and do it right. Don't think you can't contribute to documentation because of your infamiliarity with the system, because that's exactly the advantage you have for documentation contributions: those who need the docs are in exactly your shoes.

Matt also gave the closing talk, "Perl 5 is Alive!", which was a concise, water-tight presentation of Perl 5's superiority over other development environments, including CPAN and job statistics that demonstrate its growing popularity.

Some attendees went out afterwards to finish the conference over a drink. I slept about 11 hours straight to recover from the whirlwind of weekend activity. Overall I'm grateful for the opportunity to interact with the community again and I'm excited for what the future has in store for Postgres.

Moose roles

Perl programmers,

Moose roles give a really nice way of maximizing code reuse within an object system, while favoring composition over inheritance. This makes for shallower inheritance trees, reduced method dispatch spaghettification, and a more comprehensible, maintainable, extensible codebase overall. Revel in the glory.

That is all.

On "valid" Unix usernames and one's sanity

Today poor Kiel ran into an agonizing bug. A few weeks ago, building a custom RPM of perl-5.10.0 (that is, the Perl distribution itself) wasn't a problem. The unit tests passed with nary a care.

But today it no longer worked. I'll omit details of the many false paths Kiel had to go down in trying to figure out why an obscure test in the Module::Build package was failing. Eventually I took a look and noted that he'd tried all the logical troubleshooting. Time to look at the ridiculous. What if the test was failing because the last time he built it successfully it was under the user "rpmbuild", while he was now trying with user "rpmbuild-local"?

That was exactly the problem. Module::Build's tilde directory (~username) parser was of the (false) opinion that usernames consist only of \w, that is, alphanumerics and underscores. The reality is that pretty much anything is valid in a username, though some characters will cause trouble in various contexts (think of / : . for example).

I explained in more detail in CPAN bug #33492 which reports someone else's experience with the test failing when the username had a backslash in it, such as the Active Directory name "RIA\dillman".

Fun times.

Perl on Google App Engine

People are working on getting Perl support for Google App Engine, led by Brad Fitzpatrick (of Livejournal, memcached, etc. fame) at Google.

They've created a new module, Sys::Protect, to simulate the restricted Perl interpreter that would have to exist for Google App Engine. There's some discussion of why they didn't use Safe, but it sounds like it's based only on rumors of Safe problems, not anything concrete.

Safe is built on Opcode, and Sys::Protect appears to work the same way Safe + Opcode do, by blocking certain Perl opcodes. All the problems I've heard of and personally experienced with Safe were because it was working just fine -- but being terribly annoying because many common Perl modules do things a typical Safe compartment disallows. That's because most Perl module writers don't use Safe and thus never encounter such problems. It seems likely that Sys::Protect and a hardened Perl Google App Engine environment will have the same problem and will have to modify many common modules if they're to be used.

Moving on, posters are talking about having support for Moose, Catalyst, CGI::Application, POE, Template::Toolkit, HTML::Template ... well, a lot. I guess that makes sense but it will be a lot of work and complicates the picture compared to the simple Python and custom Django-only initial unveiling of Google App Engine.

If you're interested in Perl support for Google App Engine, log into your Google account, visit the "issue" page, and click on the star by the title to vote in favor of Perl support.

Perl incompatibility moving to 5.10

We're preparing to upgrade from Perl 5.8.7 to 5.10.0 for a particular project, and ran into an interesting difference between the two versions.

Consider the following statement for some hashref $attrib:

  use strict;
  ...
  my ($a, $b, $c) = @{%{$attrib}}{qw(a b c)};

In 5.8.7, the @{...} construct will return a slice of the hash referenced by $attrib, meaning that $a gets $attrib->{a}, $b gets $attrib->{b}, and so on.

In 5.10.0, the same construct will result in an error complaining about using a string for a hashref.

I suspect it's due to the hash dereference (%{$attrib}) being fully executed prior to applying the hash-slice operation (@{...}{qw(a b c)}), meaning that you're not operating against a hashref anymore.

Fortunately, the fix is wonderfully simple and significantly more readable:

  my ($a, $b, $c) = @$attrib{qw( a b c )};

The "fix" -- which is arguably how it should have been constructed in the first place, but this is software we're talking about -- works in both versions of Perl.

Building Perl on 64-bit RHEL/Fedora/CentOS

When building Perl from source on 64-bit Red Hat Enterprise Linux, Fedora, CentOS, or derivatives, Perl's Configure command needs to be told about the "multilib" setup Red Hat uses.

The multilib arrangement allows both 32-bit and 64-bit libraries to exist on the same system, and leaves the "non-native" 32-bit libraries in /lib and /usr/lib while the "native" 64-bit libraries go in /lib64 and /usr/lib64. That allows the same 32-bit RPMs to be used on either i386 or x86_64 systems. The downside of this is that 64-bit applications have to be told where to look for, and put, libraries, or they usually won't work.

For Perl, to compile from a source tarball with the defaults:

./Configure -des -Dlibpth="/usr/local/lib64 /lib64 /usr/lib64"

Then build as normal:

make && make test && sudo make install

I hope this information will come in handy for someone. I believe I learned it from Red Hat's source RPM for Perl.

Bucardo: Replication for PostgreSQL

Overview

Bucardo, an asynchronous multi-master replication system for PostgreSQL, was recently released by Greg Sabino Mullane. First previewed at this year's PostgreSQL Conference in Ottawa, this program was developed for Backcountry.com to help with their complex database needs.

Bucardo allows a Postgres database to be replicated to another Postgres database, by grouping together tables in transaction-safe manner. Each group of tables can be set up in one of three modes:

1. The table can be set as master-master to the other database, so that any changes to either side are then propagated to the other one.
2. The table can be set up as master-slave, so that all changes made to one database are replicated to the second one.
3. It can be set up in "fullcopy" mode, which simply makes a full copy of the table from the master to the slave, removing any data already on the slave.

Master-master replication is facilitated by standard conflict resolution routines, as well as the ability to drop in your own by writing small Perl routines. This custom code can alse be written to handle exceptions that often occur in master-master replication situations, such as a unique constraint on a non-primary key column.

History

Backcountry.com, an online retailer of high-end outdoor gear, needed a way to keep their complex, high-volume, Postgres databases in sync with each other in near real-time, and turned to End Point for a solution. In 2002, the first version of Bucardo was rolled out live, and reliably replicated billions of rows. In 2006, Bucardo was rewritten to employ new features, including a robust daemon model, more flexible configuration and logging, custom conflict and exception handling routines, much faster replication times, and a higher level of self-maintenance. This new version has been in production at Backcountry.com since November 2006.

In September 2007, the source code for Bucardo version 3.0.6 was released under the same license as Postgres itself, the flexible BSD license. A website and mailing lists were created to help foster Bucardo's development. The website can be found at bucardo.org.

YAPC::NA 2006 Conference Report

End Pointers Jon Jensen and I, along with 450-500 other Perl enthusiasts, descended on the campus of IIT, the Illinois Institute of Technology in Chicago, for three days at the end of June for the annual North American edition of what is affectionately known by the Perl community as Yet Another Perl Conference (YAPC).

This year's conference had three main focuses covered by four tracks of talks: Web 2.0 (as the hypesters like to call it), software development methodology improvements, and Perl 6 -- the future of the language. Participants and speakers had a range of experience with Perl and varied backgrounds, from experts working for the Perl Foundation on the forefront of Perl 6 development to beginners finding out how best to implement their first assignment.

Perl 6, while not immediately practical for daily use at this time, is advancing the language at the heart of most of End Point's development. Perl 6 represents advancements in language design that will likely bring Perl back to the forefront of dynamic language syntax and research. But Perl 5 remains in the practical lead it's long held, with the amazingly wide-ranging and useful CPAN, and it is still progressing in research as well. One talk covered the upcoming release of Perl 5.10, which incorporates some of the much-anticipated improvements in Perl 6. The Moose object system now brings much of the Perl 6 object design philosophy to Perl 5 developers as well.

"Web 2.0", on the other hand, has fully arrived, and at End Point we have already deployed sites using several of these techniques to improve interactivity and user experience. Doing so does not require a fundamental shift to a new framework or interface design methodology; we have been incorporating many newer techniques into the already mature code base of Interchange. Talks on JavaScript, AJAX, JSON, and JSAN (the JavaScript equivalent of CPAN), provided a solid foundation for user experience improvements. Talks about Catalyst, CGI::Application, Solstice, Jifty, and POE, showed how varied server-side application frameworks can help developers achieve their goals more quickly and with higher quality. Additional talks about technologies underlying these frameworks rounded out the details, particularly along these lines: MVC (model-view-controller) web development and templating systems such as Mason, object implementations such as the aforementioned Moose and inside-out objects, object/relational database mappers such as UR, and direct object storage with Presto.

Running central through all of the talks was an emphasis on design quality, methodology improvement, and enterprise class application development. Several talks showed how source code could be better managed using the increasingly popular Subversion source code control system with svk providing offline repository management at the client side. Others detailed how to test applications for improved quality assurance using automated testing platforms such as Test::WWW::Simple and Selenium, a browser based framework for testing web applications on multiple platforms in multiple browsers. Still others were less technical in essence and were tailored towards improving the general workflow of the developer through all phases of project implementation. One of these included a talk about getting out of "technical debt", which used a metaphor comparing personal financial bankruptcy to what happens when a software project ignores crucial elements such as testing, documentation, and backups, the so-called interest accumulating on top of the work already needing to be done to meet the stated goal. A second focused on common mistakes made when trying to scale systems to an arbitrarily large size.

But YAPC isn't just about the technical talks. It is also about building and improving the community surrounding the language which is always so fundamental to the success of open source software. Through informal Birds of a Feather gatherings, whether they are a social trip to see the sights of the city from the top of the Sears Tower or to hear the amazing sounds of musicians brought to the scenic Millenium Park by Yo-Yo Ma and the Silk Road Ensemble. Or through a job fair open to all sizes of companies where Perl developers can seek out new mediums of expression or where companies can find just the right person to take their next project to completion. The sponsors of, presenters at, and attendees of YAPC know that it is the experience as a whole that is the real reason to go.

More information can be found at the conference website at yapcchicago.org