Thread: 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