Implementing State Machines in PostgreSQL
1–10 of 90 posts
Re: Implementing State Machines in PostgreSQL
#2Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres.
If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else...
Re: Implementing State Machines in PostgreSQL
#3Since FSMs seem to make sense in some cases while implementing logic in both the server / database / client it would be interesting to create a language that would be a DSL which outputs transducers (Finite State Transducers) for each part. Using ideas from Functional Reactive Programming would be useful.
Adding a schema for data within the transducers would be helpful. You would end up with something like react where logic would be shared and scaffolding could be created after making the server. This could possibly be augmented by CQRS and or Event Sourcing. I think functional programming would help (Clojure or F# would be my choices for implementation). This would also help with vendor lock in.
Re: Implementing State Machines in PostgreSQL
#4Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…
My thoughts exactly. I don't do a ton of DB programming, but I've only ever written one thing in a non-agnostic way. We got a requirement that users wanted to copy an entire "project" which was the top level of a hierarchy. I wrote an Oracle routine to do the deep copy. I did it because I imagined what the PL/SQL would look like (clean) vs. what the Java code would look like (considering Hello World in Java is ugly, you can see where I'm going ;-)
Re: Implementing State Machines in PostgreSQL
#5Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…
My thoughts exactly. I don't do a ton of DB programming, but I've only ever written one thing in a non-agnostic way. We got a requirement that users wanted to copy an entire "project" which was the top level of a hierarchy. I wrote an Oracle routine to do the deep copy. I did it because I imagined what the PL/SQL would look like (clean) vs. what the Java code would look like (considering Hello World in Java is ugly,…
Mine too! Having logic embedded into SQL functions seems to be an anti-pattern to me (it's harder to maintain and harder to do release management). While it's great that Postgres can do this (btw, I love Postgres), I suspect there aren't many people will use this feature in production environment.
Re: Implementing State Machines in PostgreSQL
#6Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…
> If ever you decide you want to migrate away from Postgres[…]
it's one of the things to keep in mind. Depending on your application, migrating away from postgres will be more or less painful. If you're already deeply invested in postgres features, this is just one more problem to solve.
> If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else
yes. This has the all the usual issues of putting (some) business logic into the database. On the other hand, by using this, you're basically just creating a data integrity constraint similar to a foreign key, just one not as widely supported.
Still. If you ever plan to move to a database that doesn't support foreign key constraints, you will have to implement the business logic somewhere else.
For me, data integrity is paramount. If ever I can put a data integrity check directly into the database, I will do it because bugs in the application logic can exist and when the database itself enforces integrity constraint, I'm protected from those.
I don't want to have to deal with, to stay in the framework of the article, a shipped, but unpaid order. Was this a bug in the application? Did it actually ship? Did the payment fail?
If the database blows up on any attempts to store invalid data, I'm protected from having to ask these questions.
Re: Implementing State Machines in PostgreSQL
#7Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…
This implies that a need to migrate away from Postgres will arise. For most companies it is very likely that such a need will never materialize. Meanwhile Postgres keeps getting better and better. https://wiki.postgresql.org/wiki/New_in_postgres_10
Re: Implementing State Machines in PostgreSQL
#8Thank you for sharing! My impression is that database systems are increasingly gravitating towards Prolog, with various extensions such as logical rules, more expressive aggregation, state machines, constraints, Turing completeness, ... All these sound very familiar to Prolog programmers.
Only recently, there was a post on GRAKN.AI which seemed heavily inspired by Prolog.
This is good news for Prolog: Modern Prolog systems provide many features that are important in the domain of databases, such as JIT indexing, transactions, and dedicated mechanisms for semantic data.
Re: Implementing State Machines in PostgreSQL
#9Cool example. But, doing this will create a tight coupling between business logic , the state machine, and storage/persistence, Postgres. If ever you decide you want to migrate away from Postgres, you'll need to rewrite all the business logic into some other out of process language. If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else.…
I hear the argument about switching storage layers a lot, and I don't completely disagree with it, but in my 20+ years writing code, I've never found a case where switching storage engines didn't cause a massive rewrite even when the storage layer was used in an agnostic way. I'm sure there are examples where it has worked, but saying it as if it's a maxim just feels wrong to me.
Re: Implementing State Machines in PostgreSQL
#10[deleted]