161–170 of 199 posts
Earlier quoted context omitted.
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
Earlier quoted context omitted.
Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
So 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.
But there are also plenty times where I'd rather compute at the database side; there are no silver bullets.
Earlier quoted context omitted.
Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.
> Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting. The trick is to keep declarative business logic in the database layer, and imperative business logic in the application layer. This allows a large team of developers to move quickly without breaking things.
Earlier quoted context omitted.
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 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 kn…
I 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 m…
Earlier quoted context omitted.
> The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. So, just like columns in a view. > These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. Yeah, in SQL you could do that with a correlated subquery in a column definition in a view, instead of a join, but…
Yes, for the common case a view does the trick. However, I-Descriptors have the power of the underlying UniVerse BASIC runtime at their disposal, so you can do a LOT of things. I mention one of these in another comment[0]. However, as I mentioned, doing it this way isn't necessarily a good idea. [0] https://news.ycombinator.com/item?id=21137935
I tend to follow the Postgres releases and I am always impressed by the cool things SQL databases can do. But for programmers like me who are used to code in C++/C# I always find the transition from these languages to SQL too harsh. Especially if you don’t have to do SQL daily it’s really hard to remember the syntax and read complex SQL code. Also the transition from SQL results to typed languages is tedious. ORMs he…
I think you need a visual model that works for you to approach it since they're entirely different beasts. You'll hit a wall very soon if you approach learning SQL in terms of C++/C#. CSS is a similar phenomenon, you can't approach it as code. The box model is a nice way to get the basics and manipulate the DOM. Similarly, I think in terms of tables (rows and columns) to visualize SQL operations that I need to do. Ev…
Mozilla put out a good video about this today: https://www.youtube.com/watch?v=aHUtMbJw8iA
Earlier quoted context omitted.
I 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 m…
Triggers 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 ti…
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.
Earlier quoted context omitted.
Why do you think placing business logic into the database system is a layering violation? Just because most developers use their DB as a dumb store doesn't mean it needs to be. There are also plenty of successful software systems that place the majority of their business logic and use a generic programming language and runtime only for the presentation layer. If you're comfortable fully exploiting the capabilities of…
There's no clear separation in databases between data definition and application logic. And this separation is very useful. You can change application logic very easily. Just stop old application and start new. You can use multiple application instances to balance load in many cases. You can often rollback bad application update. There's absolutely no problem to use miriads of development tools from Git to CI systems…
* Change your application logic very easily.
* Use a transaction to deploy new code! Zero downtime! [2]
* Use read replicas to balance load in many cases.
* Rollback bad application updates
* Test anything [3]
* Use miriads of development tools from Git to CI systems.
* Use row level security, so that every user can only see his own data [4]
* Only allow applications to call your code schema, never let them touch your data directly.
[1] https://www.postgresql.org/docs/current/ddl-schemas.html
[2] https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...
[4] https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Earlier quoted context omitted.
From the article: > ” Such functionality was earlier usually achieved with triggers, but with generated columns this becomes much more elegant and cleaner.”
"much more elegant and cleaner" is a bit of an overstatement in my opinion...
"Indexes: Generated columns can be used in indexes, but cannot be used as a partition key for partitioned tables."
I also hope it can be used for a partition key in the future.
The first database I used professionally was called UniVerse[0], currently owned by Rocket Software. It's a Pick-style, non-relational database. In the data dictionary for a file you could create I-descriptors, which were computed columns much like this feature allows. The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. These were co…
I've also used this product but it's more of an operating environment than just a database. Super convenient on the front end. On the down side, getting data out of it is extremely expensive if you aren't targeting a single record ID. It's also not ACID compliant.