Обсуждение: Duplicate tuples with unique index
We've experienced problems with a database duplicating tuples in one of the tables. The problem was found during the daily vacuums. Here is an example: db=> vacuum ; NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE SAME AS HEAP' (1258) ERROR: Cannot insert a duplicate key into a unique index db=> vacuum analyze; NOTICE: CreatePortal: portal <vacuum> already exists NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE SAME AS HEAP' (1258) ERROR: Cannot insert a duplicate key into a unique index First I found two doubled tuples where the duplicates had different oid (using "select * from html_text a, html_text b where a.id = b.id and a.oid != b.oid"). I removed the duplicates and tried with another vacuum. This did not solve the problem, so I did the following: drop index html_text_idx; (this was a unique index...) db=> select count(*) from html_text; count -----1259 (1 row) db=> select distinct id from html_text; ... lot of rows ... (1211 rows) Since I could not find any duplicates using my first method, I started looking for duplicate oids... excerpt from "select id, oid from html_text" id oid ----------- 3180|667648 3180|667648 3181|676704 3185|668576 3185|668576 3187|673281 3200|672992 3200|672992 3201|699074 3201|699074 3206|672513 3208|680128 3208|680128 3212|674849 3218|679137 3220|674851 3221|680129 3221|680129 This doesn't feel very well... Has anyone experienced similar problems? Our config looks like this: Postgres 6.5.2 (version() = PostgreSQL 6.5.2 on i386-unknown-freebsd3.3, compiled by cc) FreeBSD 3.3-RELEASE 2xPII SMP vinum configured scsi (mirroring) /Patrik Kudo
Patrik Kudo <kudo@partitur.se> writes:
> db=> vacuum ;
> NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE
> SAME AS HEAP' (1258)
> ERROR: Cannot insert a duplicate key into a unique index
Given the reference to pg_statistic, I wonder if you aren't getting
burnt by the recently-discovered bug wherein VACUUM ANALYZE tries to
create pg_statistic tuples that are too long. I recommend (a) patch
vacuum.c (see Dirk Lutzebaeck's nearby message); then (b) drop and
recreate html_text_idx; finally (c) DELETE FROM pg_statistic and
then VACUUM ANALYZE to rebuild it. Good luck!
regards, tom lane
Thanks! We'll try it out. Regards, Patrik Kudo Tom Lane wrote: > Given the reference to pg_statistic, I wonder if you aren't getting > burnt by the recently-discovered bug wherein VACUUM ANALYZE tries to > create pg_statistic tuples that are too long. I recommend (a) patch > vacuum.c (see Dirk Lutzebaeck's nearby message); then (b) drop and > recreate html_text_idx; finally (c) DELETE FROM pg_statistic and > then VACUUM ANALYZE to rebuild it. Good luck! > > regards, tom lane
Tom Lane wrote: > > Patrik Kudo <kudo@partitur.se> writes: > > db=> vacuum ; > > NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. > > NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE > > SAME AS HEAP' (1258) > > ERROR: Cannot insert a duplicate key into a unique index > > Given the reference to pg_statistic, I wonder if you aren't getting > burnt by the recently-discovered bug wherein VACUUM ANALYZE tries to > create pg_statistic tuples that are too long. I recommend (a) patch > vacuum.c (see Dirk Lutzebaeck's nearby message); then (b) drop and > recreate html_text_idx; finally (c) DELETE FROM pg_statistic and > then VACUUM ANALYZE to rebuild it. Good luck! > > regards, tom lane > > ************ Unfotunately, it doesn't quite work: pp=> vacuum analyze; NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. VACUUM pp=> delete from pg_statistic; ERROR: heap_delete: (am)invalid tid pp=>
Palle Girgensohn <girgen@partitur.se> writes:
> Unfotunately, it doesn't quite work:
> pp=> vacuum analyze;
> NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> VACUUM
> pp=> delete from pg_statistic;
> ERROR: heap_delete: (am)invalid tid
Ugh. Yup, your pg_statistic table is corrupted all right.
What you may have to do is pg_dump that database, destroydb/createdb,
and reload.
I have a possible shortcut for you, but I *strongly* suggest that you
pg_dump first in case it doesn't work! After making your backup dump,
stop the postmaster and truncate the broken database's pg_statistic
file to zero length:cp /dev/null .../data/base/yourdb/pg_statistic
Then restart the postmaster and see if you can vacuum analyze the
broken database. If so, you are good to go; if not, it's time to
reload that database.
regards, tom lane
> Palle Girgensohn <girgen@partitur.se> writes: > > Unfotunately, it doesn't quite work: > > > pp=> vacuum analyze; > > NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. > > VACUUM > > pp=> delete from pg_statistic; > > ERROR: heap_delete: (am)invalid tid > > Ugh. Yup, your pg_statistic table is corrupted all right. > > What you may have to do is pg_dump that database, destroydb/createdb, > and reload. > > I have a possible shortcut for you, but I *strongly* suggest that you > pg_dump first in case it doesn't work! After making your backup dump, > stop the postmaster and truncate the broken database's pg_statistic > file to zero length: > cp /dev/null .../data/base/yourdb/pg_statistic > Then restart the postmaster and see if you can vacuum analyze the > broken database. If so, you are good to go; if not, it's time to > reload that database. > Running pg_upgrade is another option. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > Palle Girgensohn <girgen@partitur.se> writes: > > > Unfotunately, it doesn't quite work: > > > > > pp=> vacuum analyze; > > > NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. > > > VACUUM > > > pp=> delete from pg_statistic; > > > ERROR: heap_delete: (am)invalid tid > > > > Ugh. Yup, your pg_statistic table is corrupted all right. > > > > What you may have to do is pg_dump that database, destroydb/createdb, > > and reload. > > > > I have a possible shortcut for you, but I *strongly* suggest that you > > pg_dump first in case it doesn't work! After making your backup dump, > > stop the postmaster and truncate the broken database's pg_statistic > > file to zero length: > > cp /dev/null .../data/base/yourdb/pg_statistic > > Then restart the postmaster and see if you can vacuum analyze the > > broken database. If so, you are good to go; if not, it's time to > > reload that database. > > > > Running pg_upgrade is another option. Nope. pg_upgrade was "disabled in this release because the internal blahblahblah disk layout changed from previous versions".cat /dev/null > pg_statistic didn't help, and the file was already zero bytes long. Applied the patches to vacuum.c and hio.c, and pg_dumpall && initdb && psql < dump.sql fixed it. Thanks for your support! /Palle
> > > I have a possible shortcut for you, but I *strongly* suggest > that you > > pg_dump first in case it doesn't work! After making > your backup dump, > > stop the postmaster and truncate the broken > database's pg_statistic > > file to zero length: > > cp > /dev/null .../data/base/yourdb/pg_statistic > > Then restart > the postmaster and see if you can vacuum analyze the > > broken > database. If so, you are good to go; if not, it's time to > > > reload that database. > > > > Running pg_upgrade is another > option. > > Nope. pg_upgrade was "disabled in this release because the > internal blahblahblah disk layout changed from previous versions". > cat /dev/null > pg_statistic didn't help, and the file was > already zero bytes long. Sorry, you have to edit the script to reenable it. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Tom Lane wrote: > Thanks for the info. Very interesting reading! > Bruce thinks that the pg_upgrade script will ensure that the system- > table tuples are all in frozen states (by VACUUMing them). I don't > trust it worth a dime, myself. Maybe it will work, but it hasn't been > proven in the field. So, if you'd like to try it, by all means do so > --- but make a pg_dump backup first! And let us know whether you have > problems or not! I never tried it; I did an old fashion dump+initdb+restore. So, we will not know whether pg_upgrade is OK or not, at leastfor now. :( Regards, Palle