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 !
> Gaetano Mendola <mendola@bigfoot.com> writes:
> > When you are experiencing this show us the result of this query:
> > select cmax, cmin, xmax, xmin, * from <table> where <your condition>;
>
> 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
>
>
