Re: BUG #1242: Major bug in pgSQL

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(at)bigfoot(dot)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
>
>