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)