Just an idea without bigger investigation: Whare are the idle in
transactions are doing? Maybe they are blocking the create index.
Cheers,
Frank
Good question, I don't know. The app runs on Java / Apache Tomcat, which maintains a connection pool, so I assumed the IDLEs were just opened connections but I didn't notice two were idle in transaction. I haven't dealt with this before but this looks like a good explanation:
I will up logging and try to replicate. Next time I will look at pg_locks too.
Also, the PG version is 9.1.6.
Oliver