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

41–50 of 90 posts

Re: Implementing State Machines in PostgreSQL

#41

Post by dizzystar »

I've built inventory systems in a similar fashion.

On one hand, using a bunch of PL/pgSQL makes sense for transaction isolation and faster execution. I'm not sure how much of the arguments about business logic matter in this case, but a strong argument for using PL/pgSQL is that these queries written in Python (for example) are going to be significantly slower, and that really matters when there are 100 concurrent users hitting the database every 10 seconds. No one likes waiting for their system to update... they'll just switch over to Excel. I think that PL/pgSQL is a great use-case for situations where preoptimizing for speed isn't a mistake, though the trade off is that you need to find the rare expert on processing languages (what order to triggers fire and how do you prevent this from being an issue?), and who can work through the complex logic of the system.

I wonder why the author didn't use windowing instead the lateral example. You can window and sum over composite rows, and it goes pretty fast. I never did a direct comparison between lateral and windowing, but windowing would be much cleaner and not require you to generate a series.

Re: Implementing State Machines in PostgreSQL

#42

Post by [deleted] »

staticassertion wrote: post #23
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?

[deleted]

Re: Implementing State Machines in PostgreSQL

#43

Post by felixge »

dizzystar wrote: post #41

I've built inventory systems in a similar fashion. On one hand, using a bunch of PL/pgSQL makes sense for transaction isolation and faster execution. I'm not sure how much of the arguments about business logic matter in this case, but a strong argument for using PL/pgSQL is that these queries written in Python (for example) are going to be significantly slower, and that really matters when there are 100 concurrent us…

Despite being pretty familiar with window functions, I couldn't figure out how to do it for that example in the time I was willing to spend on this post :). If you have a better query, I'm happy to update the article and give credit.

Re: Implementing State Machines in PostgreSQL

#44

Post by NotAUsername »

justQuestion2 wrote: post #25
zmonx wrote: post #16

Earlier quoted context omitted.

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…

Hey! Grakn person here.

The Prolog syntax is very elegant but it could be clunky, I totally agree. Our syntax has resource and role names instead of relying on position:

e.g.

match $x has lastname "some last name";

Re: Implementing State Machines in PostgreSQL

#45

Post by zac123 »

I have done very similar things in code rather in SQL. just a bit of curious, I understood it is not possible many to many connections, but why don't you allow from cancel->started again? The business logic is always tend to be changed. I think this is a bad example using FSM here. IMHO, I will only want to apply stable, constant and (code)internal FSM to pgSQL. (Thought about a joke how to kill a programmer just needs change the requirements three times. lol) Good article anyway.

Re: Implementing State Machines in PostgreSQL

#46

Post by agentultra »

aeorgnoieang wrote: post #40
bluejekyll wrote: post #26

Earlier quoted context omitted.

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…

There are good deployment tools for databases, e.g. ones for which all of the database 'code' objects (or just all of the objects period) are maintained in source control and updates are either automatic or scripted. Of course updating a database is much harder than overwriting executable or library files, but a lot of the objects in a database should be safely updatable by simply dropping and recreating them. The tr…

A great one is http://sqitch.org

Re: Implementing State Machines in PostgreSQL

#47

Post by jsjohnst »

FLUX-YOU wrote: post #29
jsjohnst wrote: post #9

Earlier quoted context omitted.

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 laye…

> It feels like you can go an entire career without switching databases.

I wish I could say that. I've done more than I care to count. The issue is usually one of the following:

1) converting between types of storage layers (SQL/NoSQL/flat file). I've never seen an abstraction layer that could handle MongoDB and later be converted to Postgres for example (a real migration I had to do once) and still do justice to either backend.

2) Hopefully you pick a storage layer for a good reason. For example, say you picked Postgres because you have Geospatial needs. Business later dictates you have to use MySQL "for reasons", what abstraction layer is going to help there to not require code refactor?

3) If you are doing things right, there's more than App code that interfaces with the DB. You've got the entire devops chain (backups, automation, etc) that will likely need to be rewritten. This can take an immense amount of work as well.

Anyway, not saying it's not possible, but I've found it a good litmus test of another engineer if they think moving to another storage layer should be simple. It's not always, but still more often than not shows inexperience or over optimism.

Re: Implementing State Machines in PostgreSQL

#48

Post by jsjohnst »

mercer wrote: post #19
jsjohnst wrote: post #9

Earlier quoted context omitted.

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.

Could you give some specifics as to what kind of stuff typically needs refactoring if you switch storage layers?

I partly answered your question in a comment above. If you'd like me to go more in depth, happy too!

Re: Implementing State Machines in PostgreSQL

#49

Post by jsjohnst »

bluejekyll wrote: post #20
jsjohnst wrote: post #9

Earlier quoted context omitted.

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.

That's interesting. I have successfully designed systems which could be easily migrated between different RDMS' with minimal changes. Obviously to go to no-sql or other less conventional storage is a different story. It is an interesting question about how successful people are in migrating to different storage engines.

> I have successfully designed systems which could be easily migrated between different RDMS' with minimal changes.

Not that I doubt you, because I know with the right trade offs it's possible, but is the operative word "could" or did you actually ever migrate one of those architectures?

> Obviously to go to no-sql or other less conventional storage is a different story.

Ruling those out feels a bit disengenuous. There are plenty of good reasons to need to migrate to or from a relational DB.

Re: Implementing State Machines in PostgreSQL

#50

Post by mixmastamyk »

felixge wrote: post #36

Author here: If the article above has you excited, come and join my team at Apple. We're hiring Go and PostgreSQL developers in Shanghai, China right now. Relocation is possible, just send me an e-mail to find out more about this role. My e-mail is in my profile.

How is living in Shanghai?
Post reply on HN