Hi!
Tom Lane wrote:
> This is leaping to conclusions, but what I suspect is that you've got
> two types "tsvector" in your database and the column is the wrong
> one.
> This situation is not too hard to get into if you try to restore a
> dump from an old database that used contrib/tsearch2 --- the dump may
> create a partially broken type "public.tsvector" while the built-in
> pg_catalog.tsvector still remains.
It's a fair suspicion, but I have been in the fortunate situation to have tsearch2 installed to it's own schema in 8.2.4; so I dumped the old db without the tsearch2-schema like this, using 8.3.1's pg_dump on the new machine:
pg_dump -h123.123.123.123 -U postgres -N tsearch2 -s community > community.schema.sql
pg_dump -h123.123.123.123 -U postgres -N tsearch2 -a community -Fc > community.data.pg
Then I edited community.schema.sql, doing these two sed's:
sed -e 's/tsearch2\.tsvector/tsvector/g' community.schema.sq | \
sed -e 's/idxfti tsearch2\.gist_tsvector_ops/idxfti/g' - > community.schema.sql.83.tmp
Afterwards I replaced all the old trigger-declarations for the update-trigger with the new style, using tsvector_update_trigger.
Then I created a new 8.3-DB, imported the tsearch2-compatibility-script like this:
psql -U postgres community < /opt/pgsql/share/contrib/tsearch2.sql
And only then did I import the edited schema.
Afterwards I restored the dump like this:
pg_restore --disable-triggers -U postgres -v -Fc -d community community.data.pg
There haven't been any errors during the import, everything went fine. The restored database doesn't have a tsearch2-schema any more. I scanned through the edited schema-definiton which I imported and theres's no CREATE TYPE in there at all. I checked the public schema and there's no tsvector there either. So it must be the builtin-tsvector type alright - it seems to be there and work perfectly:
community=# select 'foo'::tsvector;
tsvector
----------
'foo'
(1 row)
community=# select to_tsvector('foo');
to_tsvector
-------------
'foo':1
(1 row)
community=# SELECT message_id FROM ct_com_board_message WHERE idxfti @@ to_tsquery('Starcraft') LIMIT 3;
message_id
------------
5669043
5671762
5670197
(3 rows)
I can even update that idxfti-column manually like so:
community=# UPDATE ct_com_board_message
SET idxfti = to_tsvector(coalesce(title,'')) ||
to_tsvector(coalesce(text,'')) ||
to_tsvector(coalesce(user_login,'')) WHERE message_id = 6575830;
UPDATE 1
And when I use a custom-trigger-function, there's no problem either:
CREATE FUNCTION board_message_trigger() RETURNS trigger AS $$
begin
new.idxfti :=
to_tsvector(coalesce(new.title,'')) ||
to_tsvector(coalesce(new.text,'')) ||
to_tsvector(coalesce(new.user_login,''));
return new;
end
$$ LANGUAGE plpgsql;
CREATE TRIGGER "tsvectorupdate"
BEFORE
INSERT OR UPDATE
ON "public"."ct_com_board_message"
FOR EACH ROW
EXECUTE PROCEDURE board_message_trigger();
community=# UPDATE ct_com_board_message set count_reply = count_reply where message_id = 6575830;
UPDATE 1
community=# SELECT message_id FROM ct_com_board_message WHERE idxfti @@ to_tsquery('markus') AND message_id = 6575830 LIMIT 3;
message_id
------------
6575830
(1 row)
So everything's working as expected apart from that built-in trigger function.
> There's some hints in the manual about safe migration from tsearch2
> to built-in tsearch:
> http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
I read that carefully before I went on that journey (that's why I did load that new contrib/tsearch2 module), but I didn't find anything helpful regarding this situation.
This is very puzzling. I'll resort to writing custom trigger-functions for the time being.
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
