Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New In Postgres 12: Generated Columns

pgdash.io

Post reply on HN
Print view View original

11–20 of 199 posts

Re: New In Postgres 12: Generated Columns

#11

Post by dragonwriter »

makach wrote: post #3

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.

> But after contemplating it, is this really necessary?

No, you could always do the materialized equivalent via triggers, so it's not necessary for correctness. And since this feature is limited to the materialized form, too, it doesn't offer much change other than simpler expression of what is going on (which, to be fair, is a big win.)

> I fear for putting business logic and meanings into the wrong the layer

If you don't have some mechanism for calculated columns, you are forced to put domain logic that belongs in the DB in the app layer, which is especially problematic in the (increasingly out of fashion, apparently) circumstance where there are multiple consumers of a DB, as it promotes inconsistency.

Re: New In Postgres 12: Generated Columns

#12

Post by eternalban »

pnako wrote: post #8
makach wrote: post #3

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.

If your database contains objects like "customer ID", "address" and the like, you already have business considerations (if not logic) inside it. In fact, businesses using databases for business stuff is probably 95% of DB use cases. If it's something that's not application-specific and fits nicely into the query language (like in the example given) it makes sense to have it inside the DB, because for queries involvin…

Arguably, a robust RDBMS with user defined functions (such as PostgreSQL) are the ideal place for core domain semantics.

What is missing is a modern tool chain that addresses the painpoints of such a development model.

Re: New In Postgres 12: Generated Columns

#13

Post by stupidcar »

makach wrote: post #3

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.

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 your DB, then the intelligent combination of a relational model with custom data types, constraints, triggers, views and stored procedures can make the DB the perfect place to implement business logic.

Re: New In Postgres 12: Generated Columns

#14

Post by aaronharnly »

redact207 wrote: post #2

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

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

Re: New In Postgres 12: Generated Columns

#15

Post by dewey »

Ididntdothis wrote: post #4

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 found this book to be very helpful, I'm not done yet but so far I'm really enjoying it.

"Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity."

https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/d...

Re: New In Postgres 12: Generated Columns

#16

Post by dragonwriter »

redact207 wrote: post #2

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

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

Re: New In Postgres 12: Generated Columns

#17

Post by extrapickles »

redact207 wrote: post #2

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

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

Re: New In Postgres 12: Generated Columns

#18

Post by comboy »

raphaelj wrote: post #9
Ididntdothis wrote: post #4

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…

Indeed. SQL concepts are insanely powerful, but the language itself feels a little bit old.

I think it aged insanely well. DBs are often a bottleneck and need to be heavily optimized so you need to spend some time explaining to your database what it is that you really need, but in general, SQL is all about telling what do you want and not worrying about how it's going to get done. That's how programming languages should look like.

We have so many ORMs and most often they look more ugly than pure SQL to me, especially when it comes to more complex queries.

You could write a translator (something like coffeescript -> JS but X -> SQL), but can you really come up with a syntax that's enough of an improvement that it would be worth using it?

Re: New In Postgres 12: Generated Columns

#19

Post by philshem »

makach wrote: post #3

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.

As an analyst, either for reporting or machine learning tasks, it scratches my itch.

I'm often creating wide, flat views with extended attributes. Many columns are nearly identical, for example, year as an integer, year a date type, etc. This is trivial with views, but ...

For fast query performance, I usually materialize those views. But that's a multi-step and often manual process, requiring schedules or triggers. This feature combines both of those worlds, with fewer total database objects. Especially with ALTER TABLE.

Re: New In Postgres 12: Generated Columns

#20

Post by atroche »

stupidcar wrote: post #13
makach wrote: post #3

At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.

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…

Agreed. After all, if you wanted to keep all "business logic" out of the db you wouldn't even use foreign key constraints.
Post reply on HN