Gordon wrote:
> I'm adding full text search to a CMS project after we upgraded to 8.3
> of Postgres. I'd like to do a bit of testing before deploying it.
>
> I added columns to the pertinent tables for storing tsvectors, and was
> looking at my query code to update it so these columns get updated
> where appropriate when I discovered triggers as a solution to let this
> all happen in a way that's completely invisible and will require no
> rewriting of the existing queries. I set up triggers to fire on
> insert or update to do the job and it all seems to work fine.
>
> But I do have one concern regarding performance. The tsvector only
> needs to be updated if the title, summary, or keywords fields have
> changed. If they are the same after an update then there is no need
> to run them. Doing so would only cause a new tsvector to be generated
> when it wasn't necessary.
You'd normally do something like:
IF (OLD.title IS DISTINCT FROM NEW.title) OR (OLD.body IS DISTINCT FROM
NEW.body) THEN...
That way you only update the tsvector if it's necessary. The "IS
DISTINCT FROM" copes with NULLs. You still have the overhead of
comparing old and new, but unless you have a very rapid rate of updates
you'll be fine.
> So what I want to know is, is there a way to tell if executing a query
> caused a trigger to fire? I don't need anything fancy like notify
> and listen, I just want to see what the database is doing for testing
> purposes. For example by looking at the logs and seeing what activity
> was caused by a given query. Does this stuff get logged?
Closest I can think is to check the pg_stat_xxx views and tables. That
will show you how many rows are accessed/updated on what tables.
--
Richard Huxton
Archonet Ltd