Синхронизация полнотекстового индекса
Если ваша база данных хоть сколько-нибудь обновляется, то вам нужно будет следить за поддержанием полнотекстового индекс по мере добавление новых документов. PostgreSQL позволяет автоматизировать этот процесс с помощью определения триггера, который запускается после добавления новой строки или обновления существующих записей. Встроенный триггер tsearch()
позволяет легко настроить обновление индекса, можно задать несколько текстовых колонок и имя функции для обработки соответствующей колонки. Вот пример использования функции для замены знака @ на знак пробела.
CREATE FUNCTION dropatsymbol(text) RETURNS text AS 'select replace($1, ''@'', '' '');' LANGUAGE SQL;
CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON tblMessages FOR EACH ROW EXECUTE PROCEDURE tsearch(tsvector_column,dropatsymbol, strMessage);
Для более сложного случая, когда документ состоит из нескольких частей с разными весами можно написать процедуру на языке plpgsql
(не забудьте разрешить его использование с помощью команды createlang plpgsql DBNAME).
Создадим тестовую табличку со следующей структурой.
CREATE TABLE aa ( id integer primary key, t1 text, t2 text, fts tsvector );
=# create function my_update() returns trigger as $$ BEGIN NEW.fts= setweight( to_tsvector('english',NEW.t1),'A') setweight( to_tsvector('english',NEW.t2),'B'); RETURN NEW; END; $$ language plpgsql;
В этой функции мы для простоты опустили использование coalesce().
CREATE TRIGGER fts_update BEFORE INSERT OR UPDATE ON aa FOR EACH ROW EXECUTE PROCEDURE my_update();
=# insert into aa (id, t1,t2) values(1,'12,15,789,3','500'); =# insert into aa (id, t1,t2) values(2,'-546,3','150'); =# select * from aa; id | t1 | t2 | fts ----+-------------+-----+------------------------------------------ 1 | 12,15,789,3 | 500 | '3':4A '12':1A '15':2A '500':5B '789':3A 2 | -546,3 | 150 | '3':2A '150':3B '-546':1A (2 rows)
Как мы видим, вставка новых записей работает как и ожидалось. Проверим обновление.
=# update aa set t1 = '1234567' where id=1; =# select * from aa; id | t1 | t2 | fts ----+---------+-----+--------------------------- 2 | -546,3 | 150 | '3':2A '150':3B '-546':1A 1 | 1234567 | 500 | '500':2B '1234567':1A (2 rows)
Так как триггер запускается при любом обновлении или добавлении записей, то работа с таблицами может замедляться, если обновление полнотекстового индекса является очень дорогостоящей операцией, даже когда обновляются атрибуты, которые не имеют отношение к нему. Чтобы избежать лишней работы в функции fts_update можно вставить проверку на изменение текстового атрибута, например
If ( OLD.t1 <> NEW.t1 or OLD.t2 <> NEW.t2 ) Then -- получение fts Endif
