Mantendo atualizado o índice FTS no PostgreSQL

Dando sequência ao post anterior "Full Text Search em português no PostgreSQL", veremos como manter atualizado o índice de busca textual, particularmente no caso de os dados da tabela sofrerem muitas atualizações (i.e., inclusões e modificações).

Só para relembrar, o diagrama a seguir ilustra o funcionamento do mecanismo de Full Text Search no PostgreSQL, em que fazem parte processos como parser, normalizador e indexador:

Para exemplificar o problema apresentado no início desse post, considere que a tabela MUNICIPIOS esteja devidamente populada, tendo a coluna de suporte para a busca textual criada e o índice do tipo GIN (ou GiST) associado. Sendo assim, podemos fazer consultas SQL desse tipo:

SELECT nome, uf FROM municipios
WHERE busca @@ plainto_tsquery(simples('agua lindoia'));

Veja o resultado:

curso=# SELECT nome, uf FROM municipios
curso-# WHERE busca @@ plainto_tsquery(simples('agua lindoia'))
       nome       | uf 
------------------+----
 Águas de Lindóia | SP
(1 registro)

O que acontece agora se incluirmos um novo registro na tabela? Veja:

INSERT INTO municipios (codigo, nome, uf)
VALUES (100, 'Águas de Lindóia do Sul', 'RS');

Ao executarmos a busca anterior, o município recém-incluído não aparecerá... Isso porque a coluna de suporte à busca não foi atualizada. Veja o conteúdo dela:

curso=# SELECT nome, uf, busca FROM municipios WHERE codigo = 100;
          nome           | uf | busca 
-------------------------+----+-------
 Águas de Lindóia do Sul | RS | 
(1 registro)

Para manter essa coluna atualizada automaticamente em operações de INSERT ou UPDATE, precisamos de um disparador (trigger). No PostgreSQL criamos uma função em linguagem procedural (geralmente em PL/pgSQL) e em seguida a associamos a um disparador.

Sendo assim, crie a função de trigger com o código abaixo:

CREATE FUNCTION municipios_trigger()
RETURNS trigger AS $$
begin
  new.busca := to_tsvector(simples(new.nome));
  return new;
end
$$ LANGUAGE plpgsql;

Ao ser chamada, ela fará com que o conteúdo da coluna "busca" seja preenchido com o texto gerado da normalização da coluna "nome".

Em seguida, crie o disparador de atualização:

CREATE TRIGGER municipios_tsupdate
BEFORE INSERT OR UPDATE ON municipios
FOR EACH ROW EXECUTE PROCEDURE municipios_trigger();

Ou seja, antes de cada INSERT ou UPDATE na tabela, a função municipios_trigger() será invocada para preencher automaticamente a coluna "busca".

Agora experimente modificar aquele registro inserido acima:

UPDATE municipios SET uf = uf WHERE codigo = 100;

Observe como ficou o conteúdo daquela linha:

curso=# SELECT nome, uf, busca FROM municipios WHERE codigo = 100;
          nome           | uf |           busca           
-------------------------+----+---------------------------
 Águas de Lindóia do Sul | RS | 'agu':1 'lindo':3 'sul':5
(1 registro)

Finalmente efetue a busca textual que havia falhado:

curso=# SELECT nome, uf FROM municipios
curso-# WHERE busca @@ plainto_tsquery(simples('agua lindoia'));
          nome           | uf 
-------------------------+----
 Águas de Lindóia do Sul | RS
 Águas de Lindóia        | SP
(2 registros)

Tente agora fazer uma modificação no nome desse município:

UPDATE municipios SET nome = 'Águas Quentes do Sul' WHERE codigo = 100;

E então refaça a busca considerando o novo nome:

curso=# SELECT codigo, nome, uf FROM municipios
WHERE busca @@ plainto_tsquery(simples('agua quente'));
 codigo |         nome         | uf 
--------+----------------------+----
    100 | Águas Quentes do Sul | RS
(1 registro)

Índice sempre up-to-date! Muito fácil, né? :D

Referências

[1] PostgreSQL 8.3 Documentation - Full Text Search