Обсуждение: Duplicate primary key and also the OID !!!!
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 ?
"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
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.
"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
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 ? > > > > > > >