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

101–110 of 199 posts

Re: New In Postgres 12: Generated Columns

#101

Post by philliphaydon »

SigmundA wrote: post #98
philliphaydon wrote: post #97

Earlier quoted context omitted.

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?

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 sql for you. But allows you to work with your object and worry less about persistence. Cos you can add and remove properties. It’s the closest thing I’ve come to, to being able to forget about the database and focus on code.

I Guess unless you write your own database from the ground up so it supports the language as a feature without dropping back to something else. It won’t happen.

Re: New In Postgres 12: Generated Columns

#103

Post by victor106 »

krn wrote: post #62
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.

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

Very Interesting. Never heard this before.

Can you give some examples of what would constitute declarative business logic vs imperative business logic?

Re: New In Postgres 12: Generated Columns

#104

Post by pletnes »

zo1 wrote: post #87
misterdoubt wrote: post #71

Earlier quoted context omitted.

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.

Not with syntax highlighting.

Re: New In Postgres 12: Generated Columns

#105

Post by shrumm »

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 think you need a visual model that works for you to approach it since they're entirely different beasts. You'll hit a wall very soon if you approach learning SQL in terms of C++/C#.

CSS is a similar phenomenon, you can't approach it as code. The box model is a nice way to get the basics and manipulate the DOM.

Similarly, I think in terms of tables (rows and columns) to visualize SQL operations that I need to do. Every operation is like a matrix operation, so you need to stop thinking in terms of for/loops iterators and think of matrix/table wide operators. Once you've got the basics, then you can look at stuff like window functions to do more advanced operations.

Takes a bit of working with to get used to. That's just how I think about it - you'll need to find a metaphor that works for you!

Re: New In Postgres 12: Generated Columns

#106

Post by SigmundA »

philliphaydon wrote: post #101
SigmundA wrote: post #98

Earlier quoted context omitted.

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?

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 statement you want in the db tier to take advantage of locality and so on.

Most everyone wants a object database since most people are working with objects which is why ORM are so popular.

Closet thing I ever saw was db4o.

Re: New In Postgres 12: Generated Columns

#107

Post by ilkhan4 »

SigmundA wrote: post #96
philliphaydon wrote: post #81

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

I'm with you on this one. I was thinking it would be cool to "bake in" the DB layer of an application and have it run in-process. Then you'd just provide it with block storage and it would handle the rest without having to have a separate server. You could even do compile-time migration and index generation based on your queries. With a distributed actor framework like Akka.NET or Orleans, it could probably be made to scale too.

The only downside is that the only interface to your data is through the application, but there's certainly a use-case for something like that.

Re: New In Postgres 12: Generated Columns

#108

Post by asah »

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.

Putting business logic in the database has been a boon to large companies for decades - while the development process can be trickier, it helps ensure that every team is using the same logic (incl version) to access the data.

With PostgreSQL, you can define business logic in everyday languages, including JavaScript (plv8).

Re: New In Postgres 12: Generated Columns

#110

Post by krn »

victor106 wrote: post #103
krn wrote: post #62

Earlier quoted context omitted.

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

Very Interesting. Never heard this before. Can you give some examples of what would constitute declarative business logic vs imperative business logic?

Declarative programming expresses the logic of a computation without describing its control flow.

This new feature in PostgreSQL is a great example of that: generated columns (declarative logic) were introduced to reduce the need for triggers (imperative logic).

In SQL, declarative logic consists of constraints, indexes, views, and prepared statements. They can significantly increase the efficiency and reliability of the entire system. Imperative logic is mostly triggers and stored procedures, both of which can become hard to maintain and scale.

Post reply on HN