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: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com