Re: Storing state machine

[Mohan Radhakrishnan <radhakrishnan.mohan@xxxxxxxxx>]

After further research this may be accomplished using a 'SAGA'.

The other question I was researching is the ordering of these records by timestamptz. If I have to use a

orchestrator then I will pick sets of 10000 records, for example, ordered by  timestamptz. Should I be looking at a certain

type of index like the BRIN index for ordering timestamptz ? I have to exclude already processed records.

Thanks.

On Mon, Apr 19, 2021 at 6:40 PM Mohan Radhakrishnan <radhakrishnan.mohan@xxxxxxxxx> wrote:

  >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@xxxxxxxxx> wrote:

Mohan Radhakrishnan <radhakrishnan.mohan@xxxxxxxxx> 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