Thread: Corrupt RTREE index
I have what appears to be a corrupt RTREE index.
The first query shows that of the fifteen records I'm looking at, every one of
them has the "@" based condition showing as true. The second shows one record
that really ought to be there not being listed.
I just tried the second query with enable_indexscan = off and the missing
record reappears. So I guess this is a corrupt index.
This is 7.4.6 but the database was initdb'd with an earlier 7.4.
Should I REINDEX or should I keep this around for forensic study?
slo=>
SELECT distinct store_id, geom2 @ box '(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
FROM store_location
WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) <= 60
;
slo=> slo-> slo-> slo-> slo-> store_id | ?column?
----------+----------
504 | t
597 | t
909 | t
2841 | t
2940 | t
2997 | t
3423 | t
3438 | t
3641 | t
3656 | t
4057 | t
4487 | t
4489 | t
4490 | t
4493 | t
(15 rows)
slo=>
SELECT distinct store_id, geom2 @ box '(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
FROM store_location
WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) <= 60
AND geom2 @ box '(-72.7956933266664,46.041219387024),(-74.3364602689304,44.9613057801126)'
;
slo=> slo-> slo-> slo-> slo-> store_id | ?column?
----------+----------
504 | t
597 | t
909 | t
2841 | t
2940 | t
2997 | t
3423 | t
3438 | t
3641 | t
3656 | t
4057 | t
4487 | t
4489 | t
4490 | t
(14 rows)
--
greg
Greg Stark <gsstark@mit.edu> writes: > I have what appears to be a corrupt RTREE index. I wonder if it's actually corrupt, or if it's just that the index semantics don't truly match the operator. If the latter, REINDEXing won't fix it. As for the first theory, have you had any database crashes lately? If so I'd write this off as a failure caused by the lack of WAL-logging support in rtree. As for the second theory, in this thread http://archives.postgresql.org/pgsql-general/2004-03/msg01135.php we concluded that the existing mapping of geometric operators onto rtree indexes is wrong; see in particular http://archives.postgresql.org/pgsql-general/2004-03/msg01143.php However that discussion dealt with << and related operators, not @. I didn't think @ was broken ... but I might have missed something. (I was expecting bwhite to come back with a patch to fix the rtree problems he'd identified, but he never did, so it's still an open issue.) regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I wonder if it's actually corrupt, or if it's just that the index > semantics don't truly match the operator. If the latter, REINDEXing > won't fix it. I think the index always worked properly in the past. But of course it would be hard to tell if that was really true. > As for the first theory, have you had any database crashes lately? > If so I'd write this off as a failure caused by the lack of WAL-logging > support in rtree. Ugh. I have had a couple system crashes recently. I kind of doubt the index was in the process of being written to, I don't tend to watch Farscape at the same time as doing development work... But I can't guarantee it. So you don't think this case is worth doing forensics on? > I didn't think @ was broken ... but I might have missed something. I didn't think @ was broken either. -- greg
Greg Stark <gsstark@mit.edu> writes:
> So you don't think this case is worth doing forensics on?
If the problem goes away after REINDEX then I'll write it off as missing
WAL support. rtree is not high enough on my list of priorities to
justify more effort :-(
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > So you don't think this case is worth doing forensics on? > > If the problem goes away after REINDEX then I'll write it off as missing > WAL support. rtree is not high enough on my list of priorities to > justify more effort :-( Fwiw, the problem went away after REINDEX. -- greg
I suggest a warning (if there is not already one generated) on create
index for rtree indexes so that users know that they are not fully
supported.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, December 13, 2004 4:14 PM
To: Greg Stark
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Corrupt RTREE index
Greg Stark <gsstark@mit.edu> writes:
> So you don't think this case is worth doing forensics on?
If the problem goes away after REINDEX then I'll write it off as missing
WAL support. rtree is not high enough on my list of priorities to
justify more effort :-(
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
IS this same issue true for hash or GiST indexes? On Tue, 2004-12-14 at 13:49, Dann Corbit wrote: > I suggest a warning (if there is not already one generated) on create > index for rtree indexes so that users know that they are not fully > supported. > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane > Sent: Monday, December 13, 2004 4:14 PM > To: Greg Stark > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Corrupt RTREE index > > Greg Stark <gsstark@mit.edu> writes: > > So you don't think this case is worth doing forensics on? > > If the problem goes away after REINDEX then I'll write it off as missing > WAL support. rtree is not high enough on my list of priorities to > justify more effort :-( > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Tue, 2004-12-14 at 14:12 -0600, Scott Marlowe wrote: > IS this same issue true for hash or GiST indexes? Yes, it is: currently, only btree indexes are WAL safe. (I spent some time recently looking into adding page-level concurrency and WAL to GiST, but I haven't had a chance to finish that work -- it is quite a big job...) -Neil
Would it be possible to rebuild all non-btree indexes when a recovery takes place? Another thing that seems it might be nice is to check the non-btree indexes during analyze (if that is possible and not too expensive). -----Original Message----- From: Neil Conway [mailto:neilc@samurai.com] Sent: Tuesday, December 14, 2004 4:39 PM To: Scott Marlowe Cc: Dann Corbit; pgsql-general Subject: Re: [GENERAL] Corrupt RTREE index On Tue, 2004-12-14 at 14:12 -0600, Scott Marlowe wrote: > IS this same issue true for hash or GiST indexes? Yes, it is: currently, only btree indexes are WAL safe. (I spent some time recently looking into adding page-level concurrency and WAL to GiST, but I haven't had a chance to finish that work -- it is quite a big job...) -Neil
Scott Marlowe <smarlowe@g2switchworks.com> writes: > IS this same issue true for hash or GiST indexes? I think that's true, afaik rtree, GiST, and hash are all not WAL-logged. > On Tue, 2004-12-14 at 13:49, Dann Corbit wrote: > > I suggest a warning (if there is not already one generated) on create > > index for rtree indexes so that users know that they are not fully > > supported. I'm not sure what he means by "supported" though. I'm getting all the support I'm paying for, plus a whole lot more. -- greg
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Greg Stark Sent: Tuesday, December 14, 2004 8:49 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Corrupt RTREE index Scott Marlowe <smarlowe@g2switchworks.com> writes: > IS this same issue true for hash or GiST indexes? I think that's true, afaik rtree, GiST, and hash are all not WAL-logged. > On Tue, 2004-12-14 at 13:49, Dann Corbit wrote: > > I suggest a warning (if there is not already one generated) on create > > index for rtree indexes so that users know that they are not fully > > supported. I'm not sure what he means by "supported" though. I'm getting all the support I'm paying for, plus a whole lot more. >> By "supported" I mean the operations against the index are logged, so that if someone kicks the plug out of the wall on my PostgreSQL database and I walk over and plug it back in, I can rely on my btree indexes but all bets are off for hash, rtree and gist indexes when the server restarts. Or perhaps I misunderstand the repercussions of index types not being included in the WAL. <<
On Tue, 2004-12-14 at 18:43, Dann Corbit wrote: > Would it be possible to rebuild all non-btree indexes when a recovery > takes place? Considering how long I've seen some large hash indexes take to build, that might be better left as an optional setting. > Another thing that seems it might be nice is to check the non-btree > indexes during analyze (if that is possible and not too expensive). > > -----Original Message----- > From: Neil Conway [mailto:neilc@samurai.com] > Sent: Tuesday, December 14, 2004 4:39 PM > To: Scott Marlowe > Cc: Dann Corbit; pgsql-general > Subject: Re: [GENERAL] Corrupt RTREE index > > On Tue, 2004-12-14 at 14:12 -0600, Scott Marlowe wrote: > > IS this same issue true for hash or GiST indexes? > > Yes, it is: currently, only btree indexes are WAL safe. > > (I spent some time recently looking into adding page-level concurrency > and WAL to GiST, but I haven't had a chance to finish that work -- it is > quite a big job...) > > -Neil > >
Maybe this could be handled by logging the fact that the index is being modified. Then during recovery, if an index was being modified, and the log doesn't indicate that the modification was completed, the index can be rebuilt? On Dec 15, 2004, at 10:02 AM, Scott Marlowe wrote: > On Tue, 2004-12-14 at 18:43, Dann Corbit wrote: >> Would it be possible to rebuild all non-btree indexes when a recovery >> takes place? > > Considering how long I've seen some large hash indexes take to build, > that might be better left as an optional setting. > >> Another thing that seems it might be nice is to check the non-btree >> indexes during analyze (if that is possible and not too expensive). >> >> -----Original Message----- >> From: Neil Conway [mailto:neilc@samurai.com] >> Sent: Tuesday, December 14, 2004 4:39 PM >> To: Scott Marlowe >> Cc: Dann Corbit; pgsql-general >> Subject: Re: [GENERAL] Corrupt RTREE index >> >> On Tue, 2004-12-14 at 14:12 -0600, Scott Marlowe wrote: >>> IS this same issue true for hash or GiST indexes? >> >> Yes, it is: currently, only btree indexes are WAL safe. >> >> (I spent some time recently looking into adding page-level concurrency >> and WAL to GiST, but I haven't had a chance to finish that work -- it >> is >> quite a big job...) >> >> -Neil >> >> > > ---------------------------(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 > > ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com