4 ms·
As a developer I always hated this feature in other rdbms. The idea of your app saving a row and retrieving it only to have more data in it. IMO these types of
by redact207 7y ago
As a developer I always hated this feature in other rdbms. The idea of your app saving a row and retrieving it only to have more data in it.
IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing. It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions.
Perhaps there are legitimate uses of this, maybe for DBs that aren't just repositories for apps?
- Ididntdothis 7y agoMaybe there is a performance benefit to calculating values once at update time vs millions of times during query?
- luismedel 7y agoI'm not a db-engine expert, but if I'd have to write this functionality myself, I'd perform the computation only when any of the involved columns change, not on every read. EDIT: sorry, I read your comment on the opposite way. I see we are saying the exact same thing :)
- redact207 7y agoWouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
- filleduchaos 7y agoAnd if another consumer of the DB doesn't do the calculation or, even worse, does the wrong calculation?
- baq 7y agowhat if you have 50 apps talking to the same DB?
- yourad_io 7y agoSo long as everyone, everywhere, always remembers to calculate it in the same way for all inserts and updates. This seems a lot more robust to me.
- GordonS 7y agoYou can use abstractions that push such code out of the developers concern, into the "code infrastructure" layer. But there are also plenty times where I'd rather compute at the database side; there are no silver bullets.
- magicalhippo 7y agoAnd then someone at support comes along and updates the data via SQL and forgets to update the derivative field because he got woken up at 2am for an emergency. Using a computed column ensures the data is consistent.
- benjohnson 7y agoYes, but increasingly for us we tend to have several "apps" accessing our data: The mobile, desktop and API 'apps' are all different code bases for us.
- FroshKiller 7y agoIf not a performance benefit, at least minuscule energy savings.
- myrryr 7y agoIt is great for geospacial. You want centroids and bounding boxes precalced for all the geom you are pushing? Now you can. Otherwise these can be pretty expensive operations.
- eb0la 7y agoAlso great for BI. Most BI tools make a lot of calculations in their queries just to get the data as they want. With this, you can get that calculations pre-made and persisted except when you make a backup.
- naranha 7y agoOne legitimate use case could be calculated values that you want to use in several apps (written in different languages) and in SQL reporting.
- aaronharnly 7y agoI’m not saying this is the right solution, but we do face the problem of application-calculated attributes that have meaning in an API and in the application, but are not persisted to disk. A trivial example from our education domain might be % correct, assuming we’ve persisted the possible score and an achieved score on a question or test. When we ELT to the data warehouse, analysts and internal users want to report on reason about these calculations. But then we face the quandary — do we re-implement that business logic in the ELT? Or do we go back and make sure we persist everything, even these values that are so quick and light to calculate and build in the application later? Or do we (shudder) make the ELT load from a bulk API, rather than just copying across the DB? I could imagine someone talking themselves into using these calculated columns as a solution. I wouldn’t recommend it, but I can see the ELT problem prompting it.
- dragonwriter 7y ago> IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing. I can write a test for the db logic, too. > It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions. Its a lot easier to be confident that all consumers of the DB, regardless of whether they are coming through a particular app, have the correct view of the data if the non-app-specific domain logic is in the database.
- LunaSea 7y ago> I can write a test for the db logic, too. I agree but I would add that having testing niceties like branch coverage isn't really possible for SQL queries / PGPLSQL functions.
- pnako 7y agoBut you don't need that. That's an issue for whoever implemented your DBMS. To test your queries all you need are unit tests of the standard form: for given inputs, assert(output).
- LunaSea 7y agoI disagree. If my query calls a PGPLSQL function, I'd like to be able to test and branch cover it.
- grzm 7y agoIf you’re the one implementing the functions (or even if you’re not), there are tools such as pgtap that can help you with testing. I’ve used pgtap successful on a number of projects. There’s also no reason you can’t test the behavior of functions through a driver in some other language, though you’re now one step removed. I’m not aware of any coverage tools, though it’s been a while since I’ve looked. https://pgtap.org/ https://pgtap.org/
- GordonS 7y ago
- extrapickles 7y agoIt really depends on what your app does, and how software development is structured at your company. In general though, materialized views and computed columns are much more useful for reporting than replacing application logic. They also can be useful to smooth out upgrading line-of-buisness software, where say the old system needs several separate values, but the new system only needs 1, and derives the others. You would use something like this to make the 2 systems play nice until the migration is complete.
- goatinaboat 7y agoIMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing 1) the assertion that code in the DB can’t be tested is a bizarre and unfounded one 2) in any serious organisation there may be dozens of apps in a dozen different languages talking to the DB. Do you seriously propose implementing the same thing in each one, or doing it once in the DB and knowing it’s correct for everyone?
- AmericanChopper 7y agoI disagree with the parent comment, but tucking logic away in different parts of your DB does come at a cost. It increases the burden on the engineer who’s trying to understand it. Read the code > look at the schema is no longer enough. Now you also have to know where all of the strongly coupled business logic is inside the DB. Triggers and views can be especially dangerous in a complex system, and having to keep a mental view of how all these discrete resources work together can lead to all kinds of failure.
- taffer 7y agoTriggers tend to generate all kinds of surprises when you change something in a part of your database and suddenly other seemingly unrelated things begin to change. Views can be hard to understand if you have views that use views, use views, and so on. But I don't see any problems with functions and stored procedures as long as you put them in a separate schema from your tables. A function in SQL shouldn't be more tightly coupled than a function in Ruby or Java.
- goatinaboat 7y agoTriggers tend to generate all kinds of surprises Why is a trigger any more surprising than any callback style interface? Or using inotify (Linux) or reparse points (Windows)? Triggers are very easily discoverable, they are attached along with their source code to the table! A view is just a named select statement, that’s all it is.
- phoe-krk 7y ago> where you can at least write a test and assert it's doing the right thing SQL is code. PL/pgSQL is code. Code can be tested. Code should be tested. You don't even need to leave the database to write tests for SQL or PL/pgSQL[1]. [1] https://pgtap.org/ https://pgtap.org/
- FroshKiller 7y agoYou would only get more data if you queried for *, and you should know better than to do that.