Andy Lester: April 2008 Archives

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)

What commands do you run?

| | Comments (2)

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 "%5d\t%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.

About this Archive

This page is a archive of recent entries written by Andy Lester in April 2008.

Andy Lester: March 2008 is the previous archive.

Andy Lester: May 2008 is the next archive.

Find recent content on the main index or look in the archives to find all content.

Other Perl Sites

Other Swell Blogs

  • geek2geek: An ongoing analysis of how geeks communicate, how we fail and how to fix it.