Обсуждение: Duplicate indexes found in the postgres Database
While interrogating the table information for our database, I notice that
some tables (i.e. gui_config, vm_mailbox, vm_message) have duplicate
entries. Does anybody know why this would occur and is it a problem?
Attached are appropriate outputs. Queries on the tables which are duplicated
in the entries appear to work normally.
We are running version 7.2.1
aesop=# \dt
List of relations
Name | Type | Owner
--------------+-------+-----------
gui_config | table | aesop_gui
gui_config | table | aesop_gui
gui_prefs | table | aesop_gui
vm_config | table | voicemail
vm_dbversion | table | voicemail
vm_emailjob | table | voicemail
vm_greeting | table | voicemail
vm_mailbox | table | voicemail
vm_mailbox | table | voicemail
vm_mbxusers | table | voicemail
vm_message | table | voicemail
vm_message | table | voicemail
vm_usermsg | table | voicemail
(13 rows)
aesop=# select * from pg_tables ;
tablename | tableowner | hasindexes | hasrules | hastriggers
----------------+------------+------------+----------+-------------
pg_type | postgres | t | f | f
pg_attribute | postgres | t | f | f
pg_class | postgres | t | f | f
pg_group | postgres | t | f | f
pg_database | postgres | t | f | f
pg_xactlock | postgres | f | f | f
pg_inherits | postgres | t | f | f
pg_index | postgres | t | f | f
pg_operator | postgres | t | f | f
pg_opclass | postgres | t | f | f
pg_am | postgres | t | f | f
pg_amop | postgres | t | f | f
pg_amproc | postgres | t | f | f
pg_language | postgres | t | f | f
pg_largeobject | postgres | t | f | f
pg_aggregate | postgres | t | f | f
pg_trigger | postgres | t | f | f
pg_listener | postgres | f | f | f
pg_shadow | postgres | t | f | t
pg_attrdef | postgres | t | f | f
pg_description | postgres | t | f | f
gui_config | aesop_gui | t | f | f
pg_proc | postgres | t | f | f
pg_relcheck | postgres | t | f | f
gui_prefs | aesop_gui | t | f | f
gui_config | aesop_gui | t | f | f
pg_rewrite | postgres | t | f | f
vm_config | voicemail | t | f | f
vm_dbversion | voicemail | t | f | f
pg_statistic | postgres | t | f | f
vm_mbxusers | voicemail | t | f | t
vm_greeting | voicemail | t | f | t
vm_mailbox | voicemail | t | f | t
vm_message | voicemail | t | f | t
vm_mailbox | voicemail | t | f | t
vm_usermsg | voicemail | t | f | t
vm_message | voicemail | t | f | t
vm_emailjob | voicemail | t | f | f
(38 rows)
aesop=# select * from pg_stat_user_tables
aesop-# \g
relid | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch
| n_tup_ins | n_tup_upd | n_tup_del
-------+--------------+----------+--------------+----------+---------------+
-----------+-----------+-----------
16560 | gui_config | 0 | 0 | 0 | 0
| 0 | 0 | 0
16563 | gui_prefs | 0 | 0
| | | 0 | 0 | 0
16566 | vm_config | 0 | 0 | 0 | 0
| 0 | 0 | 0
16569 | vm_dbversion | 0 | 0 | 0 | 0
| 0 | 0 | 0
16572 | vm_mailbox | 0 | 0 | 0 | 0
| 0 | 0 | 0
16575 | vm_mbxusers | 0 | 0 | 0 | 0
| 0 | 0 | 0
16584 | vm_greeting | 0 | 0 | 0 | 0
| 0 | 0 | 0
16593 | vm_message | 0 | 0 | 0 | 0
| 0 | 0 | 0
16602 | vm_usermsg | 0 | 0 | 0 | 0
| 0 | 0 | 0
16619 | vm_emailjob | 0 | 0 | 0 | 0
| 0 | 0 | 0
(10 rows)
aesop=# select * from pg_stat_user_indexes ;
relid | indexrelid | relname | indexrelname | idx_scan |
idx_tup_read | idx_tup_fetch
-------+------------+--------------+-------------------+----------+---------
-----+---------------
16560 | 16562 | gui_config | gui_config_pkey | 0
| 0 | 0
16560 | 16562 | gui_config | gui_config_pkey | 0
| 0 | 0
16566 | 16568 | vm_config | vm_config_pkey | 0
| 0 | 0
16569 | 16571 | vm_dbversion | vm_dbversion_pkey | 0
| 0 | 0
16572 | 16574 | vm_mailbox | vm_mailbox_pkey | 0
| 0 | 0
16572 | 16574 | vm_mailbox | vm_mailbox_pkey | 0
| 0 | 0
16575 | 16577 | vm_mbxusers | vm_mbxusers_pkey | 0
| 0 | 0
16584 | 16586 | vm_greeting | vm_greeting_pkey | 0
| 0 | 0
16593 | 16595 | vm_message | vm_message_pkey | 0
| 0 | 0
16593 | 16595 | vm_message | vm_message_pkey | 0
| 0 | 0
16602 | 16604 | vm_usermsg | vm_usermsg_pkey | 0
| 0 | 0
16619 | 16624 | vm_emailjob | vm_emailjob_pkey | 0
| 0 | 0
(12 rows)
"Chris White" <cjwhite@cisco.com> writes:
> While interrogating the table information for our database, I notice that
> some tables (i.e. gui_config, vm_mailbox, vm_message) have duplicate
> entries. Does anybody know why this would occur and is it a problem?
Probably it is :-(. You'll need to dig into the underlying catalogs
(everything you showed us are views, and joined ones at that) to see
what the apparent duplicates are coming from.
Way back when, it used to be possible for this symptom to arise from
multiple pg_shadow entries with the same usesysid; but that should not
happen now that there's a unique index on usesysid.
Have you had any system crashes recently?
regards, tom lane
How do I look at the catalogs and which ones do I need to look at?
No we haven't seen any system crashes, but people have reported that the
tables that are duplicated are possibly missing some data.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 01, 2003 9:31 AM
To: Chris White
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate indexes found in the postgres Database
"Chris White" <cjwhite@cisco.com> writes:
> While interrogating the table information for our database, I notice that
> some tables (i.e. gui_config, vm_mailbox, vm_message) have duplicate
> entries. Does anybody know why this would occur and is it a problem?
Probably it is :-(. You'll need to dig into the underlying catalogs
(everything you showed us are views, and joined ones at that) to see
what the apparent duplicates are coming from.
Way back when, it used to be possible for this symptom to arise from
multiple pg_shadow entries with the same usesysid; but that should not
happen now that there's a unique index on usesysid.
Have you had any system crashes recently?
regards, tom lane
"Chris White" <cjwhite@cisco.com> writes:
> How do I look at the catalogs and which ones do I need to look at?
Now that I look at it, pg_tables is not a join in 7.2, but just a
straight select from pg_class. So the problem is definitely in
pg_class. Let's see the results of
select ctid,xmin,xmax,oid,* from pg_class where relname = 'vm_message';
and similarly for the other duplicates.
> No we haven't seen any system crashes, but people have reported that the
> tables that are duplicated are possibly missing some data.
Hm. Trying to avoid theorizing in advance of the data...
regards, tom lane
Tom,
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)
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 01, 2003 12:42 PM
To: Chris White
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate indexes found in the postgres Database
"Chris White" <cjwhite@cisco.com> writes:
> How do I look at the catalogs and which ones do I need to look at?
Now that I look at it, pg_tables is not a join in 7.2, but just a
straight select from pg_class. So the problem is definitely in
pg_class. Let's see the results of
select ctid,xmin,xmax,oid,* from pg_class where relname = 'vm_message';
and similarly for the other duplicates.
> No we haven't seen any system crashes, but people have reported that the
> tables that are duplicated are possibly missing some data.
Hm. Trying to avoid theorizing in advance of the data...
regards, tom lane
"Chris White" <cjwhite@cisco.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
Sorry, the owner of the database decided to reload the database and we have
lost the duplicate entries. If it happens again I will send you the info.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 01, 2003 12:42 PM
To: Chris White
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate indexes found in the postgres Database
"Chris White" <cjwhite@cisco.com> writes:
> How do I look at the catalogs and which ones do I need to look at?
Now that I look at it, pg_tables is not a join in 7.2, but just a
straight select from pg_class. So the problem is definitely in
pg_class. Let's see the results of
select ctid,xmin,xmax,oid,* from pg_class where relname = 'vm_message';
and similarly for the other duplicates.
> No we haven't seen any system crashes, but people have reported that the
> tables that are duplicated are possibly missing some data.
Hm. Trying to avoid theorizing in advance of the data...
regards, tom lane
Tom,
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)
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 01, 2003 12:42 PM
To: Chris White
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate indexes found in the postgres Database
"Chris White" <cjwhite@cisco.com> writes:
> How do I look at the catalogs and which ones do I need to look at?
Now that I look at it, pg_tables is not a join in 7.2, but just a
straight select from pg_class. So the problem is definitely in
pg_class. Let's see the results of
select ctid,xmin,xmax,oid,* from pg_class where relname = 'vm_message';
and similarly for the other duplicates.
> No we haven't seen any system crashes, but people have reported that the
> tables that are duplicated are possibly missing some data.
Hm. Trying to avoid theorizing in advance of the data...
regards, tom lane