trigger problem?

From: "Oren Teich" <teich(at)cobalt(dot)com>
To: <pgsql-general(at)postgreSQL(dot)org>
Subject: trigger problem?
Date: 2000-01-07 01:14:53
Message-ID: LDEIJMIHBGLDIEOIGPEHCEJNCBAA.teich@cobalt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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');

Browse pgsql-general by date

  From Date Subject
Next Message Oren Teich 2000-01-07 01:24:57 trigger problem?
Previous Message The Hermit Hacker 2000-01-06 21:30:14 Re: [GENERAL] New Search Engine ... UdmSearch