Thread: ERROR: text search configuration "pg_catalog.english" does not exist
Hi, I am trying to configure Full Text Search on PostgreSQL 8.3 but I seem to be missing pg_catalog.english as I get the follow when I try and do this:
ALTER TABLE useraccounts_contact ADD COLUMN notes_tsv tsvector;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON useraccounts_contact FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(body_tsv, 'pg_catalog.english', notes);
CREATE INDEX useraccounts_contact_entry_tsv ON useraccounts_contact USING gin(notes_tsv);
UPDATE useraccounts_contact SET notes_tsv =to_tsvector(notes);
Error:
ERROR: text search configuration "pg_catalog.english" does not exist
SQL state: 42704
In my postgresql.conf I have the following:
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
Thanks in advance.
Tim Child
Tim Child wrote: > Hi, I am trying to configure Full Text Search on PostgreSQL 8.3 but I > seem to be missing pg_catalog.english as I get the follow when I try and > do this: > tsvector_update_trigger(body_tsv, 'pg_catalog.english', notes); > Error: > > ERROR: text search configuration "pg_catalog.english" does not exist > SQL state: 42704 The \dF commands show your settings (in psql) - what does \dF show for you? You might want to check your dictionaries are all there with \dFd too. -- Richard Huxton Archonet Ltd
Tim Child <tim@timc3.com> writes:
> Hi, I am trying to configure Full Text Search on PostgreSQL 8.3 but I
> seem to be missing pg_catalog.english as I get the follow when I try
> and do this:
That's odd ... what *do* you have in pg_ts_config? It should look
about like this, in a virgin database:
postgres=# 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)
postgres=#
regards, tom lane
If I do \dF:
Schema | Name | Description
------------+--------+----------------------
pg_catalog | simple | simple configuration
(1 row)
\dFd:
Schema | Name | Description
------------+--------
+-----------------------------------------------------------
pg_catalog | simple | simple dictionary: just lower case and check
for stopword
And for select * from pg_ts_config the only row shows:
simple |11 | 10 | 3722
Is there away of getting the extra catalogues in?
Thanks,
Tim.
On 11 Mar 2008, at 22:25, Tom Lane wrote:
> Tim Child <tim@timc3.com> writes:
>> Hi, I am trying to configure Full Text Search on PostgreSQL 8.3 but I
>> seem to be missing pg_catalog.english as I get the follow when I try
>> and do this:
>
> That's odd ... what *do* you have in pg_ts_config? It should look
> about like this, in a virgin database:
>
> postgres=# select * from pg_ts_config;
> cfgname | cfgnamespace | cfgowner | cfgparser
> ------------+--------------+----------+-----------
Tim Child <tim@timc3.com> writes:
> If I do \dF:
> Schema | Name | Description
> ------------+--------+----------------------
> pg_catalog | simple | simple configuration
> (1 row)
Huh. Seems like initdb forgot to install all the Snowball stemmers.
What do you find in $SHAREDIR/snowball_create.sql? (If you're not
sure where your SHAREDIR is, pg_config --sharedir will tell you.)
Where did this build of Postgres come from, anyway?
regards, tom lane
Tim Child <tim@timc3.com> writes:
> In my snowball_create.sql I find:
> -- No language-specific snowball dictionaries, for lack of shared
> library support
Really!?
> This build comes from postgresqlformac.com the "Unified Installer -
> 8.3.0 (PostgreSQLforMac)"
Hm, do they have plpgsql or any of the other PLs? It's hard to credit
that anyone would put out a build without shared library support.
There's too much functionality that goes missing.
> Now I am considering that it might have been better to compile my own,
> but before I do this is there anyway to get the libraries /
> dictionaries installed?
What you need is a non-lobotomized build. You should be able to fix the
problem without re-initdb, if you need to, by running the real
snowball_create.sql script against each of your databases.
regards, tom lane
Tim Child <tim@timc3.com> writes:
> There is a file here with the same name:
> /Library/PostgreSQL8/lib/postgresql/plpgsql.so
What else is in that directory? If you don't have dict_snowball.so
it's not gonna work.
> So I could try and run that snowball_create.sql script from src (which
> I have downloaded) and try and get the required functionality?
What I suspect at this point is that postgresqlformac.com hack up the
build process for .so's in some weird way, and that they neglected to
apply their hack to the Snowball stemmer library. You should report
that to them as a bug in their packaging.
regards, tom lane
Tim Child <tim@timc3.com> writes:
> There is other things in that directory including dict_snowball.so
Well, that's even stranger. It's real hard to see how the
src/backend/snowball Makefile would have built dict_snowball.so
and not built the correct version of snowball_create.sql.
Anyway, if you can get the correct snowball_create.sql script from
another machine, you should be able to limp along.
regards, tom lane
There is other things in that directory including dict_snowball.so ascii_and_mic.so latin2_and_win1250.so utf8_and_big5.so utf8_and_gbk.so cyrillic_and_mic.so latin_and_mic.so utf8_and_cyrillic.so utf8_and_iso8859.so dict_snowball.so pgxs utf8_and_euc_cn.so utf8_and_iso8859_1.so euc_cn_and_mic.so plperl.so utf8_and_euc_jis_2004.so utf8_and_johab.so euc_jis_2004_and_shift_jis_2004.so plpgsql.so utf8_and_euc_jp.so utf8_and_shift_jis_2004.so euc_jp_and_sjis.so plpython.so utf8_and_euc_kr.so utf8_and_sjis.so euc_kr_and_mic.so pltcl.so utf8_and_euc_tw.so utf8_and_uhc.so euc_tw_and_big5.so utf8_and_ascii.so utf8_and_gb18030.so utf8_and_win.so I have already put in a bug report to them, but have no idea how active the maintainer is in building new versions. Thanks, Tim On 12 Mar 2008, at 18:04, Tom Lane wrote: > Tim Child <tim@timc3.com> writes: >> There is a file here with the same name: >> /Library/PostgreSQL8/lib/postgresql/plpgsql.so > > What else is in that directory? If you don't have dict_snowball.so > it's not gonna work. > >> So I could try and run that snowball_create.sql script from src >> (which >> I have downloaded) and try and get the required functionality? > > What I suspect at this point is that postgresqlformac.com hack up the > build process for .so's in some weird way, and that they neglected to > apply their hack to the Snowball stemmer library. You should report > that to them as a bug in their packaging. > > regards, tom lane
There is a file here with the same name: /Library/PostgreSQL8/lib/postgresql/plpgsql.so So I could try and run that snowball_create.sql script from src (which I have downloaded) and try and get the required functionality? That would be the easiest for now. On the production system I will compile my own PostgreSQL but for now I wanted a minimum of fuss setup ;-) Thanks again for all the help. Tim. On 12 Mar 2008, at 17:03, Tom Lane wrote: > Tim Child <tim@timc3.com> writes: >> In my snowball_create.sql I find: >> -- No language-specific snowball dictionaries, for lack of shared >> library support > > Really!? > >> This build comes from postgresqlformac.com the "Unified Installer - >> 8.3.0 (PostgreSQLforMac)" > > Hm, do they have plpgsql or any of the other PLs? It's hard to credit > that anyone would put out a build without shared library support. > There's too much functionality that goes missing. > >> Now I am considering that it might have been better to compile my >> own, >> but before I do this is there anyway to get the libraries / >> dictionaries installed? > > What you need is a non-lobotomized build. You should be able to fix > the > problem without re-initdb, if you need to, by running the real > snowball_create.sql script against each of your databases. > > regards, tom lane
In my snowball_create.sql I find:
-- No language-specific snowball dictionaries, for lack of shared library support
This build comes from postgresqlformac.com the "Unified Installer - 8.3.0 (PostgreSQLforMac)"
Now I am considering that it might have been better to compile my own, but before I do this is there anyway to get the libraries / dictionaries installed?
Thanks again,
Tim
On 12 Mar 2008, at 16:07, Tom Lane wrote:
Tim Child <tim@timc3.com> writes:If I do \dF:Schema | Name | Description------------+--------+----------------------pg_catalog | simple | simple configuration(1 row)
Huh. Seems like initdb forgot to install all the Snowball stemmers.
What do you find in $SHAREDIR/snowball_create.sql? (If you're not
sure where your SHAREDIR is, pg_config --sharedir will tell you.)
Where did this build of Postgres come from, anyway?
regards, tom lane