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?

looks like use case for BEFORE UPDATE trigger. https://www.postgresql.org/docs/9.5/static/sql-createtrigger.html — Oleksii M, Sep 08 '16 at 14:05
also, consider this discussion http://stackoverflow.com/questions/14367332/sql-constraint-to-prevent-updating-a-column-based-on-its-prior-value — Oleksii M, Sep 08 '16 at 14:07
So, a trigger seems to be indeed appropriate. Is there a way to trigger a rollback? I think this would do: ``` create trigger test before update on "Job" for row when (OLD."status" = 'Init' and NEW."status" != 'Queued') execute procedure rollback() ``` But I would prefer not to declare a `rollback` function. — Dacav, Sep 08 '16 at 14:23
@Dacav: you don't need to rollback in the trigger, just [RAISE](https://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html) an exception and the outer transaction will be aborted with an error. — MatheusOl, Sep 08 '16 at 14:36
@MatheusOl yup! :) Found it https://www.postgresql.org/message-id/flat/525D9505.1030209%40optionshouse.com#525D9505.1030209@optionshouse.com and I was able to implement it too. I was wondering if I really *need* to write a function for this, if I need I'll do. At this point the first who answer gets the cake :) — Dacav, Sep 08 '16 at 14:44
I would define function where all logic described, so `(OLD."status" = 'Init' and NEW."status" != 'Queued') ` will be not in trigger definition, but in function. For forbidden transitions, return null, this will prevent update (https://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html) — Oleksii M, Sep 08 '16 at 14:44

score 6 · Accepted Answer · answered Sep 08 '16 at 15:13

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

State machine represented on DB, enforcement of state transition

1 Answers1