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.

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?

Search Discussions

  • Martijn van Oosterhout at Apr 8, 2008 at 3:09 pm

    On Tue, Apr 08, 2008 at 07:03:07AM -0700, Gordon wrote:
    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.
    The usual trick is to check if the fields changed:

    IF OLD.title != NEW.title OR OLD.summary != NEW.summary OR ... THEN
    update_tsvector()
    END
    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?
    You can access the string sent by the client, but that won't help you
    if the trigger was triggered within a stored procedure.

    Have a nice day,
    --
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    Please line up in a tree and maintain the heap invariant while
    boarding. Thank you for flying nlogn airlines.
  • Richard Huxton at Apr 8, 2008 at 3:18 pm

    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
  • Craig Ringer at Apr 8, 2008 at 6:27 pm

    Gordon wrote:
    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?
    If you can modify the trigger and it's written in PL/PgSQL you can use a
    `RAISE NOTICE' statement to log some information when the trigger fires.
    Those are several big "if"s though.

    --
    Craig Ringer

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedApr 8, '08 at 2:25p
activeApr 8, '08 at 6:27p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase