Forum OpenACS Q&A: tsearch2 and openacs 5.4.3

Does anyone know why this has happened?
Database operation "select" failed
(exception ERROR, "ERROR: text search configuration "default" does not exist")
pqerror was: "ERROR: text search configuration "default" does not exist
"
(Status of PQexec call: PGRES_FATAL_ERROR)
SQL: select txt.object_id from txt, acs_objects o
where fti @@ to_tsquery('default','content')
and exists (select 1
from acs_object_party_privilege_map m
where m.object_id = txt.object_id
and m.party_id = '601'
and m.privilege = 'read') and o.package_id in (917) and o.object_id = txt.object_id
order by rank(fti,to_tsquery('default','content')) desc
limit 10 offset 0
The txt table is empty, ltree.sql and tsearch2.sql have been installed in template1 prior to db creation
and tsearch2.so and ltree.so are in /pg82/include/postgresql/

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

Seems as you have a problem with the version mix. The released versions of openacs support only postgres 8.2.*, the development version in head works with postgres 8.3.
I would recommend to use postgres 8.2 for the time being.

On my notebook, i have postgres 8.3 + the head version of OpenACS, search works, but the query i see is

select txt.object_id from txt, acs_objects o
 where fti @@ to_tsquery('hello')
   and exists (select 1
               from acs_object_party_privilege_map m
               where m.object_id = txt.object_id
                 and m.party_id = '539'
                 and m.privilege = 'read')
and o.package_id in (849) and o.object_id = txt.object_id
 order by ts_rank(fti,to_tsquery('hello')) desc
  limit 10   offset 0

xowiki implements the search interface via a service contract. look for FtsContentProvider and datasource in xowiki-sc-procs

I have to rush...

best regards
-gustaf neumann

Concerning categories, see: https://openacs.org/forums/message-view?message_id=2472683

Concerning search, the problem with PostgreSQL 8.3 should be gone, if you use the head version of the tsearch2-driver.