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

51–60 of 90 posts

Re: Implementing State Machines in PostgreSQL

#51

Post by cr0sh »

I love seeing SQL being "abused" in this manner.

It's almost as crazy as the Excel spreadsheet I found to simulate a neural network.

I've always loved seeing how far you could push SQL and databases to do things that (on the surface) would seem extremely difficult or impossible.

What it usually turns out to be is possible - but in the long-term unmaintainable. This FSM is not that - not yet (if it gained even more states, it could get there). But I have seen (and I have unfortunately written) queries in SQL that could make your hair stand up. Insane extreme monstrosities that I am both proud and ashamed of (fortunately, I don't work for that company any longer).

On a different but related note, I do recall one query that a friend of mine wrote to allow the querying of a zip code database (which had lat/lon columns for each zip code), to be able to calculate distances from a given address - using SQL. It was a simple form of geo lookup he had to do for a particular project. I later used the same code as a part of a lookup process for placing markers on a google map (markers would indicate whatever was needed for "show me locations near my address within N miles"). It was a pretty interesting piece of SQL for the reason of doing the specialized distance calculation (can't remember which calc, but one of the simplest ones that didn't take into account certain things about the earth's "roundness" to make things super-accurate - that wasn't needed for short distances and purpose).

Re: Implementing State Machines in PostgreSQL

#52

Post by ysleepy »

mozumder wrote: post #39
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.

> Also the transition table might be an actual SQL table as well instead of switch-cases in a function. That would likely add an unnecessary read and slow things down just a little bit.

It depends. The table will reside in memory and it will just be a scan over a few bytes. Possibly cheaper than firing up the PL/pgSQL interpreter.

Re: Implementing State Machines in PostgreSQL

#53

Post by ysleepy »

felixge wrote: post #22
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 o…

Hm, I didn't know that about enums.

In the end it is just quibble about style anyway and one usually end up with what is measured faster in testing..

Re: Implementing State Machines in PostgreSQL

#54

Post by cryptonector »

felixge wrote: post #17
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 wan…

Well, this is what the SQL standard is for: to reduce the amount of work to be done in migrating between databases.

That said, I much prefer to put all this logic into SQL, and to use PostgreSQL, than the alternatives. There's any number of reasons for this:

- direct access to the DB is not dangerous if the logic needed to keep it consistent... is in the DB

- you don't have to replicate this logic if you add new front-ends

- SQL is very expressive, so you'll end up having less code using SQL than anything else -- this also means that RDBMS migration costs need not be as high as one might think, since there will be less SQL than the equivalent for the front-end

- updating business logic is easier to do atomically

Re: Implementing State Machines in PostgreSQL

#55

Post by felixge »

cryptonector wrote: post #54
felixge wrote: post #17

Earlier quoted context omitted.

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

Well, this is what the SQL standard is for: to reduce the amount of work to be done in migrating between databases. That said, I much prefer to put all this logic into SQL, and to use PostgreSQL, than the alternatives. There's any number of reasons for this: - direct access to the DB is not dangerous if the logic needed to keep it consistent... is in the DB - you don't have to replicate this logic if you add new fron…

Yeah, the idea of having a SQL standard is great. But in practice a "standard" is somewhat meaningless without rigorous compliance testing, and unfortunately SQL hasn't had this for 20 years [1]. But I'll take SQL over any of poor reinvention of relational algebra any day, so it's still better than the alternatives by far :).

[1] http://www.techrepublic.com/article/is-sql-a-standard-anymor...

Re: Implementing State Machines in PostgreSQL

#56

Post by lfittl »

felixge wrote: post #28
emidln wrote: post #18

Earlier quoted context omitted.

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

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

Re: Implementing State Machines in PostgreSQL

#57

Post by felixge »

cr0sh wrote: post #51

I love seeing SQL being "abused" in this manner. It's almost as crazy as the Excel spreadsheet I found to simulate a neural network. I've always loved seeing how far you could push SQL and databases to do things that (on the surface) would seem extremely difficult or impossible. What it usually turns out to be is possible - but in the long-term unmaintainable. This FSM is not that - not yet (if it gained even more st…

Hah, I hear you :). Pushing this much logic into the DB can feel weird at times. And the application this is part of definitely has a lot of very complicated and large SQL queries.

That being said, I think things can be somewhat tamed by applying the same best practices to your SQL as you apply to your regular code: I.e. lots of tests, good comments and documentation, extracting logic into either set returning functions or functions suitable for lateral joins (both can be inlined if done right [1]), keeping things in version control, etc.

But yes, applying those practices can be harder in SQL than it is in your application layer language for various reasons. So I'll always recommend avoiding going too crazy. You can often get the best of both worlds by making pragmatic choices about what should be done at which layer. No need to enslave yourself to a false dichotomy.

[1]: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

Re: Implementing State Machines in PostgreSQL

#59

Post by cryptonector »

cryptonector wrote: post #58

I do this sort of thing, and I highly recommend it, especially with PG. Another thing I like to do is to use queries against the pg_* tables to generate code/metadata for components of the application running outside SQL.

Another useful thing to do is to take advantage of record types. PG SQL is approaching something that one might call higher-order SQL.

When you can query the SQL schema using SQL queries (though I admit that the pg_* tables kinda suck) and when you have things like record types, you really do have a very powerful SQL.

Re: Implementing State Machines in PostgreSQL

#60

Post by felixge »

lfittl wrote: post #56
felixge wrote: post #28

Earlier quoted context omitted.

Yeah, this doesn't actually work. You'll get this error: ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

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.
Post reply on HN