Re: Duplicate indexes found in the postgres Database

"Chris White" <cjwhite(at)cisco(dot)com> writes:
> User was able to recreate the problem, but this time only on table got
> duplicated 'gui_config'. So here is the info you wanted:

> select ctid,xmin,xmax,oid,* from pg_class where relname = 'gui_config';
> ctid | xmin | xmax | oid | relname | reltype | relowner | relam |
> relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid |
> relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
> relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
> relhassubclass | relacl
> --------+------+------+-------+------------+---------+----------+-------+---
> ----------+----------+-----------+---------------+---------------+----------
> ---+-------------+---------+----------+-----------+-------------+----------+
> ----------+---------+------------+------------+-------------+---------------
> -+-----------------------
> (2,54) | 176 | 191 | 16560 | gui_config | 16561 | 101 | 0 |
> 16560 | 10 | 1000 | 0 | 0 | t |
> f | r | 2 | 0 | 0 | 0 |
> 0 | 0 | t | t | f | f |
> {=,aesop_gui=arwdRxt}
> (2,56) | 191 | 206 | 16560 | gui_config | 16561 | 101 | 0 |
> 16560 | 10 | 1000 | 0 | 0 | t |
> f | r | 2 | 0 | 0 | 0 |
> 0 | 0 | t | t | f | f |
> {=,aesop_gui=arwdRxt}
> (2 rows)

Hmm ... this looks much like the duplicate-tuple issues we were looking
at last month. Transaction 191 tried to update the row (though it's not
clear why, since none of the fields seem to have changed). But only one
of the two rows should be considered good --- either 191 committed or it
didn't. Something's gotten out of sync between the pg_class table file
and the pg_clog transaction commit status data. The only known ways for
that to happen involve system crashes just after a checkpoint, or pilot
error like trying to use a "tar" dump of an active database as a backup.

Is this a freshly-initdb'd database? Transaction number 191 seems very
small.

Do you have a procedure to reproduce the problem? Can you at least
describe what your user did?

regards, tom lane