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

61–70 of 90 posts

Re: Implementing State Machines in PostgreSQL

#61

Post by kingdomcome50 »

Oh no... I commend your effort, but this is totally ill-conceived. What's even more disheartening is the number of people who looked at this and also thought it was a good idea.

There is just so much wrong here... let's start with the basics. If this is truly a FSM then why on earth are you using a transaction table (state is a value)? An accumulating snapshot table (state is a field), besides being EXACTLY for this purpose, will be far more efficient in almost every way. Your "analytic" queries would be simple select statements, and your trigger (shudder) could be reduced to simple constraints. The sheer amount of over-engineering here is staggering.

Lastly, what is the purpose of putting the transition logic in the database? It's simply redundant upon actual implementation. Somewhere, somehow, another program has to actually carry out your actions (paying/shipping/cancelling) on the order and make a call to your database with the appropriate event inputs. So why not just put the entire state machine with the rest of your business logic? As many have pointed out, this is where it should be anyway.

Re: Implementing State Machines in PostgreSQL

#62

Post by anarazel »

felixge wrote: post #60
lfittl wrote: post #56

Earlier quoted context omitted.

FWIW, this is fixed in the upcoming Postgres 10: "Reduce locking required for adding values to enum types (Andrew Dunstan, Tom Lane) Previously it was impossible to run ALTER TYPE ... ADD VALUE in a transaction block unless the enum type was created in the same block. Now, only references to uncommitted enum values from other transactions are prohibited." https://www.postgresql.org/docs/devel/static/release-10.html..…

Sweet. That's certainly a nice improvement :). If removing ENUM values will be supported at some point as well, I can see myself recommending them unconditionally.

Would you be ok if dropping a value were to just mark it as 'deleted' from the catalogs, and no new values could be assigned? The reason enums have these weird restrictions is that they can appear in indexes, even after a value has been deleted (or its creation rolled back). If we don't know how to compare them after deletion, the index can't be correctly traversed anymore...

Re: Implementing State Machines in PostgreSQL

#63

Post by felixge »

mixmastamyk wrote: post #50
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?

Good question. I'm working remotely myself and can't relocate easily because my wife is a doctor and it's very difficult for her to move between medical systems. I was lucky to join this team when this wasn't considered a problem.

That being said, I'm in Shanghai frequently to sync with my colleagues and it's an amazing city. I've found pretty much most conveniences available to me in Berlin and to some extend even better. There is an active expat community, and a fascinating vibe.

My manager (from SF, USA) has been there for over a year now and has enjoyed the experience a lot and extended his stay. YMMV, but I'm fairly well traveled and would say that it's a pretty great spot if you're willing to emerge in a foreign language and culture.

Re: Implementing State Machines in PostgreSQL

#64

Post by gaius »

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

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 languag

You'll rewrite your front end 20 times in 20 different "frameworks", and your app 5 times in 5 different languages, for everytime you actually change databases

Re: Implementing State Machines in PostgreSQL

#65

Post by felixge »

anarazel wrote: post #62
felixge wrote: post #60

Earlier quoted context omitted.

Sweet. That's certainly a nice improvement :). If removing ENUM values will be supported at some point as well, I can see myself recommending them unconditionally.

Would you be ok if dropping a value were to just mark it as 'deleted' from the catalogs, and no new values could be assigned? The reason enums have these weird restrictions is that they can appear in indexes, even after a value has been deleted (or its creation rolled back). If we don't know how to compare them after deletion, the index can't be correctly traversed anymore...

Yeah, I think that'd be reasonable. My main motivation for using ENUMs is type safety and small column width. What you're proposing sounds like it would keep both of these advantages :)

Re: Implementing State Machines in PostgreSQL

#66

Post by aidos »

anarazel wrote: post #62
felixge wrote: post #60

Earlier quoted context omitted.

Sweet. That's certainly a nice improvement :). If removing ENUM values will be supported at some point as well, I can see myself recommending them unconditionally.

Would you be ok if dropping a value were to just mark it as 'deleted' from the catalogs, and no new values could be assigned? The reason enums have these weird restrictions is that they can appear in indexes, even after a value has been deleted (or its creation rolled back). If we don't know how to compare them after deletion, the index can't be correctly traversed anymore...

2 Questions! What's the index data structure that holds old enum values even after the value itself is not used anymore?

Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?

Re: Implementing State Machines in PostgreSQL

#67

Post by anarazel »

aidos wrote: post #66
anarazel wrote: post #62

Earlier quoted context omitted.

Would you be ok if dropping a value were to just mark it as 'deleted' from the catalogs, and no new values could be assigned? The reason enums have these weird restrictions is that they can appear in indexes, even after a value has been deleted (or its creation rolled back). If we don't know how to compare them after deletion, the index can't be correctly traversed anymore...

2 Questions! What's the index data structure that holds old enum values even after the value itself is not used anymore? Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?

> 2 Questions! What's the index data structure that holds old enum values even after the value itself is not used anymore?

It's not that index structure specific atm, even though it could possibly be avoided for some types (e.g. hash, although there's considerable visibility issues to make that possible). Consider e.g. a btree index, if the deleted enum value ends up in an inner page, you really need to know how it compares to know where to descend to.

> Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?

I like using them. There's cases where the transactional restrictions make it too problematic, but other than that I think the documentational advantage is substantial, besides just the width.

Re: Implementing State Machines in PostgreSQL

#68

Post by felixge »

kingdomcome50 wrote: post #61

Oh no... I commend your effort, but this is totally ill-conceived. What's even more disheartening is the number of people who looked at this and also thought it was a good idea. There is just so much wrong here... let's start with the basics. If this is truly a FSM then why on earth are you using a transaction table (state is a value)? An accumulating snapshot table (state is a field), besides being EXACTLY for this…

TBH I'm not sure if I follow your argument. You can certainly apply the main idea of this article (modeling a FSM as a user defined aggregate) to automatically materialize the latest state of each order. In fact, that's what we're doing in the app we're using this technique in.

Anyway, YMMV and I'm not recommending to apply the ideas in this article in every situation. But after having had all of this logic in the application layer before migrating to Postgres, I find this approach more maintainable.

Post reply on HN