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

["Markus Wollny" <Markus.Wollny@xxxxxxxxxxx>]

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 this case. 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 configuring the 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