Broken index? - Mailing list pgsql-admin

Hi,

I am having a problem with duplicates appearing in a unique index and I
have no idea why. The database in question has a high number of inserts and
updates to a table (around 5000-10000 per hour) and occasionally duplicates
slip through. To provide some background, I have listed the properties of
the table and the index in question:

player_stats=> \d player
                                Table "player"
  Attribute   |     Type     |                    Modifier

--------------+--------------+-------------------------------------------------
 id           | integer      | not null default
nextval('player_serial'::text)
 name         | varchar(50)  | not null
 password     | varchar(50)  |
 icqnumber    | varchar(20)  |
 emailaddress | varchar(255) |
 flatname     | varchar(50)  |
Indices: player_flatname_idx,
         player_flatname_unique_idx,
         player_id_idx,
         player_name_key,
         player_pkey

player_stats=> \d player_name_key
 Index "player_name_key"
 Attribute |    Type
-----------+-------------
 name      | varchar(50)
unique btree

The attribute that appears to sometimes allow duplicates to be inserted is
'name' and only 'player_name_key' operates on this attribute.

I can't say how often these duplicates slip through, but it seems to only
happen *very* rarely, but is obviously completely mangling my data. Not
only that, but VACUUM ANALYZE is taking *days* to complete and I can only
assume this is due to these duplicates. The 'player' table contains around
180000 records, so if this fall of in performance when running vacuum can
be associated with this, I'd appreciate some feedback.

Anyway, I'd just like to know if anyone else has had this problem or if it
is a known bug. All the database operations are handled through the JDBC
driver and transactions are used to bundle these operations in to sets of
20 (maybe a potential problem here?).

I'd *really* appreciate some feedback as it is becoming a very annoying
problem. If you'd like to check the site out where it is used, go to
http://www.fragmeter.com.

Cheers!
--
Paul Green
Programmer

Jippii Midlands
85 London Road,
Leicester,
England,
LE2 0PF.
tel: 0116 2230662
fax: 0116 2221305

Please visit www.businessjippii.co.uk to view our latest Internet and
Telecommunication Products and Services. We offer it all!

Want to order a free dial up account online? Go to www.dialjippii.co.uk.

Want a quick and easy way to get a domain name? Order online at
www.domainsjippii.co.uk.

For the latest ringtones, logos and fun games go to the Jippii portal at
www.jippii.co.uk.


NOTICE: This e-mail is strictly confidential and is intended solely for
the person or organisation to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this communication in error, please advise
us by e-mail and delete the file from your system. Whilst all efforts
are made to safeguard Inbound and Outbound e-mails, Webleicester Ltd.,
its subsidiaries or associates cannot guarantee that attachments are
Virus-free or compatible with your system and does not accept any
liability in respect of viruses or computer problems experienced. Any
views expressed in this message are those of the individual sender,
except where specifically stated to be the view of Webleicester Ltd.,
its subsidiaries or associates.