Thread: Odd behaviour with redundant CREATE statement

Odd behaviour with redundant CREATE statement

From
Dave Crooke
Date:

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 - it's PG 8.4.4 64-bit on RHEL 5, installed with the postgresql.org YUM repository.

Stopping and restarting PG did not clear the issue. While this is going on, the database is otherwise responsive, e.g. to access with psql.

Is this "expected failure" considered a dangerous practice in PGSQL and should we add checks?

Does the hangup indicate a possible corruption problem with the DB?

Cheers
Dave



Re: Odd behaviour with redundant CREATE statement

From
Gurjeet Singh
Date:
On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke <dcrooke@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.

 
it's PG 8.4.4 64-bit on RHEL 5, installed with the postgresql.org YUM repository.

Stopping and restarting PG did not clear the issue. While this is going on, the database is otherwise responsive, e.g. to access with psql.

Also check if you have any prepared transactions waiting to be committed or rolled back.

select * from pg_prepared_xacts
 

Is this "expected failure" considered a dangerous practice in PGSQL and should we add checks?

Does the hangup indicate a possible corruption problem with the DB?

Very unlikely.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: Odd behaviour with redundant CREATE statement

From
Robert Haas
Date:
On Mon, Sep 27, 2010 at 3:27 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Mon, Sep 27, 2010 at 8:50 PM, Dave Crooke <dcrooke@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

Re: Odd behaviour with redundant CREATE statement

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