Обсуждение: Duplicate key
I'm running Postgres 7.3.3 on a Linux Box I know that seems impossible, that I can not replicate the bug but today without hardware failure, power down etc etc I had a duplicate primary key + a duplicate unique index on one table. I already had this "kind" of problem in another table and I solved the problem not reindexing anymore that table, now this table have 12 index and is eavely updated/inserted so I must reindex this table once in a day. Are we sure at 100% that the is not a reindex fault ? What can I do next time in order to discover this nusty bug ? BTW, I don't know if you got my past email about a strange core by the postmaster that disappeared after that I change the scheduling of a vacuum ( not anymore togheter with the select that was causing the core ); well after almost 2 month I still not having another core. Regards Gaetano Mendola
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> I'm running Postgres 7.3.3 on a Linux Box
> I know that seems impossible,
> that I can not replicate the bug but
> today without hardware failure, power down etc etc
> I had a duplicate primary key + a duplicate unique index
> on one table. I already had this "kind" of problem in another
> table and I solved the problem not reindexing anymore that
> table, now this table have 12 index and is eavely updated/inserted
> so I must reindex this table once in a day.
What command have you been issuing, exactly? Also, let's see psql's \d
output for the table that's now got duplicate indexes, plus the pg_class
rows for the duplicate indexes.
regards, tom lane
Can anyone point me to any stats on how well postgresql performs on various OS's? I'm particularily interested in BSD variations and Linux. -- Until later: Geoffrey esoteric@3times25.net The latest, most widespread virus? Microsoft end user agreement. Think about it...
> Can anyone point me to any stats on how well postgresql performs on > various OS's? I'm particularily interested in BSD variations and Linux. There was recently a thread named "The results of my PostgreSQL/filesystem performance" in performance list exactly about this topic. The test results are here http://www.potentialtech.com/wmoran/postgresql.php Kaarel
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
> > I'm running Postgres 7.3.3 on a Linux Box
> > I know that seems impossible,
> > that I can not replicate the bug but
> > today without hardware failure, power down etc etc
> > I had a duplicate primary key + a duplicate unique index
> > on one table. I already had this "kind" of problem in another
> > table and I solved the problem not reindexing anymore that
> > table, now this table have 12 index and is eavely updated/inserted
> > so I must reindex this table once in a day.
>
> What command have you been issuing, exactly? Also, let's see psql's \d
> output for the table that's now got duplicate indexes, plus the pg_class
> rows for the duplicate indexes.
The table is used by hundred of clients so I don't know exactly the sequence
of
command that was causing the problem; the only think that I can say is that
I use
the table ua_user_data_exp like a "materialized view" so are only trigger on
other
tables that are modifing the table.
Here your request:
db=# \d ua_user_data_exp
Table "public.ua_user_data_exp"
Column | Type | Modifiers
------------------+--------------------------+-----------
id_user | integer |
id_provider | integer |
login | character varying |
password | character varying(20) |
lastname | character varying(64) |
firstname | character varying(64) |
email | character varying(64) |
phone | character varying(64) |
fax | character varying(64) |
street_address | character varying(64) |
zipcode | character varying(10) |
city | character varying(64) |
country | text |
country_descr | text |
occupation | text |
occupation_descr | text |
company | character varying(64) |
os_type | text |
os_type_descr | text |
orbital_ptns | character varying |
card | text |
card_descr | text |
class | character varying(20) |
class_descr | character varying(64) |
creation_date | timestamp with time zone |
mac_address | text |
pid | integer |
status | text |
status_descr | text |
bytes_traffic | integer |
ip_address | text |
provider | character varying(64) |
platform | character varying(20) |
transponder | character varying(50) |
active | text |
stickers | text |
contracts | text |
connected | text |
connections | integer |
login_time | text |
total_traffic | bigint |
Indexes: ua_user_data_exp_id_user_key unique btree (id_user),
ua_user_data_exp_login_key unique btree (login),
exp_card btree (card),
exp_ci_email btree (lower(email)),
exp_ci_lastname btree (lower(lastname)),
exp_ci_login btree (lower(login)),
exp_country btree (country),
exp_email btree (email),
exp_id_provider btree (id_provider),
exp_lastname btree (lastname),
exp_mac_address btree (lower(mac_address)),
exp_mac_address_normal btree (mac_address),
exp_orbital_ptns btree (orbital_ptns),
exp_os_type btree (os_type),
exp_pid btree (pid),
exp_provider btree (provider) WHERE ((status = 'Active'::text) OR
(status = 'Suspended'::text)),
exp_status btree (status),
exp_stickers btree (stickers)
db=# select * from pg_class where relname = 'ua_user_data_exp_id_user_key';
-[ RECORD 1 ]--+-----------------------------
relname | ua_user_data_exp_id_user_key
relnamespace | 2200
reltype | 0
relowner | 100
relam | 403
relfilenode | 3005981
relpages | 52
reltuples | 11566
reltoastrelid | 0
reltoastidxid | 0
relhasindex | f
relisshared | f
relkind | i
relnatts | 1
relchecks | 0
reltriggers | 0
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhassubclass | f
relacl |
db=# select * from pg_class where relname = 'ua_user_data_exp_login_key';
-[ RECORD 1 ]--+---------------------------
relname | ua_user_data_exp_login_key
relnamespace | 2200
reltype | 0
relowner | 100
relam | 403
relfilenode | 3005982
relpages | 81
reltuples | 11566
reltoastrelid | 0
reltoastidxid | 0
relhasindex | f
relisshared | f
relkind | i
relnatts | 1
relchecks | 0
reltriggers | 0
relukeys | 0
relfkeys | 0
relrefs | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhassubclass | f
relacl |
I had one row duplicated with the same login and the same id_user,
was failing was the update of that row complaining about the duplicated
key.
Regards
Gaetano Mendola
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> I had one row duplicated with the same login and the same id_user,
> was failing was the update of that row complaining about the duplicated
> key.
Oh. Your report was quite unclear; I thought you were saying that
REINDEX had somehow built two copies of the same index.
Is the row actually duplicated, or has it just managed to acquire two
pointers to itself in the indexes? You could check by seeing whether
the two apparent instances have the same or different ctid values
(ctid is a system column not shown unless you ask for it, like oid).
If they are different ctids, it would be useful to see whether they have
the same or different oid,xmin,cmin,xmax,cmax (more system columns).
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> wrote: > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > I had one row duplicated with the same login and the same id_user, > > was failing was the update of that row complaining about the duplicated > > key. > > Oh. Your report was quite unclear; I thought you were saying that > REINDEX had somehow built two copies of the same index. I don't know if this time was a reindex or not, but for the other table I'm sure, I had several times the duplicate key and removing the reindex the problem disapperead. > Is the row actually duplicated, or has it just managed to acquire two > pointers to itself in the indexes? You could check by seeing whether > the two apparent instances have the same or different ctid values > (ctid is a system column not shown unless you ask for it, like oid). > If they are different ctids, it would be useful to see whether they have > the same or different oid,xmin,cmin,xmax,cmax (more system columns). I already fixed the problem so I can not perform the select, before remove the two rows I selected the oid and it was duplicated too. Next time I will select the suggested columns. Regards Gaetano Mendola
just for add informations on the problem:
these are the logs received by the reindex and by
vacuum full on that table ( before to delete the
duplicated rows):
===== REINDEX =======
Start Reindex table ua_user_data_exp at Fri Sep 5 07:12:26 CEST 2003
ERROR: Cannot create unique index. Table contains non-unique values
===== VACUUM FULL ======
Start Vacuum table ua_user_data_exp at Fri Sep 5 07:13:11 CEST 2003
INFO: --Relation public.ua_user_data_exp--
INFO: Pages 890: Changed 119, reaped 531, Empty 0, New 0; Tup 11503: Vac
223, Keep/VTL 206/206, UnUsed 1674, MinLen 44, MaxLen
696; Re-using: Free/Avail. Space 1007004/934048; EndEmpty/Avail. Pages
1/421.
CPU 0.08s/0.01u sec elapsed 0.08 sec.
INFO: Index ua_user_data_exp_id_user_key: Pages 52; Tuples 11505: Deleted
223.
CPU 0.01s/0.01u sec elapsed 0.03 sec.
WARNING: Index ua_user_data_exp_id_user_key: NUMBER OF INDEX' TUPLES
(11505) IS NOT THE SAME AS HEAP' (11503).
Recreate the index.
INFO: Index ua_user_data_exp_login_key: Pages 81; Tuples 11505: Deleted
223.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
WARNING: Index ua_user_data_exp_login_key: NUMBER OF INDEX' TUPLES (11505)
IS NOT THE SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_id_provider: Pages 87; Tuples 11505: Deleted 223.
CPU 0.00s/0.02u sec elapsed 0.26 sec.
WARNING: Index exp_id_provider: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_ci_login: Pages 81; Tuples 11505: Deleted 223.
CPU 0.00s/0.01u sec elapsed 0.07 sec.
WARNING: Index exp_ci_login: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_country: Pages 106; Tuples 11505: Deleted 223.
CPU 0.00s/0.01u sec elapsed 0.28 sec.
WARNING: Index exp_country: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_os_type: Pages 269; Tuples 11505: Deleted 223.
CPU 0.03s/0.04u sec elapsed 0.92 sec.
WARNING: Index exp_os_type: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_card: Pages 96; Tuples 11505: Deleted 223.
CPU 0.01s/0.01u sec elapsed 0.22 sec.
WARNING: Index exp_card: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS
HEAP' (11503).
Recreate the index.
INFO: Index exp_status: Pages 848; Tuples 11505: Deleted 223.
CPU 0.12s/0.03u sec elapsed 4.34 sec.
WARNING: Index exp_status: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_email: Pages 123; Tuples 11505: Deleted 223.
CPU 0.00s/0.02u sec elapsed 0.19 sec.
WARNING: Index exp_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_ci_email: Pages 123; Tuples 11505: Deleted 223.
CPU 0.02s/0.01u sec elapsed 0.20 sec.
WARNING: Index exp_ci_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_lastname: Pages 79; Tuples 11505: Deleted 223.
CPU 0.01s/0.00u sec elapsed 0.08 sec.
WARNING: Index exp_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_ci_lastname: Pages 79; Tuples 11505: Deleted 223.
CPU 0.03s/0.01u sec elapsed 0.12 sec.
WARNING: Index exp_ci_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_orbital_ptns: Pages 670; Tuples 11505: Deleted 223.
CPU 0.12s/0.05u sec elapsed 4.46 sec.
WARNING: Index exp_orbital_ptns: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_stickers: Pages 210; Tuples 11505: Deleted 223.
CPU 0.05s/0.01u sec elapsed 0.68 sec.
WARNING: Index exp_stickers: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_pid: Pages 443; Tuples 11505: Deleted 223.
CPU 0.05s/0.01u sec elapsed 2.16 sec.
WARNING: Index exp_pid: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS
HEAP' (11503).
Recreate the index.
INFO: Index exp_mac_address: Pages 114; Tuples 11505: Deleted 223.
CPU 0.02s/0.02u sec elapsed 0.14 sec.
WARNING: Index exp_mac_address: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_mac_address_normal: Pages 114; Tuples 11505: Deleted 223.
CPU 0.01s/0.05u sec elapsed 0.11 sec.
WARNING: Index exp_mac_address_normal: NUMBER OF INDEX' TUPLES (11505) IS
NOT THE SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_provider: Pages 299; Tuples 7516: Deleted 223.
CPU 0.05s/0.04u sec elapsed 1.28 sec.
INFO: Rel ua_user_data_exp: Pages: 890 --> 878; Tuple(s) moved: 11.
CPU 0.01s/0.09u sec elapsed 0.12 sec.
INFO: Index ua_user_data_exp_id_user_key: Pages 52; Tuples 11505: Deleted
11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index ua_user_data_exp_id_user_key: NUMBER OF INDEX' TUPLES
(11505) IS NOT THE SAME AS HEAP' (11503).
Recreate the index.
INFO: Index ua_user_data_exp_login_key: Pages 81; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index ua_user_data_exp_login_key: NUMBER OF INDEX' TUPLES (11505)
IS NOT THE SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_id_provider: Pages 87; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_id_provider: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_ci_login: Pages 81; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_ci_login: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_country: Pages 106; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_country: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_os_type: Pages 269; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_os_type: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_card: Pages 96; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_card: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS
HEAP' (11503).
Recreate the index.
INFO: Index exp_status: Pages 848; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_status: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_email: Pages 123; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS HEAP' (11503).
Recreate the index.
INFO: Index exp_ci_email: Pages 123; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_ci_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_lastname: Pages 79; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_ci_lastname: Pages 79; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_ci_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_orbital_ptns: Pages 670; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_orbital_ptns: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_stickers: Pages 210; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_stickers: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_pid: Pages 443; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_pid: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS
HEAP' (11503).
Recreate the index.
INFO: Index exp_mac_address: Pages 114; Tuples 11505: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: Index exp_mac_address: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_mac_address_normal: Pages 114; Tuples 11505: Deleted 11.
CPU 0.00s/0.01u sec elapsed 0.00 sec.
WARNING: Index exp_mac_address_normal: NUMBER OF INDEX' TUPLES (11505) IS
NOT THE SAME AS HEAP' (11503).
Recreate the index.
INFO: Index exp_provider: Pages 299; Tuples 7516: Deleted 11.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Newsgroups: comp.databases.postgresql.admin
Sent: Monday, September 08, 2003 11:41 PM
Subject: Re: Duplicate key
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
> > I had one row duplicated with the same login and the same id_user,
> > was failing was the update of that row complaining about the duplicated
> > key.
>
> Oh. Your report was quite unclear; I thought you were saying that
> REINDEX had somehow built two copies of the same index.
>
> Is the row actually duplicated, or has it just managed to acquire two
> pointers to itself in the indexes? You could check by seeing whether
> the two apparent instances have the same or different ctid values
> (ctid is a system column not shown unless you ask for it, like oid).
> If they are different ctids, it would be useful to see whether they have
> the same or different oid,xmin,cmin,xmax,cmax (more system columns).
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
No clues? Gaetano ----- Original Message ----- From: ""Gaetano Mendola"" <mendola@bigfoot.com> Newsgroups: comp.databases.postgresql.admin Sent: Tuesday, September 09, 2003 4:39 PM Subject: Re: Duplicate key ( reindex and vacuum full logs ) > just for add informations on the problem: > these are the logs received by the reindex and by > vacuum full on that table ( before to delete the > duplicated rows): > > ===== REINDEX ======= > > Start Reindex table ua_user_data_exp at Fri Sep 5 07:12:26 CEST 2003 > ERROR: Cannot create unique index. Table contains non-unique values > > > ===== VACUUM FULL ====== > > Start Vacuum table ua_user_data_exp at Fri Sep 5 07:13:11 CEST 2003 > INFO: --Relation public.ua_user_data_exp-- > INFO: Pages 890: Changed 119, reaped 531, Empty 0, New 0; Tup 11503: Vac > 223, Keep/VTL 206/206, UnUsed 1674, MinLen 44, MaxLen > 696; Re-using: Free/Avail. Space 1007004/934048; EndEmpty/Avail. Pages > 1/421. > CPU 0.08s/0.01u sec elapsed 0.08 sec. > INFO: Index ua_user_data_exp_id_user_key: Pages 52; Tuples 11505: Deleted > 223. > CPU 0.01s/0.01u sec elapsed 0.03 sec. > WARNING: Index ua_user_data_exp_id_user_key: NUMBER OF INDEX' TUPLES > (11505) IS NOT THE SAME AS HEAP' (11503). > Recreate the index. > INFO: Index ua_user_data_exp_login_key: Pages 81; Tuples 11505: Deleted > 223. > CPU 0.00s/0.00u sec elapsed 0.07 sec. > WARNING: Index ua_user_data_exp_login_key: NUMBER OF INDEX' TUPLES (11505) > IS NOT THE SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_id_provider: Pages 87; Tuples 11505: Deleted 223. > CPU 0.00s/0.02u sec elapsed 0.26 sec. > WARNING: Index exp_id_provider: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_ci_login: Pages 81; Tuples 11505: Deleted 223. > CPU 0.00s/0.01u sec elapsed 0.07 sec. > WARNING: Index exp_ci_login: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_country: Pages 106; Tuples 11505: Deleted 223. > CPU 0.00s/0.01u sec elapsed 0.28 sec. > WARNING: Index exp_country: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_os_type: Pages 269; Tuples 11505: Deleted 223. > CPU 0.03s/0.04u sec elapsed 0.92 sec. > WARNING: Index exp_os_type: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_card: Pages 96; Tuples 11505: Deleted 223. > CPU 0.01s/0.01u sec elapsed 0.22 sec. > WARNING: Index exp_card: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS > HEAP' (11503). > Recreate the index. > INFO: Index exp_status: Pages 848; Tuples 11505: Deleted 223. > CPU 0.12s/0.03u sec elapsed 4.34 sec. > WARNING: Index exp_status: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_email: Pages 123; Tuples 11505: Deleted 223. > CPU 0.00s/0.02u sec elapsed 0.19 sec. > WARNING: Index exp_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_ci_email: Pages 123; Tuples 11505: Deleted 223. > CPU 0.02s/0.01u sec elapsed 0.20 sec. > WARNING: Index exp_ci_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_lastname: Pages 79; Tuples 11505: Deleted 223. > CPU 0.01s/0.00u sec elapsed 0.08 sec. > WARNING: Index exp_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_ci_lastname: Pages 79; Tuples 11505: Deleted 223. > CPU 0.03s/0.01u sec elapsed 0.12 sec. > WARNING: Index exp_ci_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_orbital_ptns: Pages 670; Tuples 11505: Deleted 223. > CPU 0.12s/0.05u sec elapsed 4.46 sec. > WARNING: Index exp_orbital_ptns: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_stickers: Pages 210; Tuples 11505: Deleted 223. > CPU 0.05s/0.01u sec elapsed 0.68 sec. > WARNING: Index exp_stickers: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_pid: Pages 443; Tuples 11505: Deleted 223. > CPU 0.05s/0.01u sec elapsed 2.16 sec. > WARNING: Index exp_pid: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS > HEAP' (11503). > Recreate the index. > INFO: Index exp_mac_address: Pages 114; Tuples 11505: Deleted 223. > CPU 0.02s/0.02u sec elapsed 0.14 sec. > WARNING: Index exp_mac_address: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_mac_address_normal: Pages 114; Tuples 11505: Deleted 223. > CPU 0.01s/0.05u sec elapsed 0.11 sec. > WARNING: Index exp_mac_address_normal: NUMBER OF INDEX' TUPLES (11505) IS > NOT THE SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_provider: Pages 299; Tuples 7516: Deleted 223. > CPU 0.05s/0.04u sec elapsed 1.28 sec. > INFO: Rel ua_user_data_exp: Pages: 890 --> 878; Tuple(s) moved: 11. > CPU 0.01s/0.09u sec elapsed 0.12 sec. > INFO: Index ua_user_data_exp_id_user_key: Pages 52; Tuples 11505: Deleted > 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index ua_user_data_exp_id_user_key: NUMBER OF INDEX' TUPLES > (11505) IS NOT THE SAME AS HEAP' (11503). > Recreate the index. > INFO: Index ua_user_data_exp_login_key: Pages 81; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index ua_user_data_exp_login_key: NUMBER OF INDEX' TUPLES (11505) > IS NOT THE SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_id_provider: Pages 87; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_id_provider: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_ci_login: Pages 81; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_ci_login: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_country: Pages 106; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_country: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_os_type: Pages 269; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_os_type: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_card: Pages 96; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_card: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS > HEAP' (11503). > Recreate the index. > INFO: Index exp_status: Pages 848; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_status: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_email: Pages 123; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME > AS HEAP' (11503). > Recreate the index. > INFO: Index exp_ci_email: Pages 123; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_ci_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_lastname: Pages 79; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_ci_lastname: Pages 79; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_ci_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_orbital_ptns: Pages 670; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_orbital_ptns: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_stickers: Pages 210; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_stickers: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_pid: Pages 443; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_pid: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME AS > HEAP' (11503). > Recreate the index. > INFO: Index exp_mac_address: Pages 114; Tuples 11505: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > WARNING: Index exp_mac_address: NUMBER OF INDEX' TUPLES (11505) IS NOT THE > SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_mac_address_normal: Pages 114; Tuples 11505: Deleted 11. > CPU 0.00s/0.01u sec elapsed 0.00 sec. > WARNING: Index exp_mac_address_normal: NUMBER OF INDEX' TUPLES (11505) IS > NOT THE SAME AS HEAP' (11503). > Recreate the index. > INFO: Index exp_provider: Pages 299; Tuples 7516: Deleted 11. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > > > > > > ----- Original Message ----- > From: "Tom Lane" <tgl@sss.pgh.pa.us> > Newsgroups: comp.databases.postgresql.admin > Sent: Monday, September 08, 2003 11:41 PM > Subject: Re: Duplicate key > > > > "Gaetano Mendola" <mendola@bigfoot.com> writes: > > > I had one row duplicated with the same login and the same id_user, > > > was failing was the update of that row complaining about the duplicated > > > key. > > > > Oh. Your report was quite unclear; I thought you were saying that > > REINDEX had somehow built two copies of the same index. > > > > Is the row actually duplicated, or has it just managed to acquire two > > pointers to itself in the indexes? You could check by seeing whether > > the two apparent instances have the same or different ctid values > > (ctid is a system column not shown unless you ask for it, like oid). > > If they are different ctids, it would be useful to see whether they have > > the same or different oid,xmin,cmin,xmax,cmax (more system columns). > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >