We have a trigger that is 1400 lines long. Try debugging that when there is a problem.
Sounds like your problem isn't the trigger per-se
How Postgres Triggers Can Simplify Your Back End Development
31–40 of 112 posts
Re: How Postgres Triggers Can Simplify Your Back End Development
#32Earlier quoted context omitted.
Then you discover Materialised Views... :) Magic.
Just wait until we get Incremental Materialized Views!
I was gonna mention that as well. There's a relevant talk from the System distributed convention https://www.youtube.com/watch?v=QtQMWUik0oY
It's about caching views or queries and updating them when necessary as well as keeping your cache and DB in sync automatically.
Re: How Postgres Triggers Can Simplify Your Back End Development
#33My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…
Yeah I was expecting the post author to discuss the trade-off being made here because it’s really important to do so. The biggest complaint I have with these pithy articles is that they try to sell you on a particular trade-off without explaining what the deal is. It makes me think the author: 1. Just discovered this. 2. Implemented a bunch of them. 3. Hasn’t been maintaining this solution for more than a couple of m…
> The biggest complaint I have with these pithy articles is that they try to sell you on a particular trade-off without explaining what the deal is.
This is basically the clickbait in the wider world affecting software development, even though it might not look like it. Boiled down to the essentials, we are telling each other the software version of "Here's How to Lose 10 Pounds in Time for Summer." way more often than "How To Balance Diet and Exercise to Remain Healthy Over A Decade".
But it's up to us to those of us who like more discussion to reverse these trends. In that vein -- do you have good sources that typically talk about tradeoffs with technology rather than promoting a specific one? Kleppmann's book on Data Intensive applications is one I have found in the past.
Re: How Postgres Triggers Can Simplify Your Back End Development
#34I wouldn’t call this “simplified”. Personally, it’s much more valuable to have all business logic in one place, in a single language, available at a glance. The perforance gain isn’t worth the increased complexity in codebase.
You just unintentionally made your problem distributed which is can of worms and you'll find out later when your project is successful in production.
Re: How Postgres Triggers Can Simplify Your Back End Development
#35What's old is new again. In 2007 we were using triggers and stored procedures heavily with mysql and a java app. Unfortunately we were also reliant on read replicas. Some of this replication behaviour did not translate well with the mix of these functions and auto incrementing IDs. Sometimes it would result in foreign key constraint violations and all of a sudden our replication would stop. This was even worse when we tried multi master setups. I spent years dealing with this. Ultimately we dropped the use of the most complex queries and shifted them into code which made the replication more stable but at the cost of Dev time.
Morale of the story, use it with caution. I know postgres is different but when you start turning you database into a ball of mud things get dangerously difficult to debug and fix.
Re: How Postgres Triggers Can Simplify Your Back End Development
#36Why is this the top story? This is a major foot gun. Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Instead adopt a solution for structuring your business logic in a sane way, such as using a workflow engine. Your code will become simpler and well organized that way without creating a tangled web of distributed rules, as well as exist…
Can you give some more detail plaese?
Re: How Postgres Triggers Can Simplify Your Back End Development
#37I love these types of techniques. Need a basic no nonsense queue? Postgres. Need a basic reporting infrastructure? Postgres. Need a document store? Postgres. But every single time this comes up, people on the engineering teams Ive been on all throw their hands up and accuse folks of overengineering or underengineering. You need rabbit or kafka. We should move to mongo. Etc. Thats the part thats hard.
Depending on the team I'd still opt for using "anything else" rather than Postgres as a queue. The tendency to have long-lived connections combined with a flow of short-lived messages will yield a runaway queue (due to MVCC) at low enough message rates that even an early-stage startup might notice.
Re: How Postgres Triggers Can Simplify Your Back End Development
#38Why is this the top story? This is a major foot gun. Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Instead adopt a solution for structuring your business logic in a sane way, such as using a workflow engine. Your code will become simpler and well organized that way without creating a tangled web of distributed rules, as well as exist…
> Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex.
Alternatively, write all the business logic in the database. This way you can better leverage the DB features and ensure that logic only needs to be written once.
Re: How Postgres Triggers Can Simplify Your Back End Development
#39Business logic in the database screams anti-pattern to me.
How do we know who created the rule, edited the rule? How can we reason about the sequence in which these rules are executed based on larger use cases with complex interactions.
Seems like a fire waiting to happen.
Re: How Postgres Triggers Can Simplify Your Back End Development
#40If you plan on never changing away from postgres, never having to shard, never needing to do anything that a trigger can’t support, then it is a good option. Which may be true for the vast majority of the apps. You also need sql expertise in addition to app dev expertise.