Month: April 2008

The case of the blocking CREATE INDEX call

April 29, 2008 Databases 1 comment

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)

Follow Perlbuzz on Twitter

April 28, 2008 Uncategorized 1 comment

I’ve created a Twitter feed for Perlbuzz. I’m going to start posting links there on cool things I find throughout the day, but that don’t merit a full-blown Perlbuzz or Mechanix story.

Eventually I’ll have all Perlbuzz and Mechanix stories posted there automagically, too.

YAPC::NA 2008 offers something for everyone

April 25, 2008 Conferences, Perl 5, Perl 6, Rakudo No comments

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

April 25, 2008 Community, Perl 6, Rakudo No comments

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.

Looking after your bugs with Request Tracker

April 24, 2008 Uncategorized 5 comments

Linux Magazine has a big introductory article called Looking after your bugs with Request Tracker. RT is the bug tracking system used for
Perl 5,
Parrot,
Perl 6 and all the CPAN modules, at least by default.

WebGUI Users Conference announced for August 26-29, 2008

April 24, 2008 Conferences No comments

Plain Black has announced the WebGUI Users Conference this summer in Madison, WI. WebGUI is a GPLed CMS written in Perl with thousands of customers around the world, and gets 5,000 downloads each day.

Asciio lets you create ASCII charts graphically

April 22, 2008 Code craft, CPAN 2 comments

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.”

I wish I could use smart matching

April 18, 2008 Perl 5 3 comments

We’re nowhere near ready to switch to Perl 5.10 at work, even with the release of mod_perl to support it. I’d like to use smart matching all the time. Perltraining.au’s put out an article about smart matching in its series of Perl tips that just makes me cry for what I can’t use.

mod_perl now supports Perl 5.10

April 17, 2008 Perl 5 No comments

mod_perl 2.04 has been released and it supports Perl 5.10. If mod_perl has been a barrier to your uptake of Perl 5.10, there’s no longer a reason to wait.

What commands do you run?

April 16, 2008 Tools 2 comments

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.