Thread: BUG #5561: Tsearch across schemas

The following bug has been logged online:

Bug reference:      5561
Logged by:          Satish Kumar
Email address:      satish.kumar@icasework.com
PostgreSQL version: 8.2
Operating system:   Ubuntu 9.04
Description:        Tsearch across schemas
Details:

Hi,

We decided to use gist text search on two columns in our database (both on
different tables) and hence decided we required the tsearch2.sql
functionalities. We ran tsearch2.sql and create index across 5 schemas.

CREATE INDEX case_attributes_text ON case_attributes USING gist
(to_tsvector(value));

CREATE INDEX appl_categories_cat1 ON appl_categories USING gist
(to_tsvector(category1));

Thing seems to work fine until we encounter an occasion error message when
running our application and trying to insert into the appl_categories or
case_attributes table.

SEVERE: org.postgresql.util.PSQLException: ERROR: No tsearch cfg with id
698563
org.postgresql.util.PSQLException: ERROR: No tsearch cfg with id 698563

On further investigation we found out the OID 698563 was on pg_ts_cfg table
of a different schema.

Is there a known issue where even though we are passing in the right
search_path to point to the right schema, the index might fail to look into
the right schema for updating itself?

Regards,

Satish
iCasework Ltd.

"Satish Kumar" <satish.kumar@icasework.com> writes:
> We decided to use gist text search on two columns in our database (both on
> different tables) and hence decided we required the tsearch2.sql
> functionalities. We ran tsearch2.sql and create index across 5 schemas.

> CREATE INDEX case_attributes_text ON case_attributes USING gist
> (to_tsvector(value));

> CREATE INDEX appl_categories_cat1 ON appl_categories USING gist
> (to_tsvector(category1));

> Thing seems to work fine until we encounter an occasion error message when
> running our application and trying to insert into the appl_categories or
> case_attributes table.

> SEVERE: org.postgresql.util.PSQLException: ERROR: No tsearch cfg with id
> 698563
> org.postgresql.util.PSQLException: ERROR: No tsearch cfg with id 698563

> On further investigation we found out the OID 698563 was on pg_ts_cfg table
> of a different schema.

contrib/tsearch2 is not designed to be used that way.  You should have
only one copy of the tsearch2 functions and tables, not one in every
schema.  I don't know whether there are bugs if you try to make use of
the tsearch2 functionality from a table in a different schema from where
the tsearch2 functions are --- but if there are, we might make an effort
to fix them.  The case you describe above is just not going to be
supportable.

            regards, tom lane