Skip site navigation (1) Skip section navigation (2)

Re: Duplicate values found when reindexing unique index

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Mason Hale" <masonhale(at)gmail(dot)com>, <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Duplicate values found when reindexing unique index
Date: 2007-12-31 06:12:19
Message-ID: 87y7bbz930.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-bugs
"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!

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-12-31 06:27:51
Subject: Re: Duplicate values found when reindexing unique index
Previous:From: Tom LaneDate: 2007-12-31 06:08:35
Subject: Re: Duplicate values found when reindexing unique index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group