Volltextsuche mit PostgreSQL 8.1, Tsearch2 und UTF8
Update 14.03.2007: Für PostgreSQL 8.2 habe ich ein neues Tutorial erstellt: PostgreSQL 8.2, Tsearch2 und UTF8
Update 27.10.2006: Ich habe ein paar Sachen geändert/hinzugefügt. Ich gehe noch etwas genauer auf das PGCLIENTENCODING ein, das ziemlich wichtig ist. Seit PostgreSQL 8.1.5 scheint man auch den Tsearch2-Patch nicht mehr zu brauchen und ich benutze jetzt andere Stemmer-Library's.
Die
Tsearch2-Entwickler haben den UTF8-Support von Postgres 8.2 auf 8.1.x
rückportiert. D.h. UTF8 kann man nun auch unter 8.1.x einsetzen. Den
Patch findet man hier: tsearch2.82.tar.gz (Tsearch2 Entwicklerseite). Dieser ist aber mittlerweile auch wieder veraltet. Ich möchte aber doch noch darauf hingewiesen haben, falls jemand noch Versionen < 8.1.5 verwendet oder Probleme beim installieren hat. In diesem Fall könnte man es mit diesem Patch versuchen.
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.5 verwenden.
Den Sourcecode von Postgres haben wir nach /opt/source/postgres gelegt.
Dort drin gibt es ein contrib Verzeichnis in welches man
reinwechselt.
Hat man nun Postgres 8.1.5, kann man sich folgenden Abschnitt nach meiner momentanen Erkenntnis sparen. Am Besten weiterlesen ab "Dann wechseln wir in...". Die Postgres-Installation für 8.1.4 und UTF8 hat bei mir nur mit dem erwähnten Patch funktioniert. Holen wir uns also den UTF8 Patch für PostgreSQL 8.1.x herunter und legen ihn ins contrib-Verzeichnis:
cd /opt/source/postgres/contrib
wget http://www.sai.msu.su/%7Emegera/postgres/gist/tsearch/V2/tsearch2.82.tar.gz
tar xvfz tsearch2.82.tar.gz
Dann benennen wir das alte tsearch2-Verzeichnus um, und legen auf das neue Verzeichnis einen Softlink (das ist wichtig, sonst kompiliert das nachher nicht!):
mv tsearch2 tsearch2.wo_utf8_support
ln -s tsearch2.82 tsearch2
Dann wechseln wir in das Verzeichnis tsearch2. 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, die wir gleich besprechen. Um zu überprüfen, ob die DB auch mit UTF8 Encoding angelegt wurde, connected man sich auf den Datenbankcluster per psql und läßt sich mit \l alle Datenbanken anzeigen (oder psql -l). Wenn in der letzten Spalte UTF8 steht, paßt's.
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. Für Postgres 8.1.4 hatte ich bisher immer diese Dateien verwendet:
wget http://www.snowball.tartarus.org/algorithms/german/stem.c
wget http://www.snowball.tartarus.org/algorithms/german/stem.h
wget http://www.snowball.tartarus.org/algorithms/german/stem_UTF_8.c
wget http://www.snowball.tartarus.org/algorithms/german/stem_UTF_8.h
Hat man die stem_UTF_8.(c|h) runtergeladen, benennt man sie nun um in stem.c und stem.h.
Bei Postgres 8.1.4 habe ich nun in den Dateien alle Vorkommen von german_ISO_8859_1 durch german ersetzt. Bei Postgres 8.1.5 (stem_UTF_8.(c|h)) habe ich die Dateien unverändert gelassen. Nun folgenden Befehl ausführen:
Postgres 8.1.4:
./config.sh -n de -s -p german -i -v -c stem.c -h stem.h -C'Snowball stemmer for German'
Postgres 8.1.5:
./config.sh -n de -s -p german_UTF_8 -i -v -c stem.c -h stem.h -C'Snowball stemmer for German'
cd ../../dict_de
Nun müßte man eigentlich mit make alle kompilieren. Neuerdings fliegt mir aber immer das Teil um die Ohren und behauptet, die Methode german_UTF_8_close_env hätte ein Argument zu viel. Deshalb ändere ich die Zeile (ganz unten in der Datei stem.c):
extern void german_UTF_8_close_env(struct SN_env * z) { SN_close_env(z, 0); }
in
extern void german_UTF_8_close_env(struct SN_env * z) { SN_close_env(z); }
Dann kann man den make starten:
make
make install
Es fliegen einem dann zwar ein paar Warnings um die Ohren, aber die scheinen nicht weiter schlimm zu sein. Wenn das dann passiert ist, landet die dict_de.so im PostgreSQL
lib Verzeichnis. Ab hier ist dann für alle Postgres-Versionen alles gleich. Ich schreib das hier nur alles auf, damit es jemanden vielleicht weiterhilft. Das scheint aber bei jedem anders zu sein...
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, muss 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 (wobei es ja eigentlich kein Fehler im eigentlichen Sinne ist, sondern ein Konfigurationsfehler meinerseits) hätte mich fast zur Weißglut getrieben. Guggen wir mal in die Konfiguration:
db=> SELECT * FROM pg_ts_cfg;
| ts_name | prs_name | locale |
| default | default | C |
| default_russian | default | ru_RU.KOI8-R |
| simple | default |
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.utf8@euro
(1 Zeile)
db=> SHOW lc_collate;
lc_collate
de_DE.utf8@euro
(1 Zeile)
In meinem Fall also de_DE.utf8@euro. 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 Dateien. Die muß man normalerweise selber generieren, aber ich hab sie mal zusammengepackt:
tsearch2_utf8_required_files.tar.gz
Darin enthalten sind u.a.: german.aff (hiermit wird es möglich, die zusammengesetzten Wörter sog. compound words aufzulösen, also z.B. Tischkante), german.med (das Wörterbuch an sich), german.stop und german.stop.ispell (zwei mögliche Dateien mit Stopwörtern also "der, die, das, usw.". Dann zu suchen, macht wenig Sinn...). Die Dateien reichen erstmal um weiter zu machen. Man kopiert die Dinger am Besten irgendwo hin, wo Postgres Zugriff hat. Das konvertieren an sich, ist auch nicht allzu schwer, wenn man's selber machen will. Wenn man die entsprechenden Dateien im Latin1-Encoding schon hat, konvertiert man sie einfach wie folgt:
iconv -f iso-8859-1 -t utf-8 -o german_utf8.med german.med
iconv -f iso-8859-1 -t utf-8 -o german_utf8.aff german.aff
iconv -f iso-8859-1 -t utf-8 -o german_utf8.stop german.stop
iconv -f iso-8859-1 -t utf-8 -o german_utf8.stop.ispell german.stop.ispell
Da ich auch noch Datenbanken mit Latin1 Encoding habe, gibt es bei mir zwei Unterordner - einen für die Dateien mit Latin1 Encoding und die anderen eben für UTF8.
Damit wir nun eine funktionsfähige Tsearch2 Konfiguration bekommen, sind noch ein paar weitere SQL-Statements notwendig. Zunächst benennen wir den default-Tsearch2-Eintrag mal um, da wir ja unseren eigenen verwenden wollen:
UPDATE pg_ts_cfg SET ts_name='default_c' WHERE prs_name='default' AND locale='C';
Dann fügen wir unsere Defaults ein:
db=> INSERT INTO pg_ts_cfg(ts_name, prs_name, locale) VALUES('default', 'default', 'de_DE.utf8@euro');
Bevor man nun weitermacht, sollte man noch das richtige Clientencoding einstellen. Das dürfte i.d.R. LATIN1 sein, wenn man auf der Shell arbeitet und ist für später wichtig, wenn wir die Testquery ausführen wollen. Das Clientencoding setzt man auf der Shell mit
export PGCLIENTENCODING=LATIN1
Wenn euch Meldungen wie
ERROR: invalid byte sequence for encoding "UTF8": 0xe4fcf6
oder
ERROR: Affix parse error at 644 line
um die Ohren fliegen, liegt das mit gaaaaanz hoher Wahrscheinlichkeit an einem falsch eingestellten Encoding. Bei PostgreSQL 8.1.5 sind die Meldungen etwas genauer. Bei letzterer Meldung war z.B. das Encoding der german_utf8.aff Datei kein "echtes" UTF8. Bei der ersten Meldung hab ich auf der psql-Shell gearbeitet und unten stehende Testquery ausgeführt, die Umlaute enthielt. Also immer mal auf das Encoding aufpassen!
Dann fügen wir mal die entscheidenste Konfiguration ein:
db=> INSERT INTO pg_ts_dict
(SELECT 'de_ispell', dict_init,
'DictFile="/data/pgsql/share/dict/utf8/german_utf8.med",'
'AffFile="/data/pgsql/share/dict/utf8/german_utf8.aff",'
'StopFile="/data/pgsql/share/dict/utf8/german_utf8.stop.ispell"',
dict_lexize
FROM pg_ts_dict
WHERE dict_name = 'ispell_template');
Dann stellen wir ein, welches Wörterbuch wir verwenden wollen:
db=> SELECT set_curdict('de_ispell');
oder
db=> SELECT set_curdict(DIE_NUMMER_DIE_DER_VORHERGEHNDE_INSERT_AUSSPUCKT);
Ich habe festgestellt, das letzter Befehl beim Debuggen gar nicht so schlecht ist, da er wesentlich mehr Info's ausspuckt. Als nächstes müssen wir dann noch angeben, für welche Wörter wir welches Wörterbuch verwenden wollen:
db=> DELETE FROM pg_ts_cfgmap WHERE ts_name='default' AND dict_name='{en_stem}';
INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('default', 'lhword', '{de_ispell,simple}');
INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('default', 'lpart_hword', '{de_ispell,simple}');
INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('default', 'lword', '{de_ispell,simple}');
Bei dem Delete sollten drei Einträge rausfliegen. {de_ispell,simple} besagt, das wir zunächst das ISpell-Wörterbuch verwenden wollen und dann das Simple-Wörterbuch. Ohne das Simple-Wörterbuch würde das Suchergebnis erheblich kleiner sein und viele einfache Wörter würden aussen vor bleiben. Ein noch besseres Ergebnis dürften die OpenOffice-Wörterbücher bringen. Das muss ich bei Gelegenheit mal ausprobieren. 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.
Beispielquery's:
Einfache Suche:
SELECT post_text FROM phpbb_posts_text WHERE idxfti @@ to_tsquery('default', 'haus');
Suche mit UND:
SELECT post_text FROM phpbb_posts_text WHERE idxfti @@ to_tsquery('default', 'haus & garten');
Suche mit ODER:
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
TSearch2 / German compound words / UTF-8
http://archives.postgresql.org/pgsql-general/2005-11/msg01113.php
Posted at 12:00vorm. Aug. 23, 2006 by cetixx in Tipps | Kommentare [0]