Re: Storing state machine

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(dot)mohan(at)gmail(dot)com> 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(at)gmail(dot)com> wrote:
>
>>
>> Mohan Radhakrishnan <radhakrishnan(dot)mohan(at)gmail(dot)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
>>
>>
>>