New In Postgres 12: Generated Columns • Hacker News Board

Re: New In Postgres 12: Generated Columns

#21

Post by bauerd » Wed, Oct 02, 2019, 9:33 AM UTC

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 much prefer it to arcane JS(ON) query formats someone dreamed up in a hurry, only to avoid SQL. Only exception I can think of was RethinkDB

Re: New In Postgres 12: Generated Columns

#22

Post by eb0la » Wed, Oct 02, 2019, 9:35 AM UTC

myrryr wrote: post #7

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

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

Re: New In Postgres 12: Generated Columns

#23

Post by naranha » Wed, Oct 02, 2019, 9:35 AM UTC

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…

Also in my experience DB Schemas often (but not always) outlast logic implemented in applications. The more the business assumptions reside in the database, the easier it becomes to rewrite applications on top of it later.

Re: New In Postgres 12: Generated Columns

#24

Post by redact207 » Wed, Oct 02, 2019, 9:36 AM UTC

Ididntdothis wrote: post #5

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…

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

Re: New In Postgres 12: Generated Columns

#25

Post by petereisentraut » Wed, Oct 02, 2019, 9:36 AM UTC

dragonwriter wrote: post #11

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…

This is also much faster than the equivalent using a PL/pgSQL trigger.

Re: New In Postgres 12: Generated Columns

#26

Post by goatinaboat » Wed, Oct 02, 2019, 9:36 AM UTC

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

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?

Re: New In Postgres 12: Generated Columns

#27

Post by jeltz » Wed, Oct 02, 2019, 9:41 AM UTC

atroche wrote: post #20

stupidcar wrote: post #13

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…

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

Or arguably data types.

Re: New In Postgres 12: Generated Columns

#28

Post by arethuza » Wed, Oct 02, 2019, 9:43 AM UTC

atroche wrote: post #20

stupidcar wrote: post #13

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…

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

You probably wouldn't use multiple tables or multiple columns either and just have a single table that stores document-like rows... which vaguely reminds me of something.

Re: New In Postgres 12: Generated Columns

#29

Post by throwaway_bad » Wed, Oct 02, 2019, 9:49 AM UTC

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…

There's definitely a language/paradigm switching cost if you jump back and forth too much.

What I found helpful was to prototype all the migrations and queries (of whatever feature or user flow you're building) in a .sql file first with mocked values. You'll be surprised by how much you can do if you're simply forced to write pure sql. Once you've reached the limit of what you can do you can move the queries back into your app to wire the rest up. This is usually not much work if you use raw queries instead of ORMs. You usually end up writing a lot more efficient apps this way!

Re: New In Postgres 12: Generated Columns

#30

Post by wildduck » Wed, Oct 02, 2019, 9:52 AM UTC

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…

Scaling and replication! It is easier to scale horizontally the application layer than the db layer. Application/code has better debugging tools, IDE.