Re: index support for arrays (GiST)

Well,

we found an answer ourserlves. Patch for 7.0.3 is included below.
Credits to Teodor Sigaev (teodor@stack.net)
Some comments: 

From src/backend/access/gist/gist.c
/*
** Take a compressed entry, and install it on a page.  Since we now know
** where the entry will live, we decompress it and recompress it using
** that knowledge (some compression routines may want to fish around
** on the page, for example, or do something special for leaf nodes.)
*/

After compressing of index it's written to disk decompressed (!) which
is the reason we have the problem with broken index !
It looks like other people just didn't use index decompression function
(at least in Gene's code decompression function just do return  ) and 
that's why this bug was not discovered. We could make a patch for 
upcoming 7.1 if hackers desired. I consider this patch as a bugfix
not a new feature or improvement. We got a very promising results.

Another question to this code is - why gistPageAddItem does
compress - decompress - compress. It's not clear from the comment.
Best regards,
    Oleg

-------------------------------------------------------------------------
maze% diff -c backend/access/gist/gist.c 
backend/access/gist/gist.c.orig  
*** backend/access/gist/gist.c  Fri Dec 15 13:03:40 2000
--- backend/access/gist/gist.c.orig     Fri Dec 15 13:00:50 2000
***************
*** 374,380 **** {       GISTENTRY       tmpcentry;       IndexTuple      itup = (IndexTuple) item;
-       OffsetNumber    retval;        /*        * recompress the item given that we now know the exact page and
--- 374,379 ----
***************
*** 386,400 ****                                  IndexTupleSize(itup) -
sizeof(IndexTupleData), FALSE);       gistcentryinit(giststate, &tmpcentry, dentry->pred, r, page,
           offsetNumber, dentry->bytes, FALSE);
 
!       *newtup = gist_tuple_replacekey(r, tmpcentry, itup);
!       retval = PageAddItem(page, (Item) *newtup,
IndexTupleSize(*newtup),
!                                               offsetNumber, flags);       /* be tidy */       if (tmpcentry.pred !=
dentry->pred              && tmpcentry.pred != (((char *) itup) +
 
sizeof(IndexTupleData)))               pfree(tmpcentry.pred); 
!       return (retval); }  
--- 385,398 ----                                  IndexTupleSize(itup) -
sizeof(IndexTupleData), FALSE);       gistcentryinit(giststate, &tmpcentry, dentry->pred, r, page,
           offsetNumber, dentry->bytes, FALSE);
 
!       *newtup = gist_tuple_replacekey(r, *dentry, itup);       /* be tidy */       if (tmpcentry.pred != dentry->pred
             && tmpcentry.pred != (((char *) itup) +
 
sizeof(IndexTupleData)))               pfree(tmpcentry.pred); 
!       return (PageAddItem(page, (Item) *newtup,
IndexTupleSize(*newtup),
!                                               offsetNumber, flags)); } 

-----------------------------------------------------------------------



On Wed, 13 Dec 2000, Oleg Bartunov wrote:

> Date: Wed, 13 Dec 2000 18:48:40 +0300 (GMT)
> From: Oleg Bartunov <oleg@sai.msu.su>
> To: selkovjr@mcs.anl.gov
> Cc: Tom Lane <tgl@sss.pgh.pa.us>, vmikheev@SECTORBASE.COM,
>     'pgsql-hackers ' <pgsql-hackers@postgresql.org>
> Subject: [HACKERS] index support for arrays (GiST)
> 
> Hi,
> 
> we are getting a bit close to add index support for int arrays using
> GiST interface. This will really drive up performance of our full text
> search fully based on postgresql. We have a problem with broken index
> and couldn't find a reason. I attached archive with sources
> for GiST functions and test suite to show a problem - vacuum analyze
> at end end of TESTSQL should complain about broken index.
> Here is a short description:
> 1. untar in contrib 7.0.*
> 2. cd _intarray
> 3. edit Makefile for TESTDB (name of db for test)
> 4. createdb TESTDB
> 5. gmake
> 6. gmake install
> 7. psql TESTDB < TESTSQL
> 
>     Regards,
> 
>         Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83