Create Index CONCURRENTLY

I saw Depesz just came up against something on pgsql-bugs that I've done a couple of times.

CREATE INDEX CONCURRENTLY
ON tablename (column list);

Works perfectly. It adds an index called "concurrently" onto "tablename". That part is actually quite funny, and easily correctable using

ALTER INDEX concurrently RENAME TO something_sensible;

It's a very good lesson in why its a great idea to use tab completion, or a GUI. I have another story about that though, for another day.

Anyway, the really painful part about creating our index above is that the DBA (yours truly) intended to create an index concurrently, that is "concurrently" as an adverb rather than noun. The result is that you run a normal CREATE INDEX statement, so you end up applying a ShareLock to the table and all writes against the table stop. Of course, that happens right at the moment where you decide a cappuccino is a great plan and drive out for a round of coffee while the index build runs. Neat huh?

I figure I'm not the only person to forget to make mistakes and if we never admit them the software won't improve. So I'll see if we can come up with a little patch that avoids us adding "concurrently" as a reserved word. Not sure I see the problem really, since I never met anyone who named their database objects using adverbs rather than nouns. I bet there's one. There always is.

Anyway, the moral of the story is use

CREATE INDEX CONCURRENTLY indexname
ON tablename (column list);

Well actually, its not, but this is: Production systems are like lions - never turn your back on them, even after they've been fed, no matter how long you've been training them. And even lion tamers get bitten, sometimes. (And that is why I spend so long developing recovery features).