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

[Dominic Hargreaves]

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.

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.

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.

-- 
Dominic Hargreaves, Systems Development and Support Team
Computing Services, University of Oxford
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100127/697345a0/attachment.sig>