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

Re: Duplicate values found when reindexing unique index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mason Hale" <masonhale(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Duplicate values found when reindexing unique index
Date: 2007-12-31 02:54:06
Message-ID: 19423.1199069646@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Mason Hale" <masonhale(at)gmail(dot)com> writes:
> I found a single pair of rows that were duplicated. Interestingly it was not
> just the guid and feed_id that were duplicated but all columns were
> indentical, including the primary key, except an update_at column which is
> automatically populated via a trigger (BEFORE UPDATE on entry FOR EACH ROW).

> The duplicate data included a created_at column which defaults to now() --
> that the two duplicate rows have exactly the same values strongly hints to
> me that the duplicates were created during the same transaction.

Hmm, what sounds more likely to me is that both of those rows are
updated versions of the same original row.

> 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...

The fact that both rows have been frozen (xmin=2) means we can't really
tell whether they originated in the same transaction or not.

Can you show us all the triggers on this table?  Also, it would be real
interesting to see "pg_filedump -i -f" output for the two blocks in
question (1508573 and 1818219) --- see http://sources.redhat.com/rhdb/
to get a copy of pg_filedump.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2007-12-31 03:56:48
Subject: Re: BUG #3841: core dump in uuid-ossp
Previous:From: Mason HaleDate: 2007-12-31 02:41:31
Subject: Re: Duplicate values found when reindexing unique index

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