React to DB events for multi-node setup about bob

[lispyclouds]

@amexboy This is what I've come up with to solve the distributed state problem when pausing/unpausing and even stopping(#56) pipelines without letting the current step finish and reacting (almost) instantly:

• The runs table contains the info we are interested in, namely the pipeline, current container id and status.
• When a node recieves the pause/unpause/stop request, it checks if its running the container from the last_pid from the runs table, if yes pauses/unpauses/kills it. if its not running it, sets the status column to paused/running based on the request.
• The other nodes are then notified of this change and the node actually running it does the job.
• To achieve this async notification, I'm using this and the impl is as follows:
  • Create a PLSQL function that responds with the current row as json that has been affected with an update on the runs table:

  CREATE FUNCTION "notify_on_status_update"()
  RETURNS TRIGGER
  LANGUAGE "plpgsql"
  AS $$
  BEGIN
    PERFORM pg_notify('pipeline_status', row_to_json(NEW)::TEXT);
    RETURN NEW;
  END;
  $$;

  • Create a trigger which triggers this when an update happens:

  CREATE TRIGGER "pipeline_status_change"
  AFTER UPDATE ON "runs"
  FOR EACH ROW EXECUTE PROCEDURE "notify_on_status_update"();

  • Create a LISTEN connection from each of the nodes to the DB as described here
  • In the callback, do the same process of checking if the node has the container and handle it accordingly.

This way the distributed state is maintained, messaging is async and the DB is still the single source of state.

I am planning to implement the framework for this so that the pausing and stopping works and feedback and better ideas most welcome!