Re: Storing state machine - Mailing list pgsql-general

Mohan Radhakrishnan <radhakrishnan.mohan@gmail.com> writes:

> Hello,
>            We have a workflow when we receive events into the service. But we don't have a way to choreograph or
orchestratethe workflow. The
 
> services are all independent and receive and respond to events.
>
> Since there is no order imposed by the event queues I was thinking of storing a simple state machine in the table.
>
> 1. Order PENDING
> 2. Order line 1 PENDING 
> 3. Order line 2 PENDING 
> 4. Order line 1 PROCESSED
> 5. Order line 2 PROCESSED 
> 6. Order PROCESSED
>
> Order and Order lines can be PROCESSED in any order. But at the end all lines should be  PROCESSED and then the order
isalso  PROCESSED.
 
> I won't be able to use any PostgreSql functions because we don't depend on those features.
>
> Are there any PostgreSql features that could support this pattern ? Is it just like any other eventually consistent
pattern?
 
>   

What you appear to have here is two entities - orders and order items.
An order entity has a 'state' (pending/processed) and is linked to 
one or more order items which in turn have a state.

The information about order state could be derived rather than actually
stored i.e. an order is pending if any of its order items are pending
and is processed if all of its order items are processed. At a minimum,
storing the order item state would be sufficient and a basic sql
statement would be able to tell you what the state of an order is.

In general, you don't want to store duplicate or redundant information
as this can be a source of anomalies. (e.g. order state is not updated
to 'processed' when all items are processed or is updated to processed,
but then another item is added and for some reason, the state is not
switched back to pending etc).

in general, it is usually a mistake or poor design to use one table to
represent different 'entities'. That is a 'bit bucket' approach which
really degrades the ability of the database to do what it is good at -
managing entities and their relationships.

From the description you have provided, everything you need can be
easily managed with basic SQL statements - no need for functions or
stored procedures. All you would need is an SQL statement to insert a
new order item, an SQL statement to update the state of an item and a
SQL statement to report on the state of an order. 

Your requirement statement is extremely simple and I suspect you have
glossed over some of the constraints/requirements, but based on what you
have written, your requirement seems to be trivial and easily satisfied
with basic database facilities. 

--
Tim Cross