BUG #8382: Duplicate primary key

The following bug has been logged on the website:

Bug reference:      8382
Logged by:          Curd Reinert
Email address:      curd.reinert@ppi.de
PostgreSQL version: 9.0.5
Operating system:   Windows Server 2013
Description:

One of our customers encounters a problem because some tables contain
multiple rows with the same primary key. I've noticed a similar issue has
been reported in #3231 and #7760, but couldn't see how to proceed from
there.


The customer has about 1.000 installations of our software, each running
with its own Postgres instance, the vast majority running without any
problems. One system is causing most of the trouble, with the problem
occuring about every one or two weeks. Our software uses about 70 tables. In
two of these tables, multiple rows with the same primary key occure. These
tables are used to lock resources between threads. One of them, called
"cluster_ressource" has a constant set of rows which are updated frequently.
The other, called "sperre" is empty when the system is idle. Rows are
inserted to lock certain objects and removed when the locks are released.


The operating system is Windows Server 2003. apparently in a VMWare virtual
machine. The data folder is on D: which is a VMWare virtual disk. Compared
to the other systems, this one has quite a lot of traffic and therefore a
high probability of concurrent updates. According to our customer, there
were no problems with the same system as long as it was running with an
older version of our software which used Postgres 8.3. Of course there were
also changes in our software.


Once the problem is noticed, the customer fixes it by deleting both entries
and inserting a new one. In one case, a REINDEX was necessary to get the
system running again. Afterwards, the system is running smoothly for some
time, approximately one or two weeks. Then duplicate rows appear again.


I checked fsync, it has not been switched off.


Our application is written in Java, using a plain JDBC driver to access the
database. Of course we don't fiddle with the data model, indexes etc. during
runtime.


I do have a copy of the data directory with a duplicate row in
cluster_ressource, in case anyone wants to dive into it.


It seems that this not just one system. Most of their systems are nowadays
running on Postgres 9.0, and the majority without any problems. However, the
customer encountered similar problems with a handful of installations. Again
the problems started after updating to Postgres 9.0, and again these systems
have a higher workload than the average.


Please let me know if you need any further information. Thank you very much
in advance!