Stored Procedure / Trigger Strangeness - Mailing list pgsql-general
From | laotse@lumberjack.snurgle.org |
---|---|
Subject | Stored Procedure / Trigger Strangeness |
Date | |
Msg-id | Pine.LNX.4.41.0112210938150.24360-100000@lumberjack.snurgle.org Whole thread Raw |
In response to | Re: (Gurudutt <guru@indvalley.com>) |
Responses |
Re: Stored Procedure / Trigger Strangeness
|
List | pgsql-general |
Hello everyone. I apologize ahead of time for the length of this email, I wanted to provide enough detailed information to show exactly what was going on with my problem. I'm using fti.so, a full text indexing procedure from the /contrib directory of the postgresql source tree. For the most part it works wonderfully.. except that I appear to be having problems when I have multiple triggers on the same table calling fti. ------------------------------------------------------------------ CREATE TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname); CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname); CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname); ------------------------------------------------------------------- For my tests the values in the db initially are: firstname: Brian lastname: Knox screenname: brianknox1 Okay... so I check to see if "Brian" has been indexed in my fti table, which it has: select * from fti where string ~* 'Brian'; row_id | string | id --------+------------+-------- 18 | brianknox1 | 472111 22 | brian | 472111 I update the firstname and set it to Frodo: orb2=# update person set firstname = 'Frodo' where firstname = 'Brian'; UPDATE 1 Now, I look, and 'Brian' is no longer in the fti table, but neither is 'Frodo' orb2=# select * from fti where string ~* 'Frodo'; row_id | string | id --------+--------+---- (0 rows) orb2=# select * from fti where string ~* 'Brian'; row_id | string | id --------+--------+---- (0 rows) Now, watch this: if I update the field that the *first* trigger is on (lastname), then everything works fine: orb2=# update person set lastname = 'Baggins' where lastname = 'Knox'; UPDATE 1 orb2=# select * from fti where string ~* 'Baggins'; row_id | string | id --------+---------+-------- 2138 | baggins | 472111 (1 row) Now, I can verify that it is indeed the order that matters, and that only the first trigger works, by recreating the triggers in a new order: Now, the firstname trigger is first in order: CREATE TRIGGER fti_employee_firstname AFTER UPDATE OR INSERT OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE fti(fti, firstname); CREATE TRIGGER fti_employee_lastname AFTER UPDATE OR INSERT OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE fti(fti, lastname); CREATE TRIGGER fti_employee_screenname AFTER UPDATE OR INSERT OR DELETE ON person FOR EACH ROW EXECUTE PROCEDURE fti(fti, screenname); orb2=# select * from fti where string ~* 'Brian'; row_id | string | id --------+------------+-------- 18 | brianknox1 | 478636 22 | brian | 478636 orb2=# update person set firstname = 'Frodo' where firstname = 'Brian'; UPDATE 1 orb2=# select * from fti where string ~* 'Frodo'; row_id | string | id --------+--------+-------- 2110 | frodo | 485133 orb2=# select * from fti where string ~* 'Brian'; row_id | string | id --------+--------+---- (0 rows) ========================================================================== NOTE: I notice that even when I update the first field that is run through the fti() routine and it updates successfully, that the last two fields are deleted from the fti search table. Here is the information from the FTI table before and after and update: orb2=# select oid from person where firstname = 'Brian'; oid -------- 498130 orb2=# select * from fti where id = '498130'; row_id | string | id --------+------------+-------- 1 | et | 498130 2 | net | 498130 3 | ol | 498130 4 | aol | 498130 5 | se | 498130 6 | tse | 498130 7 | otse | 498130 8 | aotse | 498130 9 | laotse | 498130 10 | x1 | 498130 11 | ox1 | 498130 12 | nox1 | 498130 13 | knox1 | 498130 14 | nknox1 | 498130 15 | anknox1 | 498130 16 | ianknox1 | 498130 17 | rianknox1 | 498130 18 | brianknox1 | 498130 19 | ox | 498130 20 | nox | 498130 21 | knox | 498130 22 | an | 498130 23 | ian | 498130 24 | rian | 498130 25 | brian | 498130 (25 rows) orb2=# update person set firstname = 'Frodo' where firstname = 'Brian'; UPDATE 1 orb2=# select * from fti where id = '498130'; row_id | string | id --------+--------+-------- 2107 | do | 498130 2108 | odo | 498130 2109 | rodo | 498130 2110 | frodo | 498130 (4 rows) Notice that the other two fields, which were indexed in the fti table (lastname, and screenname) are gone. Anyone have any idea what might be going on? Is there anyone on the list that is very familiar with the fti.so module, or who can spot something obviously wrong in the way I'm using my triggers? I'm completely stumped. Brian Knox laotse@lumberjack.snurgle.org ----------------------------------------------------------------------------- Brian Knox Just Another Perl Hacker perl -le '$_="6110>374086;2064208213:90<307;55";tr[0->][ LEOR!AUBGNSTY];print'
pgsql-general by date: