Re: Duplicate values found when reindexing unique index

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> Here's the system column data you requested.
>
>> id | ctid | xmin | xmax | cmin | cmax
>> -----------+--------------+------+------+------+------
>> 151341072 | (1508573,11) | 2 | 0 | 19 | 0
>> 151341072 | (1818219,11) | 2 | 0 | 19 | 0
>> (2 rows)
>
> I wonder whether it's just a coincidence that these have the same offset
> number...

I can't imagine any Postgres bug which would depend on the offsets being the
same. But what I could imagine is filesystem corruption which copied the block
to someplace else in the table or possibly has even mapped the same block into
two different places in the table.

Can you unmount the filesystem and run "fsck -v -n" on it? Is this the only
duplicate record in the table? Are there any other records on either of these
blocks?

To answer the latter question I found a handy trick of converting the tid to a
"point" so I could refer to the block or offset. In 8.3 this looks like:

select ctid from foo where (ctid::text::point)[0] = 0;

But in 8.2 iirc you had to call the tid output function explicitly because
there was no cast to text.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!