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

171–180 of 199 posts

Re: New In Postgres 12: Generated Columns

#171

Post by mopierotti »

This seems like a cool feature, but would I be correct in thinking that:

1) If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? (https://www.postgresql.org/docs/current/indexes-expressional...)

2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expression you're using is, because you're saving time by precomputing the column rather than computing at query time.

Edit: I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases.

Re: New In Postgres 12: Generated Columns

#172

Post by AmericanChopper »

goatinaboat wrote: post #167
taffer wrote: post #164

Earlier quoted context omitted.

Triggers tend to generate all kinds of surprises when you change something in a part of your database and suddenly other seemingly unrelated things begin to change. Views can be hard to understand if you have views that use views, use views, and so on. But I don't see any problems with functions and stored procedures as long as you put them in a separate schema from your tables. A function in SQL shouldn't be more ti…

Triggers tend to generate all kinds of surprises Why is a trigger any more surprising than any callback style interface? Or using inotify (Linux) or reparse points (Windows)? Triggers are very easily discoverable, they are attached along with their source code to the table! A view is just a named select statement, that’s all it is.

I don’t think there’s anything wrong with any of these features, I’ve used all of them myself at various times. The problem though is that whenever you use them, you’re introducing additional complexity to your application, so you have to decide every time whether it’s worth it. If you use these features without proper consideration, you can easily end up with a mess of interdependent schema objects. It’s easy enough to get to a state where it’s difficult to visualize code flows, and in that case making changes will become riskier because all of those features can produce major gotchas.

Re: New In Postgres 12: Generated Columns

#173

Post by taffer »

goatinaboat wrote: post #167
taffer wrote: post #164

Earlier quoted context omitted.

Triggers tend to generate all kinds of surprises when you change something in a part of your database and suddenly other seemingly unrelated things begin to change. Views can be hard to understand if you have views that use views, use views, and so on. But I don't see any problems with functions and stored procedures as long as you put them in a separate schema from your tables. A function in SQL shouldn't be more ti…

Triggers tend to generate all kinds of surprises Why is a trigger any more surprising than any callback style interface? Or using inotify (Linux) or reparse points (Windows)? Triggers are very easily discoverable, they are attached along with their source code to the table! A view is just a named select statement, that’s all it is.

> Why is a trigger any more surprising than any callback style interface?

A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application is full of triggers, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can trigger thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not.

In summary, triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases even without side effects.

Re: New In Postgres 12: Generated Columns

#174

Post by combatentropy »

mopierotti wrote: post #171

This seems like a cool feature, but would I be correct in thinking that: 1) If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? ( https://www.postgresql.org/docs/current/indexes-expressional... ) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expre…

> If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions?

Good point!

> I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases.

You can do that also with a database view or function, which is my preference.

I prefer my tables to be fully normalized. Any computation or processing, I try to keep in views. This just helps my mind. Tables = hard data. Views = processed data. But maybe I'm just set in my ways. Calculated columns are in the SQL standard, after all, and have been implemented in other databases for some time. In special cases (heavy calculation + heavy reads) of course this feature makes a bit more sense.

Re: New In Postgres 12: Generated Columns

#175

Post by [deleted] »

SigmundA wrote: post #106
philliphaydon wrote: post #101

Earlier quoted context omitted.

Well you don’t need to worry about creation, and migrations. You only need to tell the store which objects it needs to know about. You declare everything in c#. It’s stores it as jsonb. So you don’t need to worry about weird relationships you only worry about your root aggregate. You can index it. Apply full text search. And persisted columns in the map if for convince or speed. So Yeah it maps the linq to the equiv…

Again that's great, its an ORM, that's what they do, try to abstract the DB from your language, and do their best to hide the impedance mismatch. However the mismatch is always there, the overhead of mapping types, inefficient serialization (key names repeated over and over in json vs a real schema), certain statements cannot be transpiled to sql so you have pathologic cases at unexpected times, in ability to run any…

[deleted]

Re: New In Postgres 12: Generated Columns

#176

Post by michaelper22 »

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.

Here's one of my current use cases: We have a folder tree table in our (formerly nested sets, then closure table, now materialized path), and we cache a materialized version of the path with the folder names. There are some places in the app where you can look up a node by its full path, and this column should conceptually be unique. Obviously an unindexed VARCHAR(2048) is bad to filter on, but MySQL indexes can only cover the first 767 bytes (we used utf8mb4). So we have another column, PathHash CHAR(40) AS (SHA1(path)) VIRTUAL, and then a unique index on that.

Re: New In Postgres 12: Generated Columns

#177

Post by dahdum »

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.

You’d hate my work, we shoehorn everything we can into the database, with a nice simple API layer of versioned stored procedures in front. It’s easy to test and enforce the business logic, and then it can be easily utilized across web, mobile apps, custom apps, and third parties.

Works great if you’re sure you won’t ever need to scale huge horizontally. One of my RoR apps connects to a schema of views, with instead of triggers calling procedures for updates. Something like 30 normalized tables are rolled up into 5 denormalized views, leaving hardly any ActiveRecord woes and still excellent performance.

Re: New In Postgres 12: Generated Columns

#178

Post by rpedela »

mopierotti wrote: post #171

This seems like a cool feature, but would I be correct in thinking that: 1) If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? ( https://www.postgresql.org/docs/current/indexes-expressional... ) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expre…

I think your edit is why I am excited about this feature. There are so many times when I just need some relatively simple text formatting, such as titlecase, but want to store the original text too. Titlecase isn't hard to do or expensive, but I only have to do it once with a simple SQL expression. Then client code can decide whether they SELECT the formatted or original text. This is especially helpful if I don't already have an ETL pipeline that includes a cleaning/formatting step, and I just need 1-2 columns to be formatted.

Re: New In Postgres 12: Generated Columns

#179

Post by pizza234 »

magicalhippo wrote: post #46
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…

SQL is a bit verbose at times, but it's not much different from going to other programming languages. In fact I'd say it's easier given that it's so verbose. I have no idea what all those modifier symbols do in Rust, say, but I find "case when InvoiceNo is null then" or "select Name, list(distinct Title) as Titles from" pretty transparent in comparison. The biggest mental difference I think is that you're dealing wit…

What specifically do you find verbose? The only thing I find redundant (based on the queries I handle) is that theoretically join arguments could be deduced from foreign keys (maybe extending this behavior, having joins automatically deduced).

I also wonder if the perceived verbosity is caused by some programmers cramming logic into the queries, rather than keeping them simple and processing the results in the client language.

Re: New In Postgres 12: Generated Columns

#180

Post by Mister_Snuggles »

macdice wrote: post #170
fleetside72 wrote: post #153

Earlier quoted context omitted.

I've also used this product but it's more of an operating environment than just a database. Super convenient on the front end. On the down side, getting data out of it is extremely expensive if you aren't targeting a single record ID. It's also not ACID compliant.

I encountered this PICK stuff a couple of decades ago. It was really enlightening. I began to understand how the features of relational databases (transactions, isolation levels, constraints, normal form) fit together to avoid anomalies and orphans. They had jobs that would search for orphan references etc at night.

Some of these things are making their way into the Pick world.

UniVerse, for example, gained proper transaction support at some point in its lifetime. When I showed the consultant developer, an old-school Pick guy, how that worked he was very impressed.

Granted, it was fairly limited since they somewhat faked it using the regular locking mechanisms and it was only usable within UniVerse BASIC, but it did what it said on the tin. I ended up writing my own simple command interpreter which allowed me to use the UniVerse command prompt and BEGIN/COMMIT/ROLLBACK transactions.

Transactions are definitely foreign to a LOT of Pick code though. I also worked on an ERP system that ran on a Pick-style system - if it crashed during GL posting it was completely possible, and this did happen frequently, that you would get a half-posted entry and an out-of-balance ledger. Had they just used the transactions that the underlying database supported they would not have had that problem.

The equivalent in the SQL world is to use your database connection in autocommit mode, which is pretty rare. Most developers know to use transactions or work with a framework that intelligently uses transactions.

Post reply on HN