Thanks folks, that makes sense. We're now being more precise with our DDL :-)
Cheers Dave On Thu, Oct 7, 2010 at 3:40 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Sep 27, 2010 at 3:27 PM, Gurjeet Singh <singh.gurj...@gmail.com> > wrote: > > On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke <dcro...@gmail.com> wrote: > >> > >> Our Java application manages its own schema. Some of this is from > >> Hibernate, but some is hand-crafted JDBC. > >> > >> By way of an upgrade path, we have a few places where we have added > >> additional indexes to optimize performance, and so at startup time the > >> application issues "CREATE INDEX ..." statements for these, expecting to > >> catch the harmless exception "ERROR: relation "date_index" already > exists", > >> as a simpler alternative to using the meta-data to check for it first. > >> > >> In general, this seems to work fine, but we have one installation where > we > >> observed one of these CREATE statements hanging up in the database, as > if > >> waiting for a lock, thus stalling the app startup > > > > You can tell if it is really waiting by looking at 'select * from > pg_locks', > > and check the 'granted' column. > > CREATE INDEX (without CONCURRENTLY) tries to acquire a share-lock on > the table, which will conflict with any concurrent INSERT, UPDATE, > DELETE, or VACUUM. It probably tries to acquire the lock before > noticing that the index is a duplicate. CREATE INDEX CONCURRENTLY > might be an option, or you could write and call a PL/pgsql function > (or, in 9.0, use a DO block) to test for the existence of the index > before trying create it. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company >
