Re: Duplicate oid and primary key values

Поиск
Список
Период
Сортировка
От Richard Huxton
Тема Re: Duplicate oid and primary key values
Дата
Msg-id 200402102101.36351.dev@archonet.com
обсуждение исходный текст
Ответ на Duplicate oid and primary key values  (Jeff Bohmer <bohmer@visionlink.org>)
Список pgsql-general
On Tuesday 10 February 2004 17:10, Jeff Bohmer wrote:
> I have a table in a PG 7.4.1 database with 380 duplicate rows,
> including duplicate oid and primary key values.  Looking through our
> backups, the duplicates did not exist before Friday, 02/06/2004.  I'm
> assuming neither pg_dumpall nor restoring from a pg_dumpall file will
> eliminate such duplicates.  We upgraded from 7.3.4 to 7.4.1 on
> 02/02/2004.
>
> What can cause these duplicates?
>
> The server has had several system crashes over the past few days and weeks.

Hardware related? Or is it not clear yet?

> Below is my session with the DB showing an example of the duplicates,
> the table structure, and trigger functions.
>
> cos=> select oid, recordnumber from client where recordnumber = 10970;
>     oid   | recordnumber
> ---------+--------------
>   2427408 |        10970
> (1 row)
>
> cos=> select oid, recordnumber from client where recordnumber < 10971
> and recordnumber > 10969;
>     oid   | recordnumber
> ---------+--------------
>   2427408 |        10970
>   2427408 |        10970
> (2 rows)

In the absence of Tom or some other more knowledgable source, try these out.

SELECT xmin,cmin,xmax,cmax,tid,oid,* FROM ... to see if these are two versions
of the same row
Perhaps stick an EXPLAIN ANALYSE on the front of those and see if one is using
an index and the other not.

It might be a corrupted INDEX, in which case REINDEX should fix it.


PS - you probably want now() or CURRENT_DATE etc. in the trigger functions
rather than 'now'.
--
  Richard Huxton
  Archonet Ltd

В списке pgsql-general по дате отправления:

Предыдущее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: DB cache size strategies
Следующее
От: lists@natserv.com
Дата:
Сообщение: Re: Join query on 1M row table slow