HOWTO install tsearch2 for mediawiki on postgres (wikipgedia)

I just got wikipgedia 0.0.4 installed and working, and it is running
sweeter than a horses arse at the Ascot races. tsearch2 gave me some
troubles, but they now seem to be resolved. For those of you new to
postgres or tsearch, I'll show you how I did it.

This HOWTO assumes the following:

You are running Debian unstable and you have the Postgres 8.1 client,
server, and contrib packages installed and running.

Create your database "wikidb" with owner "wikiadmin" and the schema
"mediawiki".

$ su - postgres -c "psql template1"
template1=# CREATE USER wikiadmin WITH PASSWORD "somepassword";
template1=# CREATE DATABASE wikidb WITH OWNER wikiadmin;
template1=# \c wikidb
wikidb=# CREATE SCHEMA mediawiki;
wikidb=# \i /usr/share/postgresql/8.1/contrib/tsearch2.sql

The \c command in psql connects you to the wikidb database.

The \i command in psql "includes" the named file, executing all the SQL
commands in the file as if you had typed them in.

Notice we didn't give the "wikiadmin" account superuser powers inside
postgres. For security reasons, I don't recommend it. So you need to
install tsearch2 into the wikidb database as the user "postgres", the
default superuser account. The commands above accomplish that for you.

When I did this I noticed various errors that worried me. Everything
seems to work, but I'd prefer a version of tsearch2 that didn't spit out
those errors during install. They seem harmless, so I will reproduce
them here, in case any googler shares my anxiety:

### TSORT2 INSTALL ERROR MESSAGES ###
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:13:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_dict_pkey" for table "pg_ts_dict"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:145:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_parser_pkey" for table "pg_ts_parser"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:244:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_cfg_pkey" for table "pg_ts_cfg"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:251:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_cfgmap_pkey" for table "pg_ts_cfgmap"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:337:
NOTICE: type "tsvector" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:342:
NOTICE: argument type tsvector is only a shell
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:396:
NOTICE: type "tsquery" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:401:
NOTICE: argument type tsquery is only a shell
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:543:
NOTICE: type "gtsvector" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:548:
NOTICE: argument type gtsvector is only a shell
### END OF ERROR MESSAGES ###

Once tsearch2 was installed, I went through the steps to getting
wikipgedia set up through the web browser. It seemed to work. The main
page popped up. I clicked the Edit link. Clicked the Save button.

Horror!

### EDIT ERROR MESSAGES ###
Warning: pg_query(): Query failed: ERROR: column "si_title" of relation
"searchindex" does not exist in
/my/path/to/html/wiki/includes/DatabasePostgreSQL.php on line 98
A database error has occurred Query: INSERT INTO searchindex
(si_page,si_title,si_text) VALUES ( 1, to_tsvector('main
page'),to_tsvector(' wiki software successfully installed please see
documentation on customizing the interface and the user user''s guide
for usage and configuration help test test test second test; see her
knickers in a knot sh bm bm bm one more time for the howto ')) Function:
SearchTsearch2:update Error: 1 ERROR: column "si_title" of relation
"searchindex" does not exist

Backtrace:

* GlobalFunctions.php line 500 calls wfbacktrace()
* DatabasePostgreSQL.php line 573 calls wfdebugdiebacktrace()
* Database.php line 383 calls databasepostgresql::reportqueryerror()
* SearchTsearch2.php line 116 calls databasepostgresql::query()
* SearchUpdate.php line 103 calls searchtsearch2::update()
* index.php line 270 calls searchupdate::doupdate()
### END OF EDIT ERROR MESSAGES ###

Finally, a tsearch2 webpage mentioned something about granting search
privileges to some of the tsearch2 tables. So I did this, first
assuming the powers of the postgres account:

$ su - postgres -c "psql wikidb"
wikidb=# GRANT SELECT ON pg_ts_dict to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_parser to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_cfg to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_cfgmap to wikiadmin;

After doing this, wikipgedia worked like a champ. Edit was fast and
snappy and gave no errors.

Kudos to the wikipgedia team. If only every software package was so
easy to install. A pity I am no longer able to package it up and
include it in Debian.