TauCeti.NET The lost star colony...

Volltextsuche mit PostgreSQL und Tsearch2

UPDATE 2006-08-22: Ich habe eine neue Anleitung geschrieben. Wer UTF8 als DB Encoding verwendet, sollte diese Anleitung verwenden: Volltextsuche mit PostgreSQL 8.1.x, Tsearch2 und UTF8.

UPDATE 2006-04-24: Die Tsearch2-Entwickler haben den UTF8-Support von Postgres 8.2 auf 8.1.3 rückportiert. D.h. UTF8 kann man nun auch unter 8.1.3 einsetzen. Den Patch findet man hier: tsearch2.82.tar.gz (Tsearch2 Entwicklerseite). Ich hab den Text an den entsprechenden Stellen angepaßt. Bisher bin ich ja davon ausgegangen, das die Volltextsuche mit einer Latin1 Datenbank installiert wird.

Gleich vorweg: Dieses Dokument ist "Work in progress". Diese Installationsanweisung ist wohl nicht der Weisheits letzter Schluss, aber man sollte damit eine Tsearch2 Installation hinbekommen.

Tsearch2 ist eine Erweiterung zu PostgreSQL speziell für die Volltextsuche. Zunächst mal, muss man ein bißchen was kompilieren. Gehen wir mal davon aus, das PostgreSQL schon vorher kompiliert worden ist und gehen wir weiter davon aus, das wir Version 8.1.3 verwenden. Den Sourcecode von Postgres haben wir nach /opt/source/postgres gelegt. Dort drin gibt es ein contrib/tsearch2 Verzeichnis in welches man reinwechselt. Wenn man den UTF8 Tsearch2-Patch runtergeladen und in entsprechenden Verzeichnis entpackt hat, dann wechelt man nach tsearch2.82 rein. Wichtig hierbei ist, das das Verzeichnis entweder in tsearch2 umbenannt oder einen Softlink drauf gelegt wird, sonst fliegen einem nachher beim Kompilieren ein paar Sachen um die Ohren. Zum Kompilieren von tsearch2 gibt man in diesem Verzeichnis nun einfach

make
make install

ein. Damit das dann auch mit der deutschen Sprache alles etwas besser funktioniert, müssen noch ein paar zusätzliche Dinge erledigt werden. Das ist zum großen Teil hier beschrieben, aber bei mir hat das so nicht ganz funktioniert. Soweit ich das in den Mailinglisten gelesen habe, unterstützt Tsearch2 momentan noch gar kein UTF8 in der Version 8.1.(0-2) von PostgreSQL. Das soll erst mit der 8.2er offiziell funken. Für die 8.1.3 gibt es den o.g. Patch. Die vorher genannte Seite geht jedenfalls davon aus, das die Datenbank UTF8/Unicode als Encoding verwendet. Damit funktioniert aber die Library mit den Wortstämmen nicht. Diese hat nämlich gar keine Methode für UTF8 sondern nur für Latin1 bzw. eben ISO8859-1. Deshalb gehe ich jetzt davon aus, das die Datenbank mit Encoding Latin1/ISO8859-1 erstellt worden ist. Um das zu überprüfen, connected man sich auf den Datenbankcluster per psql und läßt sich mit \l alle Datenbanken anzeigen. Wenn in der letzten Spalte LATIN1 steht, paßt's. Hat man Version 8.1.3 mit o.g. Patch installiert, kann man auch UTF8/UNICODE als Datenbankencoding verwenden. Mehr dazu weiter unten.

Als nächstes wechselt man in das Verzeichnis tsearch2/gendict. gendict hilft beim Erstellen von Wörterbüchertemplates. Genauer gesagt, bietet es Unterstützung für die Snowball Wortstämme. Und von dieser Site brauchen wir erstmal zwei Dateien:

wget http://www.snowball.tartarus.org/algorithms/german/stem.c
wget http://www.snowball.tartarus.org/algorithms/german/stem.h

Die in der obigen Doku beschriebenen Links sind inzwischen veraltet. Als nächstes ruft man folgenden Befehl auf:

./config.sh -n de -s -p german_ISO_8859_1 -i -v -c stem.c -h stem.h -C'Snowball stemmer for German'

Und das ist genau der Gag an der Sache. In obiger Doku steht nur -p german als Option. Das config funkt zunächst, aber beim kompilieren nachher fliegt einem das um die Ohren, weil er eine bestimmte Methode nicht findet. Wenn man nämlich mal in die stem.c reinguggt, dann sieht man da ganz oben gleich die Zeile

extern int german_ISO_8859_1_stem(struct SN_env * z);

Und genau das muss man bei der -p Option angeben, aber ohne das _stem hinten. Also falls es da mal beim anschließenden make; make install Probleme gibt, prüft das vorher mal. Von Mathias Behrle (siehe Kommentare unten) kam noch der Tipp, in den Dateien stem.c und stem.h german_ISO_8859_1 einfach durch german zu ersetzen. Das scheint auch zu funktionieren. Das bietet sich bei Verwendung von UTF8 als Datenbankencoding wohl auch an, da das irgendwie mit dem ISO_8859_1 nicht so gut zusammenpaßt (optisch gesehen ;-)). Der config-Befehl sieht dann so aus:

./config.sh -n de -s -p german -i -v -c stem.c -h stem.h -C'Snowball stemmer for German'

Wenn config durchgelaufen ist, wechselt man in ein anderes Verzeichnis:

cd ../../dict_de/
make
make install

Wenn das passiert ist, landet die libdict_de.so im PostgreSQL lib Verzeichnis. Als nächstes muß man jetzt erstmal die Datenbank für tsearch2 und das Wortstammbuch vorbereiten und das funkt, in dem man zwei SQL-Skripte einspielt als PostgreSQL Superuser (die muß man natürlich vorher rüberspielen):

Die Datei contrib/tsearch2/tsearch2.sql sollte man sich vorher nochmal anschauen und eventl. in der ersten Zeile den search_path anpassen. Dann:
psql -d dbname -f tsearch2.sql

Die Datei contrib/dict_de/dict_de.sql sollte man sich ebenfalls vorher nochmal anschauen und eventl. in der ersten Zeile den search_path anpassen. Dann:
psql -d dbname -f dict_de.sql

Da nun vier Tabellen als Superuser angelegt worden sind, aber man i.d.R. ja eher unter einem "normalen" Account mit weniger Rechten arbeitet, muß man nun diesem User erlauben, das er diese Tabellen bearbeiten darf:

GRANT select,insert,update,delete ON [SCHEMA].pg_ts_cfg TO [USER];
GRANT select,insert,update,delete ON [SCHEMA].pg_ts_cfgmap TO [USER];
GRANT select,insert,update,delete ON [SCHEMA].pg_ts_dict TO [USER];
GRANT select,insert,update,delete ON [SCHEMA].pg_ts_parser TO [USER];

SCHEMA kann man weglassen, wenn man sowieso alles im public Schema hat. Ansonsten gibt man halt noch den Schema-Namen davor an. So... Nun kann man sich mal unter dem User einloggen, unter dem man alles installiert hat. Folgender Befehl sollte dann unter psql schon klappen:

db=> SELECT 'Our first string used today'::tsvector;

Hier sieht man dann schon, dass der übergebene String in seine Einzelteile zerlegt wird. Als Nächstes probieren wir mal Folgendes aus:

db=> SELECT * FROM ts_debug('Our first string used today');
ERROR: could not find tsearch config by locale
CONTEXT: SQL function "_get_parser_from_curcfg" statement 1
SQL function "ts_debug" during startup

Dieser Fehler hätte mich fast zur Weißglut getrieben. Guggen wir mal in die Konfiguration:

db=> SELECT * FROM pg_ts_cfg;

Da sehen wir, das als Locale C als Default (Spalte ts_name, Eintrag default) verwendet wird. Geben wir unter psql dann mal folgende Befehle ein:

db=> SHOW lc_ctype;
lc_ctype
de_DE.iso88591
(1 Zeile)

db=> SHOW lc_collate;
lc_collate
de_DE.iso88591
(1 Zeile)

In meinem Fall also de_DE.iso88591 oder auch LATIN1 genannt. Und in der Konfigurationstabelle steht davon natürlich noch nicht's. Man muß also für das Server-Locale eine entsprechende Konfiguration erstellen. Wenn ich das richtig verstanden habe, dann sollte das Locale hier das sein, was man beim Initialisieren des Postgres-Clusters beim initdb als Paramter --locale mitgegeben hat. Gehen wir's also an...

Zunächst brauchen wir erstmal ein paar Wörterbücher. Die muß man normalerweise selber generieren, aber man kann sie auch hier downloaden:
http://www.computec.de/dload/tsearch2_german_utf8.zip

Darin enthalten sind u.a.: german.aff, german.med, german.stop und german.stop.ispell. Die Dateien reichen erstmal um weiter zu machen. Man kopiert die Dinger am Besten irgendwo hin, wo Postgres Zugriff hat. Als Nächstes brauchen wir einen Eintrag in der Konfigurationstabelle für die Wortstämme z.B.:

db=> UPDATE pg_ts_dict
db=> SET dict_initoption='/data/pgsql/share/dict/german.stop'
db=> WHERE dict_name = 'de';

Der Pfad muß natürlich entsprechend angepaßt werden, je nachdem wo man die Stopdatei hingelegt hat. In dieser Datei stehen alle Wörter, die Tsearch2 ignorieren soll. Also wenn jemand nach die, der, das suchen will, ist das sicherlich nicht sehr sinnvoll. Damit wir nun eine funktionsfähige Tsearch2 Konfiguration bekommen, sind noch ein paar weitere SQL-Statements notwendig:

INSERT INTO pg_ts_cfg(ts_name, prs_name, locale) VALUES('default_german', 'default', 'de_DE.iso88591');

INSERT INTO pg_ts_dict
   (SELECT 'de_ispell',
   dict_init,
   'DictFile="/data/pgsql/share/dict/german.med",'
    'AffFile="/data/pgsql/share/dict/german.aff",'
    'StopFile="/data/pgsql/share/dict/german.stop.ispell"',
    dict_lexize
   FROM pg_ts_dict
   WHERE dict_name = 'de_ispell');

SELECT set_curdict('de_ispell');

INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('default_german', 'lhword', '{de_ispell,de}');
INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('default_german', 'lpart_hword', '{de_ispell,de}');
INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('default_german', 'lword', '{de_ispell,de}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'word', '{de_ispell,de}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'url', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'host', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'sfloat', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'uri', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'int', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'float', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'email', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'hword', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'nlword', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'nlpart_hword', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'part_hword', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'nlhword', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'file', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'uint', '{simple}');
INSERT INTO pg_ts_cfgmap VALUES ('default_german', 'version', '{simple}');

Und damit wir unser neues Wörterbuch auch noch zum Default machen, setzen wir folgenden Befehl ab:

db=> UPDATE pg_ts_cfg SET locale='de_DE.iso88591' WHERE ts_name='default';

Wenn das durchgelaufen ist, kann man mal folgende Testquery absetzen:

db=> select to_tsvector('PostgreSQL ist weitgehend konform mit dem SQL92/SQL99-Standard, d.h. alle in dem Standard geforderten Funktionen stehen zur Verfügung und verhalten sich so, wie vom Standard gefordert; dies ist bei manchen kommerziellen sowie nichtkommerziellen SQL-Datenbanken bisweilen nicht gegeben.');
to_tsvector
* 'all':9 'bei':28 'd.h':8 'dem':6,11 'die':26 'ist':2,27 'mit':5 'sql':34 'und':18 'vom':23 'wie':22 'zur':16 'sich':20 'sowi':31 'nicht':37 'stehen':15 'gegeben':38 'konform':4 'manchen':29 'standard':12,24 'bisweilen':36 'gefordert':25 'verfügung':17 'verhalten':19 'funktionen':14 'postgresql':1 'weitgehend':3 'datenbanken':35 'geforderten':13 'kommerziellen':30 'sql-datenbanken':33 'nichtkommerziellen':32 'sql92/sql99-standard':7 (1 Zeile)*

Man sieht also hier jetzt, wie Tsearch2 den String zerlegt hat. Die Nummern dahinter sind die Positionen der Wörter im Text. So... Soweit wären wir nun also schon. Aber wir wollen natürlich nicht nur in einem Satz nach bestimmten Wörtern suchen, sondern in der Datenbank bzw. eben in verschiedenen Spalten. Nehmen wir als Beispiel eine Installation von der bekannten Forumsoftware phpBB. Dort gibt es eine Tabelle phpbb_posts_text (wenn man als Prefix bei der Installation phpbb belassen hat) die wiederum eine Spalte post_text hat. Diese Spalte ist vom Typ text. Da landen die Postings der User drin und da kann schon mal längerer Text werden. Und genau da drin wollen wir suchen können.

Zunächst fügen wir dieser Tabelle eine neue Spalte hinzu:

db==> ALTER TABLE phpbb_posts_text ADD COLUMN idxfti tsvector;

Anstatt wie bei "normalen" Indizies "hängt" dieser Index soz. an der Tabelle dran. Diese Spalte kann dann durch die Tsearch2 Operatoren und Funktionen und durch einen speziellen Index zur Volltextsuche genutzt werden. Diese Spalte heißt in diesem Fall idxfti. Es gibt einen Grundsatz, denn man einhalten sollte, wenn man einen Volltextindex erzeugt:

1. Tabelle befüllen
2. VACUUM FULL ANALYZE tablename;
3. Index erzeugen
4. VACUUM FULL ANALYZE tablename;

db==> UPDATE phpbb_posts_text SET idxfti=to_tsvector('default', post_text);
db==> VACUUM FULL ANALYZE phpbb_posts_text;

Der Funktion to_tsvector sagen wir hier, das wir die default Konfiguration verwenden wollen (die haben wir oben ja auf die deutschen Wörterbücher eingestellt) und das wir die Spalte post_text indizieren wollen. Möchte man mehrere Spalten indizieren, sieht das so aus:

db==> UPDATE phpbb_posts_text SET idxfti=to_tsvector('default',coalesce(post_text,'') ||' '|| coalesce(post_subject,''));

Nun erzeugen wir den eigentlichen Index:

db==> CREATE INDEX idxfti_idx ON phpbb_posts_text USING gist(idxfti);

Nun brauchen wir noch einen Trigger, der uns den Index auf den neuesten Stand hält, sobald ein neuer Eintrag hinzukommt:

db==> CREATE TRIGGER idxftiupdate BEFORE UPDATE OR INSERT ON phpbb_posts_text
db==> FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxfti, post_text);

Wenn man oben zwei Spalten für die Indizierung angegeben hat, sieht der Trigger etwas anders aus:

db==> CREATE TRIGGER idxftiupdate BEFORE UPDATE OR INSERT ON phpbb_posts_text
db==> FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxfti, post_text, post_subject);

Die Funktion tsearch2 akzeptiert mehrere Argumente, so das man hier die beiden Spalten nicht verknüpfen muss. Und dann war's dann auch schon... Jetzt kann man Abfragen starten bzw. man kann sich auch die Inhalte der Tabelle selber anschauen. Die neue Spalte enthält nämlich z.B. die gefundenen Wörter.

Samplequeries:

SELECT post_text FROM phpbb_posts_text WHERE idxfti @@ to_tsquery('default', 'haus & garten');

 
Links zum Thema:

Implementing Full Text Indexing with PostgreSQL
http://www.devx.com/opensource/Article/21674/0/page/3

Tsearch2 - Introduction
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html

Tsearch2 and Unicode/UTF-8 - German Unicode Datebase
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2_german_utf8.html

Gendict - generate dictionary templates for contrib/tsearch2 module
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/README.gendict

Deutsche Ispell Stop-Datei
http://hannes.imos.net/german.stop.ispell

German stemming algorithm
http://www.snowball.tartarus.org/algorithms/german/stemmer.html

Deutsche Wörterbuch und Stop-Dateien
http://www.computec.de/dload/tsearch2_german_utf8.zip

Can't find tsearch config by locale
http://blog.gmane.org/gmane.comp.db.postgresql.openfts.general/month=20040801

USING TSEARCH AND POSTGRESQL FOR A WEB BASED SEARCH ENGINE
http://www.sai.msu.su/~megera/postgres/gist/tsearch/tsearch-intro.html

Tsearch V2 Notes
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes

Tsearch V2 Optimization
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/optimization.html

Tsearch V2 compound words
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words

Posted at 01:00vorm. Apr. 02, 2006 by cetixx in Tipps  |  Kommentare [7]