Durante a migração de um banco em PostgreSQL - para uma versão mais nova do mesmo, tive alguns problemas com o TSearch. Então fui à busca da solução e vou compartilhar aqui de forma mais objetiva.
Mas o que é Tsearch?
A todo momento precisamos buscar por palavras ou frases em determinados textos. Quando esta busca é feita em campos relativamente pequenos, normalmente utilizamos a clausula "LIKE", que funciona muito bem. O problema surge quando temos campos realmente grandes, que armazenam textos extensos, para este caso, se utilizarmos a clausula "LIKE" deixaríamos a busca muito custosa em termos computacionais. Agora imagina isso em sites na internet, você busca um edital ou uma lei, tem um tempo para a busca e ainda o tempo de carregamento da página.
Para resolver estes problemas, os sistemas de bancos de dados, como o PostgreSQL e outros, fornecem implementações de busca através de indexação, que permite uma busca mais rápida, baseada em textos extensos, quebrando o texto e criando palavras chaves para indexar. O uso deste mecanismo reduz consideravelmente o número de elementos distintos a serem indexados, alem de possibilitar algoritmos muito mais eficientes para a busca. Este mecanismo de indexação no PostgreSQL recebe o nome de TSearch.
Inicialmente a função TSearch era um modulo a parte do banco PostgreSQL, para utiliza - lá era necessário recompilar o banco, incluindo essa funcionalidade ao mesmo. A partir da versão 8.3, este módulo se tornou parte do kernel do PostgreSQL. Ai entra a questão:
O que muda do módulo compilado, para a versão nativa?
Houve poucas alterações na forma em que se busca a informação. Creio que isso aconteceu para facilitar em partes a vida dos programadores, que não precisão necessariamente refazer todos os selects de seus programas. As alterações da busca foram acrescentadas mais opções.
O que realmente mudou foi a forma com que o TSearch indexa as informações. Na migração do banco para a versão mais recente, o PostgreSQL retira tudo que era do antigo TSearch e leva somente o que é compatível. O que resta é um vazio e a sensação de que nada vai funcionar mais. Mas... calma, nem tudo esta perdido. É possível refazer tudo que não é migrado. Vou mostrar um pouco do que fiz.
Correção Passo a Passo:
Para não me estender por demais, vou considerar que você esteja migrando seu banco para uma versão mais atualizada, portanto não vou explicar como fazer a pesquisa e sim o que fazer para que a pesquisa continue funcionando após a migração.
O primeiro passo é refazer a coluna de indexação. Ela é a primeira a ser excluída, ela foi alterada para que a pesquisa possa ser mais rápida. Segue exemplo da criação da coluna:
ALTER TABLE tabela ADD COLUMN idxFTI tsvector;
O segundo passo é popular essa nova coluna do seu banco de dados. No exemplo abaixo, eu estou indexando varias colunas no campo idxFTI, pego todos os campos e converto para o tipo tsvector utilizando a função "to_tsvector":
UPDATE tabela
SET idxFTI = to_tsvector(coalesce(campo1,'')
||' '|| coalesce(campo2,'')
||' '|| coalesce(campo3,'')
||' '|| coalesce(campo4,'')
||' '|| coalesce(campo5,'')
||' '|| coalesce(campo6,''));
VACUUM FULL ANALYZE;
E o terceiro e ultimo passo é criar uma trigger que atualize esse campo todas as vezes que for incluída ou alterada alguma linha da tabela. Segue minha trigger:
CREATE TRIGGER tsvectorupdate_tabela
BEFORE INSERT OR UPDATE ON tabela
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(idxFTI, 'pg_catalog.english', 'campo1', 'campo2', 'campo3',
'campo4', 'campo5', 'campo6');
Pronto, agora sua busca vai voltar a funcionar novamente sem problemas.
Dentre os vários links que pesquisei tem um que achei de extrema importância:
Também retirei algumas informações daqui:
Onde é utilizado esse tipo de pesquisa:
Forte Abraço!
