Thread: tsearch2: more than one index per table?
Is there something in tsearch2 that prevents more than one index per table?
I would like an index on field A, and a separate index on field B.
The index builds fine for A, but gives an error for B. The error text is
ERROR: could not find tsearch config by locale
The code below is taken almost verbatim from the tsearch2 documentation.
Any help is appreciated!
================================================================
\i /home/rick/ftp/postgresql-8.1.0/contrib/tsearch2/tsearch2.sql
CREATE TABLE t (a varchar(20), b varchar(20));
INSERT INTO t (a,b) VALUES ('hello world','quick brown fox');
--
-- A
--
ALTER TABLE t ADD COLUMN idxA tsvector;
UPDATE t SET idxA=to_tsvector('default', a);
VACUUM FULL ANALYZE;
CREATE INDEX idxA_idx ON t USING gist(idxA);
VACUUM FULL ANALYZE;
CREATE TRIGGER ts_A_Update BEFORE UPDATE OR INSERT ON t
FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxA, a);
--
-- B
--
ALTER TABLE t ADD COLUMN idxB tsvector;
--
-- The next line gives: ERROR: could not find tsearch config by locale
--
UPDATE t SET idxB=to_tsvector('default', b);
VACUUM FULL ANALYZE;
CREATE INDEX idxB_idx ON t USING gist(idxB);
VACUUM FULL ANALYZE;
CREATE TRIGGER ts_B_Update BEFORE UPDATE OR INSERT ON t
FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxB, b);
> ERROR: could not find tsearch config by locale
> UPDATE t SET idxA=to_tsvector('default', a);
>
Is it working
select to_tsvector('foo bar')?
I suppose, no. In that case tsearch can't find configuration for current
database locale, update pg_ts_cfg.locale in wished row to correct value.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
On Wed, 23 Nov 2005, Teodor Sigaev wrote:
>> ERROR: could not find tsearch config by locale
>> UPDATE t SET idxA=to_tsvector('default', a);
>>
>
> Is it working
> select to_tsvector('foo bar')?
>
> I suppose, no. In that case tsearch can't find configuration for current
> database locale, update pg_ts_cfg.locale in wished row to correct value.
Actually, it's described in tsearch2 introduction.
Make sure pg_ts_cfg.locale matched server's locale (see 'show all')
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
On Tuesday 22 November 2005 21:45, Rick Schumeyer wrote: > Is there something in tsearch2 that prevents more than one index per table? > > I would like an index on field A, and a separate index on field B. > > The index builds fine for A, but gives an error for B. The error text is > > > > ERROR: could not find tsearch config by locale > > This is not a problem with the index creation ... your tsearch2 installation is not configured for the locale your server is running. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html See the section "TSEARCH2 CONFIGURATION". It explains, and has examples on how to set this up. cheers, Andy
On Tue, 22 Nov 2005, Andrew J. Kopciuch wrote: > This is not a problem with the index creation ... your tsearch2 installation > is not configured for the locale your server is running. > > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html > > See the section "TSEARCH2 CONFIGURATION". It explains, and has examples on > how to set this up. I added paragraph about this problem, check my notes http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
I apologize if I'm being dense, but I'm not completely following the
explanation. It is true that my pg_ts_cfg.locale is set to en_US.UTF-8.
It was my understanding that specifying "default" as in
UPDATE t SET idxB=to_tsvector('default', b);
should give tsearch enough information.
It is not clear to me why the first time works, but not the second time
with almost identical statements.
I thought that I only had to follow the procedure in the docs if I want
to do the following:
UPDATE t SET idxB=to_tsvector(b); -- no 'default'
Perhaps I am wrong about this?
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Andrew J. Kopciuch
> Sent: Wednesday, November 23, 2005 12:08 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] tsearch2: more than one index per table?
>
> On Tuesday 22 November 2005 21:45, Rick Schumeyer wrote:
> > Is there something in tsearch2 that prevents more than one index per
> table?
> >
> > I would like an index on field A, and a separate index on field B.
> >
> > The index builds fine for A, but gives an error for B. The error text
> is
> >
> >
> >
> > ERROR: could not find tsearch config by locale
> >
> >
>
>
> This is not a problem with the index creation ... your tsearch2
> installation
> is not configured for the locale your server is running.
>
> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-
> intro.html
>
> See the section "TSEARCH2 CONFIGURATION". It explains, and has examples
> on
> how to set this up.
On Wed, 23 Nov 2005, Rick Schumeyer wrote:
> I apologize if I'm being dense, but I'm not completely following the
> explanation. It is true that my pg_ts_cfg.locale is set to en_US.UTF-8.
>
> It was my understanding that specifying "default" as in
>
> UPDATE t SET idxB=to_tsvector('default', b);
>
> should give tsearch enough information.
>
> It is not clear to me why the first time works, but not the second time
> with almost identical statements.
>
> I thought that I only had to follow the procedure in the docs if I want
> to do the following:
>
> UPDATE t SET idxB=to_tsvector(b); -- no 'default'
>
> Perhaps I am wrong about this?
nothing wrong except trigger you defined 'BEFORE UPDATE OR INSERT' fired
and it has no knowledge which configuration to use.
You may put trigger statement after update, but you'll get the same
error when inserting something to table.
Don't resist and configure tsearch2 to match server's locale.
It's most painless way.
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Andrew J. Kopciuch
>> Sent: Wednesday, November 23, 2005 12:08 AM
>> To: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] tsearch2: more than one index per table?
>>
>> On Tuesday 22 November 2005 21:45, Rick Schumeyer wrote:
>>> Is there something in tsearch2 that prevents more than one index per
>> table?
>>>
>>> I would like an index on field A, and a separate index on field B.
>>>
>>> The index builds fine for A, but gives an error for B. The error text
>> is
>>>
>>>
>>>
>>> ERROR: could not find tsearch config by locale
>>>
>>>
>>
>>
>> This is not a problem with the index creation ... your tsearch2
>> installation
>> is not configured for the locale your server is running.
>>
>> http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-
>> intro.html
>>
>> See the section "TSEARCH2 CONFIGURATION". It explains, and has examples
>> on
>> how to set this up.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
On Wednesday 23 November 2005 06:55, Rick Schumeyer wrote:
> I apologize if I'm being dense, but I'm not completely following the
> explanation. It is true that my pg_ts_cfg.locale is set to en_US.UTF-8.
>
> It was my understanding that specifying "default" as in
>
That takes the locale that is specified in the tsearch2 configuration called
"default";
take a look at this :
ftstest=# select * from pg_ts_cfg;
ts_name | prs_name | locale
-----------------+----------+--------------
default | default | C
default_russian | default | ru_RU.KOI8-R
simple | default |
custom | default |
If you don't have a record in that table that matches the server encoding
(en_US.UTF-8), you need to create a new configuration to use that locale.
That's all describe in the link I sent before.
If you have a configuration you always want to use you can just switch to that
configuration.
If you do something like this :
ftstest=# select set_curcfg('simple');
set_curcfg
------------
(1 row)
ftstest=# select to_tsvector('simple', 'a simple test');
to_tsvector
---------------------------
'a':1 'test':3 'simple':2
(1 row)
ftstest=# select to_tsvector('a simple test');
to_tsvector
---------------------------
'a':1 'test':3 'simple':2
(1 row)
ftstest=# select to_tsvector('default', 'a simple test');
to_tsvector
--------------------
'test':3 'simpl':2
(1 row)
The second example does not specify a configuration, but because you set in
the set_curcfg statement it knows to use the "simple" configuration.
> UPDATE t SET idxB=to_tsvector('default', b);
>
This forces a configuration called "default"
> UPDATE t SET idxB=to_tsvector(b); -- no 'default'
>
This doesn't tell it which configuration to use, so it tries to find one that
matches your locale (en_US.UTF-8). There probably isn't one in pg_ts_cfg, so
it gives the error.
hth,
Andy