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

11–20 of 90 posts

Re: Implementing State Machines in PostgreSQL

#11

Post by jononor »

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…

This was basically the background for http://github.com/jonnor/finito - still experimental.

Re: Implementing State Machines in PostgreSQL

#13

Post by liotier »

devy wrote: post #5
ericfrederich wrote: post #4

Earlier quoted context omitted.

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.

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

Re: Implementing State Machines in PostgreSQL

#14

Post by harperlee »

zmonx wrote: post #8

Thank 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 s…

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.

Re: Implementing State Machines in PostgreSQL

#15

Post by aidos »

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.

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.

Re: Implementing State Machines in PostgreSQL

#16

Post by zmonx »

harperlee wrote: post #14
zmonx wrote: post #8

Thank 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 s…

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!

Re: Implementing State Machines in PostgreSQL

#17

Post by felixge »

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

Author here.

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

Yeah, but I don't consider this a bad thing. IMO migrating between databases should always require a lot of rewriting. If it doesn't, you're most likely underutilizing the features provided by your database.

> 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'm not sure I follow. The application I'm using this in has > 1 billion rows and we frequently re-compute the state of all our entities across the entire data set in batches after we make changes to our logic. Having the code that does this in the database avoids having to move large amounts of data between our db and the application.

Re: Implementing State Machines in PostgreSQL

#18

Post by emidln »

aidos wrote: post #15
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.

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;

Re: Implementing State Machines in PostgreSQL

#19

Post by mercer »

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.

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

Re: Implementing State Machines in PostgreSQL

#20

Post by bluejekyll »

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.

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.

Post reply on HN