Thread: Corrupt RTREE index

Corrupt RTREE index

From
Greg Stark
Date:
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

Re: Corrupt RTREE index

From
Tom Lane
Date:
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

Re: Corrupt RTREE index

From
Greg Stark
Date:
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

Re: Corrupt RTREE index

From
Tom Lane
Date:
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

Re: Corrupt RTREE index

From
Greg Stark
Date:
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

Re: Corrupt RTREE index

From
"Dann Corbit"
Date:
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

Re: Corrupt RTREE index

From
Scott Marlowe
Date:
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

Re: Corrupt RTREE index

From
Neil Conway
Date:
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



Re: Corrupt RTREE index

From
"Dann Corbit"
Date:
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



Re: Corrupt RTREE index

From
Greg Stark
Date:
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

Re: Corrupt RTREE index

From
"Dann Corbit"
Date:

-----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.
<<

Re: Corrupt RTREE index

From
Scott Marlowe
Date:
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
>
>

Re: Corrupt RTREE index

From
"Frank D. Engel, Jr."
Date:
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