Postgres Pro
Downloads
Home   >   mailing lists

Thread: Duplicate primary key and also the OID !!!!

Duplicate primary key and also the OID !!!!

From
"Gaetano Mendola"
Date:
Hi all,
I'm running postgres 7.3.2 and in a table with a primary key
I got that primary key duplicated   :-(

I found this because each time that I try tu update the field
with id_user_traffic = 11348 I obtain an error about key
duplicated.


SELECT oid, id_user_traffic, start_date, end_date
FROM user_traffic
WHERE id_user = 6738;


   oid   | id_user_traffic | start_date |  end_date
---------+-----------------+------------+------------
 1213776 |           11348 | 2003-03-07 | 2003-04-07
 1213776 |           11348 | 2003-03-07 | 2003-04-07
 1213776 |           11348 | 2003-03-07 | 2003-04-07
 1213776 |           11348 | 2003-03-07 | 2003-04-07
 1107090 |            9675 | 2003-01-17 | 2003-02-17
 1213779 |           11349 | 2003-04-07 | 2003-05-07
 1213782 |           11350 | 2003-05-07 | 2003-06-07


How is it possible have the oid and the id_user_traffic (
serial primary key ) duplicated ? Any idea ?

Re: Duplicate primary key and also the OID !!!!

From
Tom Lane
Date:
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> How is it possible have the oid and the id_user_traffic (
> serial primary key ) duplicated ? Any idea ?

Have you had any power failures lately?  Tried to copy a running
database via "tar" or "cp"?  Anything interesting like that?

There are ways for multiple versions of a row to all appear good,
if something happens that breaks the synchronization between datafiles
and pg_clog.  AFAIK these all reduce to DBA error (copying a live
database) or hardware misfeasance (disk drive claiming write complete
before it really is, and then losing the data in a power failure).

Also, what Postgres version are you using?  Awhile back there was a bug
that allowed a sequence to appear to go backward in a crash.

            regards, tom lane

Re: Duplicate primary key and also the OID !!!!

From
"Mendola Gaetano"
Date:
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> Have you had any power failures lately?  Tried to copy a running
> database via "tar" or "cp"?  Anything interesting like that?

No power failure ( I shall investigate about last shutdown ),
a copy was performed with the DB running but the copy was not
used and the problem was on the original.

> Also, what Postgres version are you using?  Awhile back there was a bug
> that allowed a sequence to appear to go backward in a crash.

Postgres 7.3.2



PS: Why your reply not appear on the News server ?


Gaetano.

Re: Duplicate primary key and also the OID !!!!

From
"Gaetano Mendola"
Date:
"CoL" <col@mportal.hu> wrote in message news:b6sfho$1b7d$1@news.hub.org...
> begin;
> create table temp as select distinct * from user_traffic WHERE id_user =
> 6738;
> delete from user_traffic WHERE id_user = 6738;
> insert into user_traffic select * from temp;
> drop table temp;
> commit;


Is not so easy like this...
After that delete some other records in
other tables will be deleted ( cascade ).

The solution was doing the dump, modify
by hand the dump, reinit the DB ........


Thank you anyway.

Gaetano

Re: Duplicate primary key and also the OID !!!!

From
CoL
Date:
begin;
create table temp as select distinct * from user_traffic WHERE id_user =
6738;
delete from user_traffic WHERE id_user = 6738;
insert into user_traffic select * from temp;
drop table temp;
commit;


C.

Gaetano Mendola wrote, On 4/6/2003 2:17 PM:
> Hi all,
> I'm running postgres 7.3.2 and in a table with a primary key
> I got that primary key duplicated   :-(
>
> I found this because each time that I try tu update the field
> with id_user_traffic = 11348 I obtain an error about key
> duplicated.
>
>
> SELECT oid, id_user_traffic, start_date, end_date
> FROM user_traffic
> WHERE id_user = 6738;
>
>
>    oid   | id_user_traffic | start_date |  end_date
> ---------+-----------------+------------+------------
>  1213776 |           11348 | 2003-03-07 | 2003-04-07
>  1213776 |           11348 | 2003-03-07 | 2003-04-07
>  1213776 |           11348 | 2003-03-07 | 2003-04-07
>  1213776 |           11348 | 2003-03-07 | 2003-04-07
>  1107090 |            9675 | 2003-01-17 | 2003-02-17
>  1213779 |           11349 | 2003-04-07 | 2003-05-07
>  1213782 |           11350 | 2003-05-07 | 2003-06-07
>
>
> How is it possible have the oid and the id_user_traffic (
> serial primary key ) duplicated ? Any idea ?
>
>
>
>
>
>
>