TauCeti.NET The lost star colony...

Volltextsuche mit PostgreSQL 8.2, Tsearch2 und UTF8

So... PostgreSQL 8.2 ist jetzt doch schon eine Weile draussen. Zeit, die Tsearch2 Installationsbeschreibung zu erneuern. Wie immer ist diese Installationsanweisung 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.2.3 verwenden. Den Sourcecode von Postgres haben wir nach /opt/source/postgres/8.2.3 gelegt. Dort wechseln wir rein. Die Postgres 8.2 kompiliere ich momentan mit folgenden Befehlen:

./configure --prefix=/server/pgsql/8.2.3 --enable-nls
make
make install

Nun wechselt man in das contrib Verzeichnis. Zunächst brauchen wir einen Patch für die Stemmer (Wortstammbibliotheken). Da gab es nach der Freigabe von Postgres 8.2 Probleme, die dieser Patch löst:

wget http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch_snowball_82.gz

Dann entpacken und patchen:

gunzip tsearch_snowball_82.gz
patch -p1 < tsearch_snowball_82

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. Zunächst sollte die (eventl. zu erstellende) DB in UTF8 bzw. Unicode codiert sein. Ist die Datenbank schon installiert und man möchte überprüfen, ob die DB 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 gendict. gendict hilft beim Erstellen von Wörterbüchertemplates. Genauer gesagt, bietet es Unterstützung für die Snowball Wortstämme. NORMALERWEISE(!) würde man sich von der Snowball Seite erstmal zwei Dateien downloaden und umbenennen:

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

Das erzeugt aber bei mir dann leider folgenden Fehler, beim Kompilieren (kommt weiter unten):

stem.c: In function 'r_prelude':
stem.c:163: error: too many arguments to function 'in_grouping_U'
stem.c:168: error: too many arguments to function 'in_grouping_U'
stem.c:177: error: too many arguments to function 'in_grouping_U'
stem.c: In function 'r_mark_regions':
stem.c:212: error: too many arguments to function 'out_grouping_U'
stem.c:217: error: too many arguments to function 'in_grouping_U'
stem.c:227: error: too many arguments to function 'out_grouping_U'
stem.c:232: error: too many arguments to function 'in_grouping_U'
stem.c: In function 'r_standard_suffix':
stem.c:320: error: too many arguments to function 'in_grouping_b_U'
stem.c:347: error: too many arguments to function 'in_grouping_b_U'
make: *** [stem.o] Error 1

Also das taugt natürlich nix. Aber die stem.(c|h), die ich bei der Postgres 8.1.X verwendet habe, funktionieren noch. Falls man die noch hat, nimmt man diese. Ansonsten hab ich die Dateien hier zusammengepackt: stem.tar.gz

Wie auch immer legt man diese Dateien dann in das oben erwähnte gendict Verzeichnis. Dann führt man folgenden Befehl aus:

./config.sh -n de -s -p german_UTF_8 -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 dann passiert ist, landet die dict_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 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;

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 (die ich für Version 8.1.X verwendet habe, funktionieren nicht mehr! Das merkt man ganz schnell, wenn die Meldung ERROR:  Wrong affix file format beim Index anlegen später erscheint...):

utf8.tar.gz

Darin enthalten sind u.a.: german.aff (hiermit wird es möglich, zusammengesetzte 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.". Danach 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

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:

Tsearch2 Wiki
http://www.sai.msu.su/~megera/wiki/Tsearch2

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

Technorati Tags: postgresql, tsearch2, utf8, fulltextsearch

Posted at 11:00nachm. März 13, 2007 by cetixx in Tipps  |  Kommentare [5]