7 ms·

My 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 `a

My 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 needed. PostgreSQL is a real powerhouse.

AmericanChopper 4y ago
Table triggers are the ultimate foot gun in this respect. They are highly obscured! Stored procedures split your business logic too, but when you want to go and look at your database logic, it’s at least where you’re expecting to find it, and not built into a table. I’d highly recommend people avoid them, unless you feel that you really need them _and_ you have very robust development processes. As soon as you deploy your first table trigger, from that moment you have to check every DML statement for unintended side effects.
- marcosdumay 4y ago
  > from that moment you have to check every DML statement If that's the case, you have a documentation problem. It should be easy to decide if there are side effects or not just by looking at the DML and the metadata you need for it. In fact, the case here is that the trigger on the article is an incredibly bad one. It's not a natural consequence of the table, or the database structure. It's probably not even always true to the business rules, what is the one property triggers must have no matter what.
  - AmericanChopper 4y ago
    The problem isn’t really that it’s hard to document. The problem is that when you don’t use triggers, you don’t have to consult documentation to know what’s going to happen after an insert (or an update, or delete…). People who haven’t used triggers would assume that a row will simply be inserted. But if you use triggers this is no longer a safe assumption.
throwaway173738 4y ago
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 months. This is a trade-off that only manifests itself after maybe a couple years when you’ve built a system and you have many hands maintaining it. Either there’s some performance problem that makes it worth it or you’ve just obfuscated half your code for no reason. In the latter case those many hands are going to wreak havoc on your system before you figure out how to make it maintainable.
- quadrifoliate 4y ago
  > 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.
  - throwaway173738 4y ago
    It’s unfortunately hard for me to point to a source because everything I know I absorbed from more experienced engineers on hardware projects.
- eastbound 4y ago
  DB upgrades are a pain. Triggers must be deployed with “UPDATE” instructions, it’s not as easy as keeping code in git.
simonw 4y ago
I think this problem can be robustly solved if you have the right mechanisms in place: 1. Migrations. Your schema needs to live in version control, and changes to your schema must be applied by an automated system. Django migrations are the gold standard here in my opinion, but you can stitch together a custom system if you need to, one that tracks which migrations have been run already and provides a mechanism to apply new ones. 2. Automated tests. Your triggers MUST be covered by automated tests - call them unit tests or integration tests depending on your preferences, but they need to be exercised. Don't fall into the trap of mocking your database in your tests! With these two measures in place I'm 100% comfortable using triggers that split my business logic between my application code and my database code.
- ysavir 4y ago
  But what problem is moving to triggers solving? Is keeping all your logic in a single place actually a problem, or is moving part of it to Postres tempting because it's nifty? How much training will you have to provide for new hires to be able to run with Postgres triggers instead of just Django logic?
  - simonw 4y ago
    Triggers protect you against bugs. If your application logic needs to remember to update a denormalized column somewhere it's very easy for a bug to slip in in the future when someone adds a new piece of Python code but forgets to update the denormalized column. With triggers you can eliminate that source of bugs entirely. This is even more valuable if you have more than one system interacting with a single database.
    - paulryanrogers 4y ago
      I did this with robust DB constraints and selective use of triggers and stored functions. It can feel like a strait jacket long term. Though it did help protect against less competent DBAs breaking things and avoid some footguns.
    - SOLAR_FIELDS 4y ago
whstl 4y ago
Business rules are already often in multiple separated places in a codebase. If you mean "it should be in the same git repository", then triggers should be already in migrations. But IMO, even migrations are a stopgap solution to this problem. In some cases, such as in ORMs like Rails' ActiveRecord, even the column names aren't present in the model by default, they are only migration files. The real solution is putting the definition of the triggers (and all other database things too) closer to the code that operates on them. In models, for example. And models should enforce that only the defined triggers are present in the database. Of course, since most ORM users aren't really big DB users, this isn't exactly common. Until we get features like this, I agree that triggers can be confusing.
- cgh 4y ago
  If you are using some JPA implementation, eg Hibernate, then JPA events are more or less what you are talking about except JPA can't enforce the condition that JPA-defined events are the only triggers present. If you write custom triggers then you run the risk of stale entity data being presented to the user. But otherwise, this fulfills the condition of triggered events residing in the codebase. Of course, this means you have to be comfortable with using an ORM in the first place.
brightball 4y ago
You can say similar things about any API based development though. Triggers are just a part of the database API. Databases exist to manage data, not just store and retrieve it.
avereveard 4y ago
it is also a very restrictive environment, you either only trigger on a single in transaction table, or you risk having triggers tripping other triggers, limiting the approach scale anyway. at which point a well managed transaction from an active record or a data gateway will do miles better.
citrin_ru 4y ago
You have exactly the same problem in microservice architecture while calling a remote endpoint.

AmericanChopper 4y ago

Table triggers are the ultimate foot gun in this respect. They are highly obscured! Stored procedures split your business logic too, but when you want to go and look at your database logic, it’s at least where you’re expecting to find it, and not built into a table. I’d highly recommend people avoid them, unless you feel that you really need them _and_ you have very robust development processes. As soon as you deploy your first table trigger, from that moment you have to check every DML statement for unintended side effects.

marcosdumay 4y ago
> from that moment you have to check every DML statement If that's the case, you have a documentation problem. It should be easy to decide if there are side effects or not just by looking at the DML and the metadata you need for it. In fact, the case here is that the trigger on the article is an incredibly bad one. It's not a natural consequence of the table, or the database structure. It's probably not even always true to the business rules, what is the one property triggers must have no matter what.
- AmericanChopper 4y ago
  The problem isn’t really that it’s hard to document. The problem is that when you don’t use triggers, you don’t have to consult documentation to know what’s going to happen after an insert (or an update, or delete…). People who haven’t used triggers would assume that a row will simply be inserted. But if you use triggers this is no longer a safe assumption.

throwaway173738 4y ago

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 months. This is a trade-off that only manifests itself after maybe a couple years when you’ve built a system and you have many hands maintaining it. Either there’s some performance problem that makes it worth it or you’ve just obfuscated half your code for no reason. In the latter case those many hands are going to wreak havoc on your system before you figure out how to make it maintainable.

quadrifoliate 4y ago
> 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.
- throwaway173738 4y ago
  It’s unfortunately hard for me to point to a source because everything I know I absorbed from more experienced engineers on hardware projects.
eastbound 4y ago
DB upgrades are a pain. Triggers must be deployed with “UPDATE” instructions, it’s not as easy as keeping code in git.

simonw 4y ago

I think this problem can be robustly solved if you have the right mechanisms in place: 1. Migrations. Your schema needs to live in version control, and changes to your schema must be applied by an automated system. Django migrations are the gold standard here in my opinion, but you can stitch together a custom system if you need to, one that tracks which migrations have been run already and provides a mechanism to apply new ones. 2. Automated tests. Your triggers MUST be covered by automated tests - call them unit tests or integration tests depending on your preferences, but they need to be exercised. Don't fall into the trap of mocking your database in your tests! With these two measures in place I'm 100% comfortable using triggers that split my business logic between my application code and my database code.

ysavir 4y ago
But what problem is moving to triggers solving? Is keeping all your logic in a single place actually a problem, or is moving part of it to Postres tempting because it's nifty? How much training will you have to provide for new hires to be able to run with Postgres triggers instead of just Django logic?
- simonw 4y ago
  Triggers protect you against bugs. If your application logic needs to remember to update a denormalized column somewhere it's very easy for a bug to slip in in the future when someone adds a new piece of Python code but forgets to update the denormalized column. With triggers you can eliminate that source of bugs entirely. This is even more valuable if you have more than one system interacting with a single database.
  - paulryanrogers 4y ago
    I did this with robust DB constraints and selective use of triggers and stored functions. It can feel like a strait jacket long term. Though it did help protect against less competent DBAs breaking things and avoid some footguns.
  - SOLAR_FIELDS 4y ago

whstl 4y ago

Business rules are already often in multiple separated places in a codebase. If you mean "it should be in the same git repository", then triggers should be already in migrations. But IMO, even migrations are a stopgap solution to this problem. In some cases, such as in ORMs like Rails' ActiveRecord, even the column names aren't present in the model by default, they are only migration files. The real solution is putting the definition of the triggers (and all other database things too) closer to the code that operates on them. In models, for example. And models should enforce that only the defined triggers are present in the database. Of course, since most ORM users aren't really big DB users, this isn't exactly common. Until we get features like this, I agree that triggers can be confusing.

cgh 4y ago
If you are using some JPA implementation, eg Hibernate, then JPA events are more or less what you are talking about except JPA can't enforce the condition that JPA-defined events are the only triggers present. If you write custom triggers then you run the risk of stale entity data being presented to the user. But otherwise, this fulfills the condition of triggered events residing in the codebase. Of course, this means you have to be comfortable with using an ORM in the first place.

brightball 4y ago

You can say similar things about any API based development though. Triggers are just a part of the database API. Databases exist to manage data, not just store and retrieve it.

avereveard 4y ago

it is also a very restrictive environment, you either only trigger on a single in transaction table, or you risk having triggers tripping other triggers, limiting the approach scale anyway. at which point a well managed transaction from an active record or a data gateway will do miles better.

citrin_ru 4y ago

You have exactly the same problem in microservice architecture while calling a remote endpoint.