Re: Odd behaviour with redundant CREATE statement

On Mon, Sep 27, 2010 at 3:27 PM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke <dcrooke(at)gmail(dot)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