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

61–70 of 199 posts

Re: New In Postgres 12: Generated Columns

#61

Post by ddorian43 »

aidos wrote: post #56
ddorian43 wrote: post #52

Earlier quoted context omitted.

Make a function ?

Do you mean an application layer function, or a dB one? And if a dB one, you also need to create an index to go with it. And then there are dragons that you don’t need in life. If an application one, then people need to know to call it, so, yeah. I know there are things you can do, but having better tools available is always a win (especially when they remove later maintenance from the equation).

In DB. You can keep it with migrations. You can have a functional index without creating a new column.

Re: New In Postgres 12: Generated Columns

#62

Post by krn »

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.

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

Re: New In Postgres 12: Generated Columns

#63

Post by another-one-off »

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…

All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. If it doesn't make sense to compartmentalise data and logic, why compartmentalise anywhere? Do the whole project in one big file. Of all the surprises a project is going to face 'oh, this data is useful for [new thing]' is one of the most likely. And everyone expects to find a boundary drawn there because it is such an obvious place to draw one; so it saves on confusion.

Putting complex business logic in the database is opening up all sorts of interesting new ways for data to be unavailable, corrupted or complicated to access. It is easy to imagine it working out well for simple requirements where there just needs to be something that works.

PostgreSQL is a piece of software that takes data and enforces the relational data model on it. Great idea. But the relational model of data is really only tuned to relational algebra. Put complex logic in there and all that is really being accomplished is now you can't migrate away from PostgreSQL. Relational databases already have great integration with every other programming language in current use.

Re: New In Postgres 12: Generated Columns

#64

Post by Matt3o12_ »

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…

The last time I tried to do that, I struggled a lot with error handling. The errors your database give you for schema violations aren’t really user friendly, so I have to convert them to proper errors (you must not enter negative amounts). When using SQLite there didn’t seem a way to know what column cast the error without parsing the string. That often lead that I had to implement the business logic twice. One check for in the code for the error handling and another one in the database. That was also often quite inefficient and prone to race conditions (if using improper isolation). For example if the table has two unique fields, I cannot just check for a unique constraint violation because it doesn’t tell me the column in the error data structure.

Looking at the Postgres driver it seems easier but is there any good tutorial on how to do error handling properly for databases?

Re: New In Postgres 12: Generated Columns

#65

Post by AmericanChopper »

goatinaboat wrote: post #26
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 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 mental view of how all these discrete resources work together can lead to all kinds of failure.

Re: New In Postgres 12: Generated Columns

#66

Post by perlgeek »

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.

It's super helpful for database migrations.

Say you suddenly don't have data for a column anymore, because the API that you read from has stopped providing it. Now you can add a computed column that uses a heuristic as an interim until you have time to remove all the reads from that column.

Or say that you have an object with a separate display name, and product management decides that the display name now can't be chosen freely anymore, but must be generated from name and title. A computed column can do that trick pretty well.

Re: New In Postgres 12: Generated Columns

#67

Post by [deleted] »

AhtiK wrote: post #55
aidos wrote: post #42

This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query…

This JSON plucking is also much easier now with the json path expressions coming up in v12!

[deleted]

Re: New In Postgres 12: Generated Columns

#69

Post by skrebbel »

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…

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 can't I have column aliases? The very basics of developer happiness aren't covered, let alone the harder bits, like versioning stored procedures, switching schema and code when switching to a new git branch, and so on.

(EDIT: of course there are open source tools that help with all of the above, but they're all swimming upstream, fighting a database that simply can't imagine change, and are usually terribly leaky abstractions as a result)

Re: New In Postgres 12: Generated Columns

#70

Post by Erwin »

An alternate way of doing this is to pass the entire current table row to a function which can be done easily: if you have a table "purchase" PG also creates a "purchase" type, so if you have this function:

    CREATE FUNCTION vat(a purchase) RETURNS numeric AS 'SELECT a.value * .25' LANGUAGE 'sql'
Then you can run:

    SELECT value, vat(purchase.*) FROM purchase;
And so be able to use every purchase column within the SQL function to do your calculation.

There are two interesting shortcuts: You can call just vat(purchase) because the type is an alias for the current table row. That alias is very confusing and this is not recommended (try select table from table!)

There's also a method like shortcut which the documentation calls "functional notation":

    SELECT value, purchase.vat FROM purchase;
This calls your VAT() function passing it the entire purchase row, see https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-US...

I don't actually know if PG can correctly inline the necessary code in here -- it is better able to do it if you use "SQL" function certainly.

Post reply on HN