Re: Odd behaviour with redundant CREATE statement

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 <robertmhaas(at)gmail(dot)com> wrote:

> 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
>