Re: tsvector_update_trigger throws error "column is not of tsvector type"

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