Re: Getting rid of duplicate tables.

Jared Carr <jared(at)89glass(dot)com> writes:
> live=# select oid,ctid,cmax,xmax,cmin,xmin,* from pg_class where
> relname='order_to_do';
> oid | ctid | cmax | xmax | cmin | xmin |
> relname | relnamespace | reltype | relowner | relam | relfilenode |
> relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
> relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
> relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
> relhassubclass | relacl
> ----------+---------+------+----------+----------+----------+-------------+--------------+----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
> 11462032 | (27,2) | 0 | 2 | 2 | 46034931 |
> order_to_do | 2200 | 11462033 | 101 | 0 | 11462032
> | 506 | 59401 | 11462039 | 0 | t |
> f | r | 5 | 0 | 1 | 0
> | 0 | 0 | t | t | f |
> f |
> 11462032 | (27,43) | 2 | 46034931 | 46034931 | 8051642 |
> order_to_do | 2200 | 11462033 | 101 | 0 | 11462032
> | 453 | 53407 | 11462039 | 0 | t |
> f | r | 5 | 0 | 1 | 0
> | 0 | 0 | t | t | f |
> f |
> (2 rows)

Hmm. This does not look like an index problem --- it looks like a tuple
status problem. Apparently transaction 46034931 updated this row,
marking the tuple at (27,43) invalid and inserting a new version at
(27,2). That's fine and is not a unique-index violation by itself,
but we should only be able to see one of these versions of the row.
Clearly, both of them appear good since both are visible to your current
transaction. There is no way that both of these tuples should be
considered good; one or the other ought to be invalid from the point of
view of any observing transaction. I think somebody dropped a status
update somewhere. Have you had any system crashes recently? What kind
of disk hardware is this running on --- is it IDE, and if so, do you
have write caching properly disabled?

To really dig into this, we need to get a physical dump of these rows.
The best tool for this is pg_filedump, which you can get from
http://sources.redhat.com/rhdb/utilities.html. You can run it with
something like
pg_filedump -i -f -R 27 $PGDATA/base/nnn/1259
where nnn is the OID of the database with the problem (look in
pg_database to determine this). 1259 is the known OID of pg_class.
Both of the interesting tuples are on the same page 27, so this should
get the data we need.

Also, please show the output of "ls -l $PGDATA/pg_clog". If the
relevant segments of clog are still around, we might want to look at
what it says about the commit states of these two transactions.

regards, tom lane