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!
>
Dear RT Users,
I just finished testing the two index types for PostgreSQL text
indexing (GIST and GIN). Here are the index creation commands using
the GIN index type. I have tried both, and unless you are in an
extremely update intensive environment you will really want GIN
-- very, very fast queries.
CREATE INDEX attachments_textsearch ON attachments
USING GIN (textsearchable );
CREATE INDEX largecontent_textsearch ON objectcustomfieldvalues
USING GIN (textsearchable );
Happy full text searching,
Ken
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [email protected]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
