The following bug has been logged online:

Bug reference: 1242
Logged by:

Email address: freecontact (AT) freenet (DOT) de

PostgreSQL version: 7.4.3

Operating system: Linux Debian 3.1

Description: Major bug in pgSQL

Details:

Hi,

apparently we have found a critical bug in pgSQL. At the present moment we
can not reproduce it, but here is the description:

Foreword: we have a very high loaded pgSQL-based application with thousands
of simultaneous users and overall 20 high performance server. Another
nice-to-know thing to mention - we have very large transaction blocks, with
some hunderds of SQL statements in each block.

Problem: in some cases we experience the following problem - we have found
in the database some _absolutely_ identical rows, despite the fact, that we
have defined some unique (!) indexes on some of the fields and even primary
(!) keys, we can see, that the rows are _exactly_ the same. In some cases we
have seen up to 7 absolutely identical rows, with the same primary keys and
the same unique indexed fields.

This problem is in our eyes absolutely critical. We are even considering
right now the change to another DBMS , even though we were in the past
always very satisfied with pgSQL...

We are looking forward to hear, if there are any known solutions for this
kind of problem. Thank you very much!


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

PostgreSQL Bugs List wrote:


I had the same experiences in 7.3 release and I realized that this is due to
some interaction between vacuum, reindex and update on the same table. See
this posts:

http://archives.postgresql.org/pgsql...5/msg00060.php
http://www.mail-archive.com/pgsql-ad.../msg09025.html
http://archives.postgresql.org/pgsql...4/msg00407.php
http://archives.postgresql.org/pgsql...1/msg00129.php


unfortunatelly I never was able to reproduce it.

When you are experiencing this show us the result of this query:

select cmax, cmin, xmax, xmin, * from <table> where <your condition>;

where <your condition> is a filter in order to obtain the rows wit the
primary key duplicated.

However I'm sure that you don't have two row with duplicated primary
key but two version of the same row, the result however is the same.

Are you reindexing your tables regulary ?



Regards
Gaetano Mendola






---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Gaetano Mendola <mendola (AT) bigfoot (DOT) com> writes:
Also, please, the ctid and oid columns (but leave out oid if you made
the table WITHOUT OIDS).

Also, if the condition is one that will normally use an index, try
the same query with and without "set enable_indexscan = off". It
could be that a corrupted index would cause the query to visit the
same rows multiple times (or miss rows!).

It might be a good idea to REINDEX the primary-key index on the table,
but I would counsel not doing so until we have more data on what's
happening. If the problem is index corruption then REINDEX would
destroy all the evidence ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thank you very much for a quick answer. Here is our situation:

Query:
select cmax, cmin, xmax, xmin, ctid, oid, *
from unitcontainer where unitcontainername = 'Track';

Result:
oid id_unitcontainer id_basecomb id_unitcontainertype id_userinstance
unitcontainername
16.995.030 10.464 1.009 1 1.063 'Track'
cmax 102, cmin 126761922, xmax 126761922, xmin 121168748, ctid (84,107)

so, only ONE row. "unitcontainername" has an unique index ("index_12"). Here
is the result for the following query:

Query:
reindex index index_12;

Result:
ERROR: could not create unique index
DETAIL: Table contains duplicated values.

Unable to reindex as well .

Now comes the most interesting part.

Query:
select cmax, cmin, xmax, xmin, ctid, oid, * from ds_unitcontainer where oid
= 16995030

Result:
oid id_unitcontainer id_unitcontainertype id_userinstance unitcontainername
16.995.030 10.464 1 1.063 'Track'
cmax 121457766, cmin 21, xmax 121168748, xmin 121168748, ctid (83,79)

16.995.030 10.464 1 1.063 'Track'
cmax 102, cmin 126761922, xmax 126761922, xmin 121168748, ctid (84,107)

Now we get TWO rows! The most astonishing thing about it - there are whole 3
uniqueness violations:
1) primary key violation for "id_unitcontainer"
2) unique index violation for "unitcontainername"
3) the most "crazy" one - for "oid" !

Please let us know, what we can do to fix this kind of problems, reindexing
seems to be impossible too ... Thank you very much !



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match