Orphaned event_output after event completion

[Bill Ott]

I'd found cases of action_trigger.event rows being in state "complete", but having a null template_output column. Some digging based on complete_time led me to action_trigger.event_output rows with a matching create_time.

I discovered from logs that the output was getting properly updated, but subsequently returned to a null. I'm guessing a race condition complements of PG_Pool.

I'm not certain if there's an event where the template_output would ever need to go from a non-null integer back to null, but my initial plan is to create an update trigger that will prevent the template_output from returning to null once set.

[Mike Rylander (mrylander)]

Bill,

Can you list the order of operations that you think might be causing this?

If PGPool (or some other load balancer) is required to make this happen, it seems this may be an upstream bug.

--miker

[Bill Ott (bott)]

Indeed, PGPool is likely the underlying issue. It appears that even within a transaction, the SELECT, is pulling from a replica and missing the very recent UPDATE. I do not have a Postgres log of the problem, but I have watched action_triggers to follow the pattern occurring:
   http://pastie.org/pastes/10908453/text

From an osrfsys.log, I can see the rapid update occurring, where the state changes, and the template_output appears in the 2nd "reacting" state, then returns to null in the "reacted" state.
   http://pastie.org/pastes/10908383/text

I am currently testing a trigger on action_trigger.event, where BEFORE UPDATE, I perform the following:
  IF NEW.template_output IS null THEN
    NEW.template_output = OLD.template_output;

I'm not aware of cases where template_output should be returned to NULL. If they do exist, I'll have broken something.