The case of the blocking CREATE INDEX call

I’d been working on a new functional index for the work website. I created a Pgsql function to normalize the title of a book

CREATE OR REPLACE FUNCTION exacttitle_key( TEXT )
RETURNS text AS $$
DECLARE
key TEXT := upper( $1 );
BEGIN
key = regexp_replace( key,
'^ *(?:A|AN|EL|LA|LO|THE|LOS|LAS)\M *', '' );
key = regexp_replace( key, '[^0-9A-Z ]+', '', 'g' );
key = regexp_replace( key, ' {2,}', ' ', 'g' );
RETURN trim( key );
END
$$ LANGUAGE 'plpgsql'
IMMUTABLE STRICT;

and tested it out, and all looked well. It was marked as IMMUTABLE, so Pg can use it as an index function. I created the index in psql:

create index testbook_exacttitle on testbook
using btree (exacttitle_key(title));

And all was well. Now I wanted to see how long it took to create that index, so from the shell I did:

$ time psql -c'drop index testbook_exacttitle; 
create index testbook_exacttitle  on testbook 
using btree (exacttitle_key(title));'

I knew it would take about 5 minutes to add this index on 6.7 million records in testbook, so I didn’t expect it to come back right away. Then I realized that site response fell off the table. ptop showed a couple dozen SELECT queries waiting to run. I killed the process that was running the CREATE INDEX. All the pending queries went on their merry way. Everything was back to normal.

I tried that command line again, and the results were identical. Dozens of queries backed up until I killed the CREATE INDEX process. But why were those queries backing up? That index was not used by any code yet. I asked in #postgresql, but nobody knew the answer. Then, someone said a word that clicked in my head. I made a little change to how I was running the commands, and everything worked just fine.

What was the word that helped Encyclopedia Lester figure out the problem? Turn to page 47 for the answer.

The word was “transaction”. If there are multiple commands as part of the -c option to psql, they are executed in in one transaction. DROP INDEX blocks on the entire table, so the entire transaction blocked. When I ran the DROP INDEX separately, and then reran the CREATE INDEX by itself, there was only the long blocking on the new index, which did not yet exist.

(With apologies to Donald J. Sobol and Encyclopedia Brown)

YAPC::NA 2008 offers something for everyone

The schedule for YAPC::NA just got published, and there’s plenty of good stuff this year. If you haven’t decided to make the trip out to Chicago June 16-18 yet, this should help.

Cool stuff that jumps out at me as I peruse the grid: JT Smith talking about the premade application stack that WebGUI uses, Schwern on testing data with The Sims, and Kevin Falcone on timezone handling.

For the beginners, Kent Cowgill’s intro to testing is a great way to get introduced to the topic, and I’m sure that Leonard Miller talking about Perl::Tidy and Perl::Critic will help instill good coding practices.

New this year, on Wednesday there will be workshops. Stevan Little will host a 2-hour Moose tutorial, and Jim Keenan will help you get started building and working with Parrot and Rakudo Perl.

Do you have recommendations on must-see talks? Let your fellow Perlbuzz readers know in the comments below.

Vienna.pm funds Jonathan Worthington to work on Rakudo Perl

From Thomas Klausner:

At the Oslo QA Hackathon 2008, during one evening meal, it became evident that
Jonathan Worthington would be able to spend even more time hacking on Rakudo
Perl
if he would get paid a little money for it. As Vienna.pm still has some
money earmarked for Perl development, we encouraged Jonathan to send us a
proposal for funding him. Which he did. And which we accepted.


So starting next week, Jonathan will work on Rakudo one full day a week
(minimum of 8 hours of work), post about the work on the rakudo.org blog /
use.perl.org. He will recieve € 150 per day spend working on Rakudo. We
estimate that on average he will work 4 days per month. We agreed on funding
three months (~ €1,800) and evalute the grant after that time. If everybody is
happy, we will continue the grant until the end of 2008, where we will evaluate
again (and check if we still have money left).



More info available in the WoC Wiki.

Asciio lets you create ASCII charts graphically

Nadim has released Asciio, a Perl/GTK application that lets you draw ASCII charts using a GUI. Objects on the screen are sizable and have all the properties you’d expect in a drawing tool (titles for the boxes, bullets, etc), but the end result is plain text that’s embeddable in your code.

Here’s the first of two screencasts that give you a feel for its capabilities.

[youtube https://www.youtube.com/watch?v=IiOHYNHo_Nw&hl=en]

Nadim says “I find myself starting it quite often just to draw a little diagram that helps me write better Perl code. Then I can keep it in the code itself.”

What commands do you run?

People have been posting in their blogs about what command they run, based on their shell histories. The command that I’ve seen looks like this:

history|awk '{a[$2]++} END{for(i in a){ 
printf "%5dt%s n",a[i],i}}'|sort -rn|head

That works, of course, but who wants to use awk and the shell? I pulled out the old Data::Hash::Totals module I wrote a while back, along with Perl’s built-in awk simulation:

$ history | perl -MData::Hash::Totals -ane'$x{$F[1]}++;' 
-e'END{print as_table(%x, comma => 1)}' | head
207 vim
143 svn
125 make
90 ack
77 cd
45 sdvx
34 ssq
31 ls
25 ./login-fixup
19 tail
alester:~ : cat `which sdvx`
#!/bin/sh
svn diff -x -w $* | view -

and ssq is just an alias for svn status -q.