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.mo...@gmail.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 <theophil...@gmail.com> wrote: > >> >> Mohan Radhakrishnan <radhakrishnan.mo...@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 >> >> >>
