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

81–90 of 199 posts

Re: New In Postgres 12: Generated Columns

#81

Post by philliphaydon »

SigmundA wrote: post #58
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…

Having done both SQL and C# for a very long time I prefer C# with Linq. Linq gives you nearly all the declarative power of SQL with all the procedural goodness of C# to get things done. Whats needed is a really good object database for C# to eliminate the impedance mismatch. Don't get me wrong SQL is great, but to be useful it needs to be mixed with a procedural language (pl/SQL, T-SQL, etc) I would just prefer using…

MartenDB. :)

Re: New In Postgres 12: Generated Columns

#82

Post by mcny »

pnako wrote: post #37
sorokod wrote: post #32

Earlier quoted context omitted.

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.

Not postgresql but I saw a start date and an age in days columns in MS SQL server. The age gets updated daily. It didn't sound right to me. I'm pretty sure I'd fail my database class in college if I did that. What is different in real life and why didn't they teach me this in college?

Re: New In Postgres 12: Generated Columns

#83

Post by vbezhenar »

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…

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.

Changing database schema is a big deal. It might take a lot of time or it must be done with great caution to keep database online. It's hard to properly version it and it's often hard or just impossible to roll back bad update.

Generally database is state and application is stateless. You can couple it, but decoupling works better.

Re: New In Postgres 12: Generated Columns

#84

Post by DougBTX »

skrebbel wrote: post #69
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…

I love what you're saying, but running a normal modern development and deployment cycle on code stored in databases is hell . Until db vendors start taking developer happiness seriously, stored procedures and triggers are a total non starter for any serious work. I mean, in most databases you can't even rename a column without causing an enormous blocking migration. What? Why can't this happen in the background? Why…

Any DB that requires moving large amounts of data on disk to do a column rename sounds pretty lame.

I’d expect most DBs to be like this: https://dba.stackexchange.com/questions/189794/performance-i...

A rename should be just a metadata change.

Re: New In Postgres 12: Generated Columns

#85

Post by Mister_Snuggles »

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 commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. An INVOICES file, for example, would have fields like CUSTOMER.NAME, CUSTOMER.ADDRESS, etc, (note that the '.' is just another character in the field name, it doesn't actually mean anything to the database) which would pull the relevant information from the customer file or call a subroutine to find the relevant information (e.g., in a history file).

The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch. It also leads to some interesting gotchas.

I don't have a good reference handy for UniVerse's I-descriptors, but the System Description document[1] has a section on it.

It had a certain elegance that I miss in modern SQL databases. On the flip side, modern SQL databases are so much more powerful.

[0] https://www.rocketsoftware.com/products/rocket-universe-0/ro...

[1] https://docs.rocketsoftware.com/nxt/gateway.dll/RKBnew20%2Fu...

Re: New In Postgres 12: Generated Columns

#86

Post by ako »

grzm wrote: post #59
ako wrote: post #57

Earlier quoted context omitted.

Make a trigger

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

Re: New In Postgres 12: Generated Columns

#87

Post by zo1 »

misterdoubt wrote: post #71
Ididntdothis wrote: post #31

Earlier quoted context omitted.

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.

Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.

I find that it helps differentiate the different components of the query. Same way we use all-caps for constants, etc. If it's lower-case, or camel-case, then it just sort of "melts" into the rest of the query.

Re: New In Postgres 12: Generated Columns

#88

Post by dewey »

noir_lord wrote: post #76
dewey wrote: post #15

Earlier quoted context omitted.

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…

Book looks interesting but the reviews are a bit of a mixed bag mostly about copy editing, has that been your experience?

So far I didn't notice anything.
Post reply on HN