This is indeed very nice for maintaining things such as indices for full text search. Solutions based on triggers never felt easier to maintain because triggers were one step removed from the table itself.
New In Postgres 12: Generated Columns
91–100 of 199 posts
Re: New In Postgres 12: Generated Columns
#92I 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…
[deleted]
Re: New In Postgres 12: Generated Columns
#93Earlier quoted context omitted.
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...
Sounds like the generated column implementation is faster than a PL/pgSQL trigger.
https://news.ycombinator.com/item?id=21134706
I don’t have a link at hand from a discussion on the mailing list, but I’ll take Peter’s word for it.
Re: New In Postgres 12: Generated Columns
#94Earlier quoted context omitted.
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 dra…
> All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data. > Put complex logic…
> You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data.
That all just sounds a touch complicated compared to data in database, code somewhere else (like git). I'm circling back to the same point in a couple of different ways, but pgSQL specialises in the relational model of data. That isn't a great data model for code and there are already better ways to manage code than shoehorning it into the database. Its cool that it is possible, and I'm not saying that someone who does is making a mistake. But I also don't think they are gaining an advantage and there is a really easy opportunity to separate out where bugs can occur from where data lives.
> How often do you plan to migrate to another database? In my experience this almost never happens in reality, but the layers above come and go.
If you are using a database for its advanced general purpose programming capabilities? The chances probably start to get more likely.
Databases that are a store of data don't need to change because they already do their one job (disk relational model translation) really well. If they are pressured to do 2 or 3 tasks like business logic then suddenly it is a lot more likely that there will be pressure to swap databases.
If I were using SQLite and someone wants to do fancy triggers then maybe I need to swap to PostgreSQL. Avoiding that sort of decision making is a great reason to seal the data completely away from the code.
Re: New In Postgres 12: Generated Columns
#95I wonder if a generated column can be referenced as a foreign key. Weirdness wouod probably ensue, so I'd guess... No?
Yes. A generated column can be included in a foreign key and be referenced by a foreign key. Nothing too weird happens. It's basically like having every update and insert specify the column value.
Re: New In Postgres 12: Generated Columns
#96Earlier quoted context omitted.
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. :)
Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch.
I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types.
Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accepts code snippets, compiles runs and returns results. Of course you would need proper sandboxing like .net fiddle. You could also do something in between like serializing Linq expression trees to send them to server for processing.
In an ideal world where your code runs (client, app server, db server) is a choice based on locality needs not forced by runtimes (javascript in the browser, .Net on the app server, pg/Sql on the db server).
You can pretty much get close to this now with javascript/node/plV8, but again I prefer C#/.Net.
Re: New In Postgres 12: Generated Columns
#97Earlier quoted context omitted.
MartenDB. :)
Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accept…
So... Marten DB...
Re: New In Postgres 12: Generated Columns
#98Earlier quoted context omitted.
Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accept…
So... Marten DB...
As far I know Marten doesn't run .Net in the PG process and just converts .Net objects to JSON and Linq statements into SQL. Am I missing something?
Re: New In Postgres 12: Generated Columns
#99I've been using this for a while in a hobby project, great feature which makes for neat declarative schemas.
Unfortunately some tooling is choking on this, for instance datagrip crashes om introspection, but hey that's what we can expect for being early adopters. :-)
Re: New In Postgres 12: Generated Columns
#100Earlier 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.
Foreign key constraints are not business logic, they're part of a sound and sane database architecture (just like basic indices on heavily-queried columns). For an overwhelming majority of use cases, tables should have them; one needs a very good reason why a table shouldn't have them.