Those are problems in MS Sql, certainly not in object relational DBs such as Postgres or Oracle.
And its rather sad that instead of embracing that we ended up with Json as poor man replacement for such advanced usages.
I guess non portability across DBs certainly doesnt help.
I tried showing that 10 years ago: https://github.com/ngs-doo/revenj/ but it just resulted in confusion.
There's a huge list of caveats in Postgres's inheritance documentation [1], and even the obvious issues haven't been touched in decades. I've never heard of anyone using this feature.
At this point, I would say Postgres itself is a tool that doesn't support Postgres inheritance.
Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.
The impedance mismatch is not about having to translate, but being unable to translate. An yes, that problem exist with relational-only database, but not with PG where you can create composite types, can have arrays of composite types as a column and all other goodies which come with that.
I personally do not find useful to execute .NET code in PG, but technically you could do that.
As long as you can translate LINQ expressions to PG as you can most of them with Revenj you can avoid the pitfalls of mainstream ORMs.
Not sure I have seen a type or construct that could not be translated one way or another. The mismatch is the need for translation. Table per hierarchy, table per subclass etc.. References vs foreign keys. Instance identity vs primary key.
The ability to run procedural code on the db server allow for much better performance than sending data across the network to the app server for processing. Being able to share procedural code between app server and db allows for the choice to be made easily based on the best place to run it rather than whether it might have to be rewritten to move from one to the other. I suppose a plugin could be made for .Net in PG similar to plV8, SQL server has had .Net stored procedures some time with many issue and caveats so it doesn't get much use.
The problem with Linq and what makes it so nice to use is the ability to mix procedural code in the Linq statement ex: list.Where(x=>x.SomeMethod()) Very easy to make something that can't be translated and then starts streaming the entire intermediate result to the app server for processing SomeMethod that would be much more performant in the db tier local to the data.
I’m glad to see that binding data with document templates is slowly becoming used more often. I always saw it as a killer feature, and actually built one of my products around that idea way in 2011 [1].
Once you leave the simple use cases of just replacing tags it gets quite complicated, but you can build really complex documents that way [2].
You can do even more advanced stuff in Postgres, but they've changed the rules to dissalow it since it much more faster than the the current best ones. Something about being fair to other frameworks...
> Can you request multiple heterogeneous result-sets from PG now, with something that looks like a stored procedure?
Not prettily. You can return cursors, you can use json etc, ...
But you can pipeline SQL statements. I.e. just send N SQL statements (including bind parameters etc, the protocol is the same) without waiting for results, and then process the results as they come in. If you want to avoid latency penalties that makes much more sense in my opinion than having to wrap multiple statements in a function.
Dammit, I re-read the paper and I was wrong, you were right. But I can't delete/change my comment, so please downvote away. (I hate wrong information on the internet, and to be the source of it is a horrible feeling. Sorry.)
Not worth much. MongoDB is a completely different beast after version 3.0 with WiredTiger being the default.
But I am sure that there are use cases for which PostgreSQL is going to be much faster in particular single field lookups on unindexed fields across all records.
My point is that it's never black & white to say Database A is faster than Database B. It's always use case dependent.
The great thing with LISTEN/NOTIFY is they work inside transaction boundry.
This way you can get correct cache invalidation inside inside multiple table changes really easy. But if you cant use transactions that doesnt seem helpful.
