State machine represented on DB, enforcement of state transition

Question

I've got a finite state machine which represents the phases of a job. I need to represent the states in a Postgres database. I would like to enforce the code correctness by forbidding updates from one state to the other unless the state machine allows so.

A naive way to accomplish my goal could be the acquisition of an exclusive lock on the table, within the transaction check the current state and the next state, abort with errors in case of invalid update.

This is clearly a performances killer, since I'm going to lock the Job table at each state transition.

Is there a way to accomplish the same goal via constraints?

Answer 1

Trigger is the answer for your problem.

Let's consider simple table:

CREATE TABLE world (id serial PRIMARY KEY, state VARCHAR);
insert into world (state) values ('big bang');
insert into world (state) values ('stars formation');
insert into world (state) values ('human era');

Function that will be called by the trigger. Define your state machine logic here. RAISE EXCEPTION is useful, since you can provide custom message here.

CREATE FUNCTION check_world_change() RETURNS trigger as $check_world_change$
BEGIN
  IF OLD.state = 'big bang' AND NEW.state = 'human era' THEN
    RAISE EXCEPTION 'Dont skip stars';
  END IF;
  IF OLD.state = 'stars formation' AND NEW.state = 'big bang' THEN
    RAISE EXCEPTION 'Impossible to reverse order of things';
  END IF;
  RETURN NEW;
END;
$check_world_change$ LANGUAGE plpgsql;

And define trigger for your table:

CREATE TRIGGER check_world_change BEFORE UPDATE ON world 
  FOR EACH ROW EXECUTE PROCEDURE check_world_change();

Now, when you try to update state of one of the rows, you'll get error:

world=# select * from world;
 id |      state
----+-----------------
  2 | stars formation
  1 | human era
  3 | big bang
(3 rows)

world=# update world set state='human era' where state='big bang';
ERROR:  Wrong transition
world=# select * from world;
 id |      state
----+-----------------
  2 | stars formation
  1 | human era
  3 | big bang
(3 rows)

References:

https://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html https://www.postgresql.org/docs/9.5/static/sql-createtrigger.html