Postgres Pro
Downloads
Home   >   mailing lists

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

tsvector_update_trigger throws error "column is not of tsvector type"

From
"Markus Wollny"
Date:
Hi!

I am in the process of migrating a PostgreSQL 8.2.4 database to 8.3. So far, everything has worked fine, even
tsearch2-searchingan indexed table. 

There's something severely wrong with the trigger-function I use to keep the tsvector-column updated.

Here's my table definition:

CREATE TABLE public.ct_com_board_message
(
board_id integer DEFAULT 0,
thread_id integer DEFAULT 0,
father_id integer DEFAULT 0,
message_id integer NOT NULL DEFAULT 0,
user_id integer DEFAULT 0,
title text,
signature text,
follow_up text,
count_reply integer DEFAULT 0,
last_reply timestamptz,
created timestamptz DEFAULT now(),
article_id integer DEFAULT 0,
logged_ip text,
state_id smallint DEFAULT 0,
text text,
deleted_date timestamptz,
deleted_login text,
poll_id integer DEFAULT 0,
last_updated timestamptz DEFAULT now(),
idxfti tsvector,
CONSTRAINT "pk_ct_com_board_message" PRIMARY KEY (message_id)
);

And there's this trigger definition:

CREATE TRIGGER "tsvectorupdate"
BEFORE
INSERT OR UPDATE
ON "public"."ct_com_board_message"
FOR EACH ROW
EXECUTE PROCEDURE pg_catalog.tsvector_update_trigger(idxfti,pg_catalog.german,title,text,user_login);

Now when I do anything that fires the trigger like

UPDATE ct_com_board_message set count_reply = 1 where message_id = 12345;

I get an error

ERROR:  column "idxfti" is not of tsvector type

I didn't touch the tsvector_update_trigger-function at all, it still reads

CREATE or REPLACE FUNCTION "pg_catalog"."tsvector_update_trigger"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
tsvector_update_trigger_byid
$BODY$
LANGUAGE 'internal' VOLATILE;

So what's happening here?

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

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

From
Tom Lane
Date:
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> Now when I do anything that fires the trigger like
> UPDATE ct_com_board_message set count_reply = 1 where message_id = 12345;
> I get an error
> ERROR:  column "idxfti" is not of tsvector type

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.

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

The whole thing is a bit of a mess :-(, and has certainly helped make
it clear that we need to invent some better-defined module concept to
help with major upgrades/replacements of add-on modules.

            regards, tom lane

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

From
"Markus Wollny"
Date:
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-schemaany more. I scanned through the edited schema-definiton which I imported and theres's no CREATE TYPE in
thereat 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
LIMIT3; 
 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'tfind 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

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

From
Tom Lane
Date:
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> So everything's working as expected apart from that built-in trigger function.

Well, that's just strange.  I tried to duplicate your example without
any success (ie, it worked fine) --- although it seemed you hadn't
shown the trigger definition exactly.  I got a syntax error because
'pg_catalog.german' wasn't quoted, and after that an error about
column user_login not existing.  But I assume these are just typos
in your message.

Would you confirm that

select atttypid from pg_attribute where attrelid = 'public.ct_com_board_message'::regclass and attname = 'idxfti';

gives 3614 (the preassigned OID for pg_catalog.tsvector)?

            regards, tom lane

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

From
Tom Lane
Date:
I wrote:
> Would you confirm that
> select atttypid from pg_attribute where attrelid = 'public.ct_com_board_message'::regclass and attname = 'idxfti';
> gives 3614 (the preassigned OID for pg_catalog.tsvector)?

Actually, I'll bet a nickel that you'll find it doesn't, but rather
returns the OID of the domain over tsvector that the new
contrib/tsearch2 module creates.  It's clearly a bug that the built-in
trigger doesn't allow the domain alias to be used --- will fix.

            regards, tom lane

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

From
"Markus Wollny"
Date:
Hi!

Tom Lane wrote:
> I wrote:
>> Would you confirm that
>> select atttypid from pg_attribute where attrelid =
>> 'public.ct_com_board_message'::regclass and attname = 'idxfti';
>> gives 3614 (the preassigned OID for pg_catalog.tsvector)?
>
> Actually, I'll bet a nickel that you'll find it doesn't, but rather
> returns the OID of the domain over tsvector that the new
> contrib/tsearch2 module creates.  It's clearly a bug that the
> built-in trigger doesn't allow the domain alias to be used --- will
> fix.
>
>             regards, tom lane

That nickel would be yours to keep :)

community=# select atttypid from pg_attribute where attrelid = 'public.ct_com_board_message'::regclass and attname =
'idxfti';
 atttypid
----------
 33991259
(1 row)

Concerning the table definition - sorry, I edited out a couple of fields too many, which I assumed were not relevant to
thiscase. Here is the full table definition: 

CREATE TABLE public.ct_com_board_message
(
board_id integer DEFAULT 0,
thread_id integer DEFAULT 0,
father_id integer DEFAULT 0,
message_id integer NOT NULL DEFAULT 0,
user_id integer DEFAULT 0,
title text,
signature text,
follow_up text,
count_reply integer DEFAULT 0,
last_reply timestamptz,
created timestamptz DEFAULT now(),
article_id integer DEFAULT 0,
logged_ip text,
state_id smallint DEFAULT 0,
user_login text,
user_status smallint DEFAULT 5,
user_rights text,
text text,
deleted_user_id integer DEFAULT -1,
user_rank text,
user_rank_description text,
user_rank_picture text,
deleted_date timestamptz,
deleted_login text,
user_created timestamptz,
poll_id integer DEFAULT 0,
last_updated timestamptz DEFAULT now(),
idxfti tsvector,
CONSTRAINT "pk_ct_com_board_message" PRIMARY KEY (message_id)
);

The trigger definition:
CREATE TRIGGER "tsvectorupdate"
BEFORE
INSERT OR UPDATE
ON "public"."ct_com_board_message"
FOR EACH ROW
EXECUTE PROCEDURE pg_catalog.tsvector_update_trigger(idxfti,'pg_catalog.german',title,text,user_login);

And the error message from the log:
<2008-04-09 13:42:48 CEST - 8820: 192.168.222.132(52319)@community>ERROR:  column "idxfti" is not of tsvector type
<2008-04-09 13:42:48 CEST - 8820: 192.168.222.132(52319)@community>STATEMENT:  insert into PUBLIC.CT_COM_BOARD_MESSAGE
                                (
                                  BOARD_ID
                                , THREAD_ID
                                , FATHER_ID
                                , MESSAGE_ID
                                , USER_ID
                                , TITLE
                                , TEXT
                                , SIGNATURE
                                , LOGGED_IP
                                , USER_LOGIN
                                , USER_STATUS
                                , USER_RIGHTS
                                , USER_CREATED
                                , LAST_REPLY
                                )
                                values
                                (
                                  1
                                , 6579073
                                , 0
                                , 6579073
                                , 39
                                , 'Test TSearch2 tsvector_update_trigger'
                                , 'tsvector_update_trigger test test test'
                                , ''
                                , '123.123.123.123'
                                , 'Markus_Wollny'
                                , 100
                                , 'yp'
                                , '2001-03-22 16:54:53.0'
                                , CURRENT_TIMESTAMP
                                )

Now I have a custom trigger function:

CREATE or REPLACE FUNCTION "public"."board_message_trigger"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
begin
  new.idxfti :=
     to_tsvector(coalesce(new.title,'')) ||
     to_tsvector(coalesce(new.text,'')) ||
     to_tsvector(coalesce(new.user_login,''));
  return new;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

And this trigger:
CREATE TRIGGER tsvectorupdate
       BEFORE INSERT OR UPDATE ON ct_com_board_message
       FOR EACH ROW EXECUTE PROCEDURE board_message_trigger();

Everything works fine. It's sort of less elegant though than having just the one generic trigger function and
configuringthe needed fields in the trigger itself.  

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

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

From
Tom Lane
Date:
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> Everything works fine. It's sort of less elegant though than having just the one generic trigger function and
configuringthe needed fields in the trigger itself.  

It should work if you explicitly change the column's type to
pg_catalog.tsvector.  (There's a fix in place for 8.3.2, also.)

            regards, tom lane

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

From
"Markus Wollny"
Date:
Tom Lane wrote:
> It should work if you explicitly change the column's type to
> pg_catalog.tsvector.  (There's a fix in place for 8.3.2, also.)

Which would probably be not such a good idea to try on a 7GB table in production, I think. Or is there some way hacking
thesystem catalog to correct the type instead of an ALTER TABLE ... ALTER COLUMN TYPE? 

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

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

From
Tom Lane
Date:
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> Tom Lane wrote:
>> It should work if you explicitly change the column's type to
>> pg_catalog.tsvector.  (There's a fix in place for 8.3.2, also.)

> Which would probably be not such a good idea to try on a 7GB table in production, I think. Or is there some way
hackingthe system catalog to correct the type instead of an ALTER TABLE ... ALTER COLUMN TYPE? 

Well, you could probably get away with an "update pg_attribute set
atttypid ..." but it might be safer to just wait for 8.3.2.

            regards, tom lane

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

From
"Markus Wollny"
Date:
Hi!

Tom Lane wrote:
>
> Well, you could probably get away with an "update pg_attribute set
> atttypid ..." but it might be safer to just wait for 8.3.2.

I like it safe :) Thanks for the advice!

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