strange behavior in table column in postgres

I am currently using postgres 8.3. I have created a table that acts as a dirty flag table for members that exist in another table. I have applied triggers after insert or update on the members table that will insert/update a record on the modifications table with a value of true. The trigger seems to work, however I am noticing that something is flipping the boolean is_modified value. I have no idea how to go about trying to isolate what could be flipping it.

Trigger function:

 BEGIN;
     CREATE OR REPLACE FUNCTION set_member_as_modified() RETURNS TRIGGER AS $set_member_as_modified$
  BEGIN
LOOP
  -- first try to update the key
  UPDATE member_modification SET is_modified = TRUE, updated = current_timestamp WHERE "memberID" = NEW."memberID";
  IF FOUND THEN
    RETURN NEW;
  END IF;
  --member doesn't exist in modification table, so insert them
  -- if someone else inserts the same key conncurrently, raise a unique-key failure
  BEGIN
    INSERT INTO  member_modification("memberID",is_modified,updated) VALUES(NEW."memberID", TRUE,current_timestamp);
    RETURN NEW;
  EXCEPTION WHEN unique_violation THEN
    -- do nothing, and loop to try the update again
  END;
END LOOP;
  END;
$set_member_as_modified$ LANGUAGE plpgsql;
COMMIT;

CREATE TRIGGER set_member_as_modified AFTER INSERT OR UPDATE ON members FOR EACH ROW EXECUTE PROCEDURE set_member_as_modified();

Here is the sql I run and the results:

     $CREATE TRIGGER set_member_as_modified AFTER INSERT OR UPDATE ON members FOR EACH ROW EXECUTE PROCEDURE set_member_as_modi开发者_Go百科fied(); 

Results:

     UPDATE 1
     bluesky=# select * from member_modification;
    -[ RECORD 1 ]---+---------------------------
 modification_id | 14
 is_modified     | t
 updated         | 2011-05-26 09:49:47.992241
 memberID        | 182346

bluesky=# select * from member_modification;
-[ RECORD 1 ]---+---------------------------
modification_id | 14
is_modified     | f
updated         | 2011-05-26 09:49:47.992241
memberID        | 182346

As you can see something flipped the is_modified value. Is there anything in postgres I can use to determine what queries/processes are acting on this table?

Are you sure you've posted everything needed? The two queries on member_modification suggest that a separate query is being run in between, which sets is_modified back to false.

You could add an text[] field to member_modification, e.g. query_trace text[] not null default '{}', then and a before insert/update trigger on each row on that table which goes something like:

NEW.query_trace := NEW.query_trace || current_query();

If current_query() is not available in 8.3, see this:

http://www.postgresql.org/docs/8.3/static/monitoring-stats.html

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
       pg_stat_get_backend_activity(s.backendid) AS current_query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

You could then get the list of subsequent queries that affected it:

select query_trace[i] from generate_series(1, array_length(query_trace, 1)) as i