Forum OpenACS Q&A: Re: tsearch2 and PostgreSQL 8.3

Collapse
Posted by Richard Hamilton on

Ok, got it. This is an issue that arises because the latest version of PostgreSQL includes tsearch2 as an integrated feature. There have been changes and although it has been made possible to upgrade existing databases, I was starting with a blank db based upon template1 into which tsearch.sql had been loaded.

I couldn't work out why the tables that should be there were not there. The reason? They've changed the names.

The table containing the tsearch configurations used to be called pg_ts_cfg and was part of the 'public' schema. It is now called pg_ts_config and it is part of the 'pg_catalog' schema.

In addition it helpfully doesn't contain the 'default' configuration that the openacs tsearch2 driver specifies in the calls to the 'to_tsquery()' function. Consequently we get this error.

The fix is to manually add the rows as follows however perhaps we should add a parameter to the tsearch driver to allow the configuration to be specified:

mydb=# select * from pg_ts_config;
  cfgname   | cfgnamespace | cfgowner | cfgparser 
------------+--------------+----------+-----------
 simple     |           11 |       10 |      3722
 danish     |           11 |       10 |      3722
 dutch      |           11 |       10 |      3722
 english    |           11 |       10 |      3722
 finnish    |           11 |       10 |      3722
 french     |           11 |       10 |      3722
 german     |           11 |       10 |      3722
 hungarian  |           11 |       10 |      3722
 italian    |           11 |       10 |      3722
 norwegian  |           11 |       10 |      3722
 portuguese |           11 |       10 |      3722
 romanian   |           11 |       10 |      3722
 russian    |           11 |       10 |      3722
 spanish    |           11 |       10 |      3722
 swedish    |           11 |       10 |      3722
 turkish    |           11 |       10 |      3722
(16 rows)

mydb=# insert into pg_ts_config (cfgname,cfgnamespace,cfgowner,cfgparser) VALUES ('default',11,10,3722);                   
INSERT 33987 1
mydb=# select * from pg_ts_config;
  cfgname   | cfgnamespace | cfgowner | cfgparser 
------------+--------------+----------+-----------
 simple     |           11 |       10 |      3722
 danish     |           11 |       10 |      3722
 dutch      |           11 |       10 |      3722
 english    |           11 |       10 |      3722
 finnish    |           11 |       10 |      3722
 french     |           11 |       10 |      3722
 german     |           11 |       10 |      3722
 hungarian  |           11 |       10 |      3722
 italian    |           11 |       10 |      3722
 norwegian  |           11 |       10 |      3722
 portuguese |           11 |       10 |      3722
 romanian   |           11 |       10 |      3722
 russian    |           11 |       10 |      3722
 spanish    |           11 |       10 |      3722
 swedish    |           11 |       10 |      3722
 turkish    |           11 |       10 |      3722
 default    |           11 |       10 |      3722
(17 rows)

mydb=# 

The search now works but I still haven't worked out why my txt table remains empty despite my having installed the xowiki package with tsearch installed and edited a wiki page to add text to index.

How does xowiki decide whether tsearch2 is installed? Does it look for a table that has been renamed by the PostgreSQL dev team?

Regards Richard