Skip site navigation (1) Skip section navigation (2)

Odd behaviour with redundant CREATE statement

From: Dave Crooke <dcrooke(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Odd behaviour with redundant CREATE statement
Date: 2010-09-27 18:50:43
Message-ID: AANLkTikzdRv6hMH7z2zaGuQMuLvmUy-Wq7uVHfepQqsz@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Gurjeet SinghDate: 2010-09-27 19:27:53
Subject: Re: Odd behaviour with redundant CREATE statement
Previous:From: Robert HaasDate: 2010-09-27 17:30:03
Subject: Re: how to enforce index sub-select over filter+seqscan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group