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

31–40 of 199 posts

Re: New In Postgres 12: Generated Columns

#31

Post by Ididntdothis »

dewey wrote: post #15
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…

I think it’s not only the procedural vs declarative difference but also just the plain syntax. To me SQL is just hard on the eyes. It feels a little like FORTRAN in the good old days.

Re: New In Postgres 12: Generated Columns

#32

Post by sorokod »

naranha wrote: post #23
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…

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.

Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.

Re: New In Postgres 12: Generated Columns

#33

Post by phoe-krk »

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…

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

Re: New In Postgres 12: Generated Columns

#34

Post by ludwigvan »

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…

This thinking really intrigues me, as it seems like it fell out of fashion (at least for greenfield projects), but it might have a come back.

Could you share a few cases where it is better to use a custom type as opposed to a relation? The only things I can think of are generic things like uuid. Would there be a need to create an Employee type vs an Employee relation?

Also, how is the experience with using Python for stored procedures? One reason they are not used is that the language pl/sql is non-familiar to most. If anyone has any experience with that, could you share some of your thoughts?

Re: New In Postgres 12: Generated Columns

#35

Post by LunaSea »

dragonwriter wrote: post #16
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…

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

Re: New In Postgres 12: Generated Columns

#36

Post by BjoernKW »

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…

You’re basically experiencing impedance mismatch first hand ;-)

The term is usually applied to mapping from object-oriented to relational systems and vice versa but it’s perhaps more of or also a mismatch between imperative and declarative programming styles.

I think we just have to accept that these two are different and that each is great at what it does instead of trying to shoehorn a relational approach into object-oriented and imperative ones.

Re: New In Postgres 12: Generated Columns

#37

Post by pnako »

sorokod wrote: post #32
naranha wrote: post #23

Earlier quoted context omitted.

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.

Minimizing the amount of busines logic in the dB is exactly what makes the dB structures long lasting.

Clearly the feature should not be abused, but calculated columns are great for stuff that's obvious and always true (i.e. independent from applications), e.g. a trade value computed from a trade unit price and a trade quantity.

This way I can select the top N trades for a given key without having to do the computation in the application, or storing redundant information in the DB.

Re: New In Postgres 12: Generated Columns

#38

Post by dtech »

arethuza wrote: post #28
atroche wrote: post #20

Earlier quoted context omitted.

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.

PostgreSQL is a pretty good document store, and when you need it you have the relational model available and integrated.

Re: New In Postgres 12: Generated Columns

#39

Post by pnako »

LunaSea wrote: post #35
dragonwriter wrote: post #16

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

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

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

Re: New In Postgres 12: Generated Columns

#40

Post by ako »

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.

No, putting data logic in a service is putting logic in the wrong layer. Data logic should live in the data layer.
Post reply on HN