Thread: duplicate key

Hello eveybody,

We are trying to REINDEX a database, but we encounter some strange problem.
We have the message "Cannot insert a duplicate key into index
synd_article_pkey".
It appears that we have duplicate key in the base, with exactly the
same informations.
We then did the above query:
"select id_synd_art from synd_article group by id_synd_art having
count(id_synd_art)>1".
id_synd_pkey is the primary key on id_synd_art, a not null field with
default nextval('"synd_article_id_seq"'::text)

By example, we can have the above informations, when we do a select
on one of the key given by the previous query :
11470 | actualite_generale/une/ | 2002-08-01 21:00:00-01 | La Une |
020802132619bsbfe0
11470 | actualite_generale/une/ | 2002-08-01 21:00:00-01 | La Une |
020802132619bsbfe0

We have exactly the same things in the base.
Actually, we have 221 duplicate keys in the table (65000 records)

We can't do REINDEX on the base and VACUUM. So the base is growing
and growing and so...


Any idea about this problem ?


Thanx,
Pascal.

pascal =?iso-8859-1?Q?b=E9rest?= <lists@magnetophone.com> writes:
> We have exactly the same things in the base.
> Actually, we have 221 duplicate keys in the table (65000 records)

Seems like a bug, but there's no chance of diagnosing it without more
info.  For starters, what Postgres version are you running?  Are all of
the records with duplicate keys exact duplicates in all other fields
too?  Also, it would be useful to see the ctid, xmin, xmax, cmin,
and cmax values for some of the sets of duplicate rows.

            regards, tom lane

At 10:53 -0500 13/01/2003, Tom Lane wrote:
>pascal =?iso-8859-1?Q?b=E9rest?= <lists@magnetophone.com> writes:
>  > We have exactly the same things in the base.
>  > Actually, we have 221 duplicate keys in the table (65000 records)
>
>Seems like a bug, but there's no chance of diagnosing it without more
>info.  For starters, what Postgres version are you running?

We are using postgres 7.2.3

>  Are all of
>the records with duplicate keys exact duplicates in all other fields
>too?

Yes. When we print all the fields, we have exactly the same entries.
So we can't delete one the two entries. That's quite strange.

>Also, it would be useful to see the ctid, xmin, xmax, cmin,
>and cmax values for some of the sets of duplicate rows.

Could you explain where we can have these informations ?


We also delete the duplicate keys, but we still have some of them.
We delete again and again, and we still have some of them. Finally,
we delete them manually, and it was possible to REINDEX the table.

This problem seams to be important, as we can't do REINDEX or VACUUM
on the database. In decembre 20th, we had have 1.8 Gb database and
now, it's about 5.0 Gb database. Our server is running slowly now.

We didn't have this problem with postgres 7.2.1.


Thanx for your help,
Pascal.

pascal =?iso-8859-1?Q?b=E9rest?=  <lists@magnetophone.com> writes:
>> Also, it would be useful to see the ctid, xmin, xmax, cmin,
>> and cmax values for some of the sets of duplicate rows.

> Could you explain where we can have these informations ?

Select 'em.  They're system columns, like OID.

> We also delete the duplicate keys, but we still have some of them.
> We delete again and again, and we still have some of them. Finally,
> we delete them manually, and it was possible to REINDEX the table.

So in other words, the evidence is now all gone?  Oh well.

            regards, tom lane

>pascal =?iso-8859-1?Q?b=E9rest?=  <lists@magnetophone.com> writes:
>  >> Also, it would be useful to see the ctid, xmin, xmax, cmin,
>  >> and cmax values for some of the sets of duplicate rows.
>
>>  Could you explain where we can have these informations ?
>
>Select 'em.  They're system columns, like OID.
>
>>  We also delete the duplicate keys, but we still have some of them.
>  > We delete again and again, and we still have some of them. Finally,
>  > we delete them manually, and it was possible to REINDEX the table.
>
>So in other words, the evidence is now all gone?  Oh well.

Yes, it's deleted. But we still have them on our production server.
We can restore it on our preproduction server, from a tar of the base.
Could you explain exactle where we can find tthe ctid, cmin, xmin,
xmax values ?
We will redo the test tomorrow.


Thanx very much.


Pascal.

I got same problem with Postgres 7.2.3. I kept a tar file for the whole data directory.

Wenzhe

At 11:24 AM 1/13/2003 -0500, Tom Lane wrote:
>pascal =?iso-8859-1?Q?b=E9rest?=  <lists@magnetophone.com> writes:
>>> Also, it would be useful to see the ctid, xmin, xmax, cmin,
>>> and cmax values for some of the sets of duplicate rows.
>
>> Could you explain where we can have these informations ?
>
>Select 'em.  They're system columns, like OID.
>
>> We also delete the duplicate keys, but we still have some of them.
>> We delete again and again, and we still have some of them. Finally,
>> we delete them manually, and it was possible to REINDEX the table.
>
>So in other words, the evidence is now all gone?  Oh well.
>
>                        regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)