Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Implementing State Machines in PostgreSQL

felixge.de

Post reply on HN
Print view View original

21–30 of 90 posts

Re: Implementing State Machines in PostgreSQL

#21

Post by bluejekyll »

liotier wrote: post #13
devy wrote: post #5

Earlier quoted context omitted.

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.

You should see what large Oracle shops in the enterprise segment do with PL/SQL, though whether they have gone too far is open for debate...

Yes they have. And then it's full on vendor lock-in.

Re: Implementing State Machines in PostgreSQL

#22

Post by felixge »

ysleepy wrote: post #12

Cool trick. Should be an enum instead of text though. Also the transition table might be an actual SQL table as well instead of switch-cases in a function. - That way it remains a bit more declarative and you can do some meta-queries.

Yeah, maybe I'll update the post and mention ENUMs.

I like the type safety of ENUMs, and I'm actually using them in my application, but they can cause a lot of problems. E.g. it's currently almost impossible to remove an ENUM value [1], and adding a new value can't be done inside of a transaction.

The state transitions could definitely go into a table, but I feel it's overkill in many cases, and would have probably obfuscated the main ideas presented in the article.

Anyway, thanks for your feedback. There is definitely a lot more to explore for anybody who is interested in applying these ideas in practice :)

[1] https://www.postgresql.org/message-id/21012.1459434338%40sss...

Re: Implementing State Machines in PostgreSQL

#23

Post by staticassertion »

zitterbewegung wrote: post #3

Since 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 wher…

Check out P?

Re: Implementing State Machines in PostgreSQL

#24

Post by bamdadd »

bluejekyll wrote: post #2

Cool 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 agree but have you read BoiledCarrot from Martin Fowler? https://martinfowler.com/bliki/BoiledCarrot.html

Re: Implementing State Machines in PostgreSQL

#25

Post by justQuestion2 »

zmonx wrote: post #16
harperlee wrote: post #14

Earlier quoted context omitted.

Can you recommend a good in depth introduction to "productive" (in contrast to a more academic approach) modern prolog for someone very superficially familiar to it (think uni course, long time ago)? I heard that e.g. Constraint Logic Programming functionalities makes some old approaches obsolete, and thus going through old material as starting point is very ineffective.

Please see my profile page: It contains several links to material that I recommend for learning modern Prolog. You can quite often apply Prolog in actual practice if you know it. For instance, see how often "logic" is mentioned just in the context of the present discussion. It's nice to know a logic programming language that can elegantly express business logic and business rules!

I found it hard to use entities with more than 4 Attributes when defining them as simple compounds like

person(Firstname, Lastname, Gender, Age, State, Country).

Accessing the attributes by position makes the code hard to read because one has to remember the position and mistakes are not catched because there is no type system.

Would you recommend using the libray record or using dicts in swi-prolog? This would make the code non portable. Moreover, do you recommend dedicated strings or list of atoms to represent strings?

Re: Implementing State Machines in PostgreSQL

#26

Post by bluejekyll »

jjawssd wrote: post #7
bluejekyll wrote: post #2

Cool 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

It's not just about migration. There are deployment issues as well.

I like to think of each layer in a tiered system as having differing deployment requirements and time tables. Front-end systems will be very frequent, backend systems possibly less so, though not necessarily, and then DBs ideally infrequent and they generally take longer.

At a minimum, keeping them decoupled is freeing for patching bugs and releasing features independently. It does raise the bar for keeping all changes compatible with existing systems.

Re: Implementing State Machines in PostgreSQL

#27

Post by aidos »

emidln wrote: post #18
aidos wrote: post #15

Earlier quoted context omitted.

I disagree about enum. I've tried using it but I found that it's too hard to manage/migrate in postgres. Obviously there are a few different ways of achieving the enumish behaviour in postgres (or other dbs) — nowadays I just start with text with a constraint and upgrade to a real table if I need more detail. YMMV but I don't think a blanket "just use enum" is the correct approach.

This is too hard to put into a sql file and run with your migrations tool? BEGIN; ALTER TYPE my_enum ADD VALUE 'bar' AFTER 'foo'; COMMIT;

From the docs (as I suspect you already know): ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block

Also, removing a value is a pain (though, that may have changed now).

I know you can do it — but I've found after trying a bunch of approaches that text with constraints is a better starting point. Just wanted to point it out because it's something I researched a fair amount. Many guides say that enum can be a pain but I decided that I'd use them to be pure, then discovered they were a pain, and now don't use them as a starting point.

Re: Implementing State Machines in PostgreSQL

#28

Post by felixge »

emidln wrote: post #18
aidos wrote: post #15

Earlier quoted context omitted.

I disagree about enum. I've tried using it but I found that it's too hard to manage/migrate in postgres. Obviously there are a few different ways of achieving the enumish behaviour in postgres (or other dbs) — nowadays I just start with text with a constraint and upgrade to a real table if I need more detail. YMMV but I don't think a blanket "just use enum" is the correct approach.

This is too hard to put into a sql file and run with your migrations tool? BEGIN; ALTER TYPE my_enum ADD VALUE 'bar' AFTER 'foo'; COMMIT;

Yeah, this doesn't actually work. You'll get this error:

ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

Re: Implementing State Machines in PostgreSQL

#29

Post by FLUX-YOU »

jsjohnst wrote: post #9
bluejekyll wrote: post #2

Cool 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.

It feels like you can go an entire career without switching databases. And if you still have most of the original dev team, the cost of rewrites are probably not as high as they're made out to be (by telling everyone to plan for switching databases at some undefined point in the future)

Loose coupling has a lot of other advantages but this seems to have become the biggest selling point for a loosely-coupled data layer.

Re: Implementing State Machines in PostgreSQL

#30

Post by mrkgnao »

For people interested in Prolog-like DB systems:

https://github.com/agentm/project-m36

> Unlike most database management systems (DBMS), Project:M36 is opinionated software which adheres strictly to the mathematics of the relational algebra. The purpose of this adherence is to prove that software which implements mathematically-sound design principles reaps benefits in the form of code clarity, consistency, performance, and future-proofing.

Post reply on HN