[rt-users] Full-text indexing on Pg (was Re: Upgrade and migration questions)

[Kenneth Marshall]

On Wed, Jan 27, 2010 at 05:49:06PM +0000, Dominic Hargreaves wrote:
> On Wed, Jan 27, 2010 at 08:58:47AM -0600, Kenneth Marshall wrote:
> > On Wed, Jan 27, 2010 at 02:45:52PM +0000, Dominic Hargreaves wrote:
> 
> > > Just to note that I assume you're talking about 
> > > <http://wiki.bestpractical.com/view/PostgreSQLFullText> which isn't
> > > a standard part of RT yet; I wondered for a while after reading your
> > > message whether I'd missed something!
> > > 
> > > It's a shame that the API for SearchBuilder isn't such that you can
> > > configure to treat certain columns as full-text-searchable ones rather
> > > than have to manually hack it in; makes deployment a little more
> > > fiddly.
> > > 
> > > But thanks for mentioning it because I didn't realise that this work
> > > had been done and it's something we're interested in!
> 
> > Yes, that is patch. I actually wrote it based on the Oracle
> > patch that is also in the wiki. If you take a look, the patch
> > is almost ridiculously simply and changes next to nothing in
> > the code because I could leverage the fact that creating a
> > full text search query cleaned up the strings without the need
> > to do it in the RT code base. We run it here and it is worth
> > its weight in gold the first time several users issue a fulltext
> > search simultaneously on anything but the smallest database. I
> > suspect that it is not in the default RT because it is not
> > globally available on all backends. To support that, they would
> > need to stitch in some other search engine to support those
> > functions -- a much, much more complicated option both in lines
> > of code to write and support. As far as "fiddly" goes, many
> > other pieces of a base RT install were way more so than the
> > full text search piece. :)
> 
> Indeed. In some senses it is a small change, but my general concern
> is that it is changing the behaviour in a supposedly general
> library to behave specially if a particular table name is encountered.
> Since we run multiple RT instances using a common software deployment
> infrastructure (all our software is installed as Debian packages, even
> where local modifications have been made) we like to reduce the 
> occurrence of this sort of thing as much as possible.

That is reasonable. I made some trade-offs in my patch that could
be revisited if others were interested in modularizing the functionality.

> 
> I don't agree with your assertion that an FTS search engine would have
> to be put in place to deal with other database types; the functionality
> would just vary between databases. It would probably also want to be an
> opt-in configuration option.
> 
I agree that using database specific functions for FTS would work,
unfortunately not all backends support FTS which was why I suggested
that an external search engine would be needed. I think an opt-in
configuration would be the way to go. I submitted my patch in the
vein of the Oracle patch to try and add a base for Best Practical
to draw on should they move in that direction.

> It's non-trivial to cleanly integrate either way, but it's an
> interesting area. We have some user demand for this so the next time
> we review our services internally it may be that we can put some
> resources into going in that direction (if, indeed, Best Practical
> don't already have any drivers in that area).
> 
> Dominic.
> 

That would be cool.

Regards,
Ken