[rt-users] RT full text indexing with PostgreSQL

[Kenneth Marshall]

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.
> 
> Here are the commands for adding the triggers to keep the textsearchable
> columns corresponding to attachments.subject/content and
> objectcustomfieldvalues.largecontent up to date:
> 
>   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
>   ON attachments FOR EACH ROW EXECUTE PROCEDURE
>   tsvector_update_trigger(textsearchable, 'pg_catalog.english', subject, content);
> 
>   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
>   ON objectcustomfieldvalues FOR EACH ROW EXECUTE PROCEDURE
>   tsvector_update_trigger(textsearchable, 'pg_catalog.english', largecontent);
> 
> Please let me know if there are any problems and bon voyage.
> 
> Ken

I have added a variation of the above triggers to the wiki page
to only pre-parse the first 1MB of attachments/customfields:

  CREATE FUNCTION attachments_trigger() RETURNS trigger AS $$
  begin
    new.textsearchable :=
        to_tsvector('pg_catalog.english', substring(coalesce(new.subject, '') || coalesce(new.content, '') from 1 for 1000000));
    return new;
  end
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  ON attachments FOR EACH ROW EXECUTE PROCEDURE attachments_trigger();

  CREATE FUNCTION objectcustomfieldvalues_trigger() RETURNS trigger AS $$
  begin
    new.textsearchable :=
        to_tsvector('pg_catalog.english', substring(coalesce(new.largecontent, '') from 1 for 1000000));
    return new;
  end
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  ON objectcustomfieldvalues FOR EACH ROW EXECUTE PROCEDURE
  objectcustomfieldvalues_trigger();

Please send me any feedback or suggestions.

Cheers,
Ken

PS. The fast full contents searches really rock!