On Mon, Sep 27, 2010 at 3:27 PM, Gurjeet Singh <singh.gurjeet@xxxxxxxxx> wrote: > On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke <dcrooke@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
