Storing state machine

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 orchestrate the 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 is also 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

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.

Yes. I wasn't specific. There are multiple instances of the service for
availability. The
events could reach any one of those. I think in this case isolation levels
could be key ?
An instance at any point in time can't read assuming all transactions are
committed by
all instances.

The parent order's status is used to check easily instead of checking it
children.

Thanks.

On Mon, Apr 19, 2021 at 12:21 PM Tim Cross <theophilusx@gmail.com> wrote:

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 orchestrate the 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 is also 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