I have two doc updates I'd like to offer. I see we have two example
sections: creating rule-based dict's and creating parsers. When I was
starting I would have liked to see an example usage.
I'd like to offer: "example usage" and "Upgrading".
This is my first draft, if anyone has suggestions I'd be interested in
hearing them. Also, I'm not sure where or who to send this to, so I
hope -hacker is ok.
----- Example Usage -----
Staring a new project with Full Text Searching is easy. There is
nothing to install anymore, its all built in (in fact, don't install the
contrib module tsearch2 because it will conflict with the tsearch2 built
into the core).
We need to add a new column of type tsvector to the table you'd like to
search. In this example we'll use a table called notes. If your table
exists use:
alter table notes add searchvec tsvector;
If not use:
create table notes (
rowid integer,
note text,
searchvec tsvector
);
The searchvec column is what we will use for searching, so you probably
want to create an index on it... from another place in the manual:
(http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html)
GiST indexes are very good for dynamic data and fast if the number of
unique words (lexemes) is under 100,000,
GIN indexes are best for static data with +100,000 lexemes.
For our example, I'll create a gist index:
create index notesvec on notes using gist(searchvec);
If you have existing data, we need to fill the searchvec column:
update notes set searchvec = to_tsvector(note);
After the update, any rows are inserted or updated will not have their
searchvec column set automatically, for that we need to create a trigger:
create trigger notevecupdate
before insert or update on notes
for each row
execute procedure
tsvector_update_trigger(searchvec, 'pg_catalog.english', note);
Some data:
insert into notes(rowid, note) values(1, 'this is a test');
insert into notes(rowid, note)
values(2, 'I do not like green eggs and ham');
insert into notes(rowid, note) values(3, 'the cat in the hat');
insert into notes(rowid, note)
values(4, 'rage against the dying of the light');
And now we can query it:
select * from notes where searchvec @@ to_tsquery('light');
or
select * from notes, to_tsquery('test') as q where searchvec @@ q;
writing it this way lets you reuse the tsquery "q" like this:
select note, ts_rank(searchvec, q)
from notes, to_tsquery('test') as q
where searchvec @@ q
order by ts_rank(searchvec, q);
----- Example Usage -----
----- Upgrade from prior versions -----
When tsearch2 was put into core, some functions and types were renamed,
among other things. A simple backup and restore will not work to
migrate your database from versions below 8.3 to 8.3.
In general, the way to do it is backup all your data without the
tsearch2 stuff, restore just the data, then recreate the tsearch2 stuff
by hand.
(Its easier to think of this as an upgrade from tsearch2 to tsearch3,
but without the whole renaming it to tsearch3 thing)
To make it a little easier, there is a way using the pg_restore to
selective restore everything that is not in the old tsearch2.
First you must use "pg_dump -Fc" to backup your existing database.
Then we will create an item list of things we want to restore using this
perl script. It will strip out all the things that look like tsearch2,
and return (to stdout) a list of things you should restore.
For example:
pg_dump -Fc -h oldserver -f ubberbase.bak ubberbase
perl maketoc.pl ubberbase.bak > toc
# now restore just the items in toc
pg_restore -Fc --create -d postgres --use-list toc -f ubberbse.bak
There is one thing that will fail, that's the trigger you used to update
the tsvector column. Its because the function tsearch2 was renamed to
tsvector_update_trigger. You'll need to recreate the trigger by hand.
Now that the structures and data are restored, you'll need to go through
and redo the tsearch2 stuff by hand.
After you get the database fixed up, you'll also need to update your
client programs (php, perl, etc). For the most part, just renameing
things (like rank to ts_rank) should be all that's required.
Oleg Bartunov has an incomplete list of items that have been renamed:
http://www.sai.msu.su/~megera/wiki/Tsearch2_83_changes
----- Upgrade from prior versions -----
#!/usr/bin/perl
# pg_restore must be in the path
#
# we use pg_restore to get the toc (requires the backup be made with pg_dump
-Fc) from a backup
# create a newtoc with all the things that look like tsearch2 stuff stripped out
# so you can pg_restore -Fc -L newtoc ...
# syntax: xtract dumpfile.bak > newtoc
use strict;
my %list = ('gtsq' => 1,
'gtsq_in' => 1,
'gtsq_out' => 1,
'gtsvector' => 1,
'gtsvector_in' => 1,
'gtsvector_out' => 1,
'tsquery' => 1,
'tsquery_in' => 1,
'tsquery_out' => 1,
'tsvector' => 1,
'tsvector_in' => 1,
'tsvector_out' => 1,
'statinfo' => 1,
'tokenout' => 1,
'tokentype' => 1,
'tsdebug' => 1,
'_get_parser_from_curcfg' => 1,
'concat' => 1,
'dex_init' => 1,
'dex_lexize' => 1,
'exectsq' => 1,
'get_covers' => 1,
'gin_extract_tsquery' => 1,
'gin_extract_tsvector' => 1,
'gin_ts_consistent' => 1,
'gtsq_compress' => 1,
'gtsq_consistent' => 1,
'gtsq_decompress' => 1,
'gtsq_penalty' => 1,
'gtsq_picksplit' => 1,
'gtsq_same' => 1,
'gtsq_union' => 1,
'gtsvector_compress' => 1,
'gtsvector_consistent' => 1,
'gtsvector_decompress' => 1,
'gtsvector_penalty' => 1,
'gtsvector_picksplit' => 1,
'gtsvector_same' => 1,
'gtsvector_union' => 1,
'headline' => 1,
'length' => 1,
'lexize' => 1,
'numnode' => 1,
'parse' => 1,
'plainto_tsquery' => 1,
'prsd_end' => 1,
'prsd_getlexeme' => 1,
'prsd_headline' => 1,
'prsd_lextype' => 1,
'prsd_start' => 1,
'querytree' => 1,
'rank' => 1,
'rank_cd' => 1,
'reset_tsearch' => 1,
'rewrite' => 1,
'rewrite_accum' => 1,
'rewrite_finish' => 1,
'rexectsq' => 1,
'set_curcfg' => 1,
'set_curdict' => 1,
'set_curprs' => 1,
'setweight' => 1,
'show_curcfg' => 1,
'snb_en_init' => 1,
'snb_lexize' => 1,
'snb_ru_init_koi8' => 1,
'snb_ru_init_utf8' => 1,
'spell_init' => 1,
'spell_lexize' => 1,
'stat' => 1,
'strip' => 1,
'syn_init' => 1,
'syn_lexize' => 1,
'thesaurus_init' => 1,
'thesaurus_lexize' => 1,
'to_tsquery' => 1,
'to_tsvector' => 1,
'token_type' => 1,
'ts_debug' => 1,
'tsearch2' => 1,
'tsq_mcontained' => 1,
'tsq_mcontains' => 1,
'tsquery_and' => 1,
'tsquery_cmp' => 1,
'tsquery_eq' => 1,
'tsquery_ge' => 1,
'tsquery_gt' => 1,
'tsquery_le' => 1,
'tsquery_lt' => 1,
'tsquery_ne' => 1,
'tsquery_not' => 1,
'tsquery_or' => 1,
'tsvector_cmp' => 1,
'tsvector_eq' => 1,
'tsvector_ge' => 1,
'tsvector_gt' => 1,
'tsvector_le' => 1,
'tsvector_lt' => 1,
'tsvector_ne' => 1,
'rewrite' => 1,
'gin_tsvector_ops' => 1,
'gist_tp_tsquery_ops' => 1,
'gist_tsvector_ops' => 1,
'tsquery_ops' => 1,
'tsvector_ops' => 1,
'pg_ts_cfg' => 1,
'pg_ts_cfg_pkey' => 1,
'pg_ts_cfgmap' => 1,
'pg_ts_cfgmap_pkey' => 1,
'pg_ts_dict' => 1,
'pg_ts_dict_pkey' => 1,
'pg_ts_parser' => 1,
'pg_ts_parser_pkey' => 1
);
my @list = qx/pg_restore -Fc --list $ARGV[0]/;
#open(F,'>tmp') or die;
#print F @list;
#close(F);
foreach (@list)
{
if (/(FUNCTION|AGGREGATE)\s+(public\s+)?(.*)\(/)
{
#print "$1 | $2 | $3";
if (!exists($list{$3}))
{
print;
}
}
elsif (/(TYPE|ACL|CONSTRAINT|TABLE|TABLE DATA|OPERATOR
CLASS)\s+(public)?\s+(.*?)\s/)
{
#print "$1 | $2 | $3";
if (!exists($list{$3}))
{
print;
}
}
elsif (/(OPERATOR)\s+(public)?\s+(.*?)\s/)
{
#print "$1 | $2 | $3";
open(F, '>item.tmp') or die;
print F $_;
close(F);
my $buff = qx/pg_restore -Fc -L item.tmp $ARGV[0]/;
unlink('item.tmp');
#open(F, '>item.txt') or die;
#print F $buff;
#close(F);
if ($buff =~ /PROCEDURE = (\w+)/)
{
if (!exists($list{$1}))
{
print;
}
}
else {
print STDERR "cannot find PROCEDURE\n";
}
}
else {
print;
}
}
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
