Thread: trigger problem?

-----Original Message-----
From: owner-pgsql-general@postgreSQL.org
[mailto:owner-pgsql-general@postgreSQL.org]
Sent: Thursday, January 06, 2000 5:15 PM
I'm using the fti function that is included with the source, and seem to
have run into a problem with the trigger call.

I have a full text index with only around 556645 entries.  I then have a few
triggers defined on each of the fields I'm indexing (see end of email for
part of the scheme dump).  When I create a new entry with insert, everything
is fine.  However, if I then go back and do an update, things don't go so
well.

Here's a transcript where 3 fields are filled out with the text 'asdfghjkl',
'krell', and 'bryston':

kb=> select * from article_fti where id = '2570499';
string   |     id
---------+-------
kl       |2570499
jkl      |2570499
hjkl     |2570499
ghjkl    |2570499
fghjkl   |2570499
dfghjkl  |2570499
sdfghjkl |2570499
asdfghjkl|2570499
ll       |2570499
ell      |2570499
rell     |2570499
krell    |2570499
on       |2570499
ton      |2570499
ston     |2570499
yston    |2570499
ryston   |2570499
bryston  |2570499
(18 rows)

kb=> update article_entry set keywords = 'testing keywords' where aid = 688;
UPDATE 1
kb=> select * from article_fti where id = '2570499';
string |     id
-------+-------
on     |2570499
ton    |2570499
ston   |2570499
yston  |2570499
ryston |2570499
bryston|2570499
(6 rows)

All of a sudden, only one of the fields is getting indexed!  In fact, it's
the first one that the trigger is defined on.  Am I just not understanding
triggers here?

We are running postgresql-6.5.3-1 on a 2.2.12-20smp linux box.

thanks for the help,
Oren Teich
--------------------------


CREATE TRIGGER "article_fti_trigger_question"
AFTER INSERT OR DELETE OR UPDATE
ON "article_entry"
FOR EACH ROW EXECUTE PROCEDURE fti ('article_fti', 'question');

CREATE TRIGGER "article_fti_trigger_response"
AFTER INSERT OR DELETE OR UPDATE
ON "article_entry"
FOR EACH ROW EXECUTE PROCEDURE fti ('article_fti', 'response');

CREATE TRIGGER "article_fti_trigger_comment"
AFTER INSERT OR DELETE OR UPDATE
ON "article_entry"
FOR EACH ROW EXECUTE PROCEDURE fti ('article_fti', 'comments');

CREATE TRIGGER "article_fti_trigger_keywords"
AFTER INSERT OR DELETE OR UPDATE
ON "article_entry"
FOR EACH ROW EXECUTE PROCEDURE fti ('article_fti', 'keywords');

As a follow up to my last post,
after some more testing, it appears that triggers for delete or insert work
as expected, i.e. if multiple triggers on the same table are defined they
each get called on a insert or delete.  For updates however, only the first
trigger defined on a relation seems to get called.

I defined 6 triggers - 3 "AFTER INSERT OR DELETE" and 3 "AFTER UPDATE".  All
3 triggers on the insert and delete are getting called - the data apperars
and disappears as expected in the tables.  On an update, however, all the
data is gone except for data populated by the first trigger defined.

Short of rewriting my SQL to delete/insert instead of update, is there
anything I can do?

thanks,
Oren Teich