[rt-users] RT full text indexing with PostgreSQL

[Kenneth Marshall]

On Fri, Feb 06, 2009 at 11:01:31AM +0100, Rafael Martinez wrote:
> Kenneth Marshall wrote:
> > On Sun, Feb 01, 2009 at 09:41:42PM -0600, Kenneth Marshall wrote:
> >>> I just updated the PostgreSQLFullText pages on the wiki with
> >>> the triggers to keep the parsed document columns updated whenever
> >>> there is a change to the dependent columns.
> >>>
> 
> Hello Kenneth
> 
> First of all thank you for this work. We have been waiting for this for
> a long time but the work we thought had to be done with the RT-code has
> stopped us to implement full text search.
> 
> After a close check of the wiki page:
> http://wiki.bestpractical.com/view/PostgreSQLFullText ....
> 
> I think that the patch of DBIx::SearchBuilder is not 100%
> complete/correct. All lines has the '-' (minus) symbol. I suppose that
> at least some of them should be '+' and not '-'?
> 
> This is a tiny patch, would not be an idea to implement this in RT-core
> functionality, maybe with some config parameters that
> activate/deactivate full text search (when using postgresql/oracle, etc)  .
> 
> regards
> -- 
>  Rafael Martinez, <r.m.guerrero at usit.uio.no>
>  Center for Information Technology Services
>  University of Oslo, Norway
> 
>  PGP Public Key: http://folk.uio.no/rafael/
> 

Rafael,

The diff I posted was to go from the FULLTEXT patch to the
original one. I agree that it would make more sense to go the
other way. I have updated the page. I do think that having it
as an option in RT-core would be nice. I know for PostgreSQL
versions 8.3 and higher, you can use the patch by default since
FULLTEXT indexing comes with the database as a standard feature.
PostgreSQL will simply convert the attachment field automatically
using to_tsvector() on the fly so it will just work as is. Of
course creating the needed index will greatly improve the performance.
I do not know if OracleText has the same fallback in the absence of
the FULLTEXT setup and index. I suspect that run-time tests for
a configuration option would be needed.

Cheers,
Ken