Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PRQL: Pipelined Relational Query Language

github.com

Post reply on HN
Print view View original

201–210 of 214 posts

Re: PRQL: Pipelined Relational Query Language

#201

Post by slt2021 »

aidos wrote: post #185
mulmen wrote: post #73

Earlier quoted context omitted.

Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I al…

> CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...

neat trick!

sometimes you absolutely need to materialize your CTE, especially if it is one big computation and you refer it 4-5 times downstream.

Re: PRQL: Pipelined Relational Query Language

#202

Post by cmrdporcupine »

aerzen wrote: post #11
0cf8612b2e1e wrote: post #3

I really want this to take off and become a first party supported language for Postgres. Yes, yes, relational algebra is beautiful and all that jazz. SQL is a garbage, first pass design that should have been replaced decades ago. Even Codd has complaints about it. It is amazing what he invented, but we have learned a lot about PL design since then.

People really like to associate relational algebra with SQL, probably because they learned them one alongside another. But SQL is really terrible relational language - it breaks a few core concepts in different places, like relations being unordered sets, that you can ORDER BY. This bubbles up as relations losing ordering after being wrapped into a subquery, which is really unexpected. PRQL has a data model very simi…

Seems like you'd have to break quite a few relational model concepts in order to retain compatibility with SQL, no?

Ordered tuples like you say, but also duplicate tuples. And null values.

Re: PRQL: Pipelined Relational Query Language

#203

Post by WorldMaker »

pragmatic wrote: post #121
Pxtl wrote: post #102

Earlier quoted context omitted.

While there's some stuff in C#/LINQ/EF that's more verbose (left joins are often a nightmare) or not-supported, I'll always say that I prefer writing queries in EF than in SQL, at least when dealing with SQL features that are supported by EF (which is a lot of them, it's a very expressive dialect). But EF lets you start with FROM, lets you do whichever keywords you need in whichever order (instead of WHERE -> GROUP B…

The downside though is you have to grab the sql it’s generating somehow to try to optimize it, figure out what crazy cross apply madness it’s going or to figure out why it’s blowing sql servers recursion limit. I prefer to avoid linq syntax now. It’s a false economy.

EF for some time (since the "Core" reboot) has made things like "crazy cross apply" "opt-in only" and instead you get runtime exceptions for LINQ query shapes it thinks can only be done that way. Some of those crazy things early days EF silently supported still aren't even possible in today's EF, for mostly better (but sometimes rarely worse), let alone opt-in behaviors.

I think more than ever current EF generates SQL that straightforward looks like you would expect given the LINQ you've written, with only surprises being runtime exceptions before it ever tries to confuse an SQL database with a horribly deoptimized query.

Today's EF also has more tools than ever to optimize its queries. It logs query text by default in some debug contexts to visible debug consoles, and to certain telemetry providers when errors occur in certain release/Production contexts. It's easy to opt-in to such logging in even more contexts should you desire that. (Thanks to modern .NET's ubiquitous System-owned ILogger infrastructure and the modern "Generic Hosts" approach.) For advanced scenarios, intercepting specific LINQ queries and enhancing/optimizing their output based on domain knowledge of the queries or the specific databases is easier than ever with simple dependency injection.

Re: PRQL: Pipelined Relational Query Language

#204

Post by mulmen »

slt2021 wrote: post #201
aidos wrote: post #185

Earlier quoted context omitted.

> CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...

neat trick! sometimes you absolutely need to materialize your CTE, especially if it is one big computation and you refer it 4-5 times downstream.

According to the docs [1] this is not necessary:

> All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED.

[1]: https://www.postgresql.org/docs/15/sql-select.html

Re: PRQL: Pipelined Relational Query Language

#205

Post by [deleted] »

aidos wrote: post #185
mulmen wrote: post #73

Earlier quoted context omitted.

Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I al…

> CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...

[deleted]

Re: PRQL: Pipelined Relational Query Language

#206

Post by jdmichal »

jdmichal wrote: post #194
AtNightWeCode wrote: post #174

Earlier quoted context omitted.

Don't use *. It is a common source for all kinds of problems. Many query langs does not support it at all for that reason.

I'm quite aware of your advice regarding using ` ` against table. However, I'm talking about using ` ` against subqueries and CTEs where the pieces of the table have already been extracted.

This is supposed to read:

I'm quite aware of your advice regarding using `*` against tables. However, I'm talking about using `*` against subqueries and CTEs where the pieces of the table have already been extracted.

I was unable to edit this earlier due to HN being unavailable after I realized the formatter mangled it.

Re: PRQL: Pipelined Relational Query Language

#208

Post by tabtab »

Except for niches, one is not going to unseat SQL as the primary de-facto standard relational query language unless the replacement is significantly better, not just slightly. Unless a fad storm kicks in, being slightly better is rarely enough to unseat an established standard.

As far as SQL competitor candidates, my personal favorite is SMEQL. It's more composable and uses tables for many operations instead of dedicated commands and syntax, making it more uniform where a handful of idioms do all the work instead of lots of specialized stuff for each task, like what SQL does.

Re: PRQL: Pipelined Relational Query Language

#209

Post by ako »

globular-toast wrote: post #181
0cf8612b2e1e wrote: post #3

I really want this to take off and become a first party supported language for Postgres. Yes, yes, relational algebra is beautiful and all that jazz. SQL is a garbage, first pass design that should have been replaced decades ago. Even Codd has complaints about it. It is amazing what he invented, but we have learned a lot about PL design since then.

Same. I first learnt SQL 20 years ago as a teenager and even back then I remember thinking how odd and, quite literally, backwards the language was. Back then I probably thought I just didn't understand enough to see why it had to be so. Now I know there is no reason. SQL has become more like a natural language. There's no arguing against it, you have to speak it even if it doesn't make sense. But it's not a natural…

It’s optimized for reading and use: the first time you see a query written by another developer, the interesting bit is what data it returns (the select part). Once you know that, you may be interested in the how, where does that data come from? Same for functions or methods, you first see the signature, input and output structures, before you see the implementation.

Re: PRQL: Pipelined Relational Query Language

#210

Post by globular-toast »

ako wrote: post #209
globular-toast wrote: post #181

Earlier quoted context omitted.

Same. I first learnt SQL 20 years ago as a teenager and even back then I remember thinking how odd and, quite literally, backwards the language was. Back then I probably thought I just didn't understand enough to see why it had to be so. Now I know there is no reason. SQL has become more like a natural language. There's no arguing against it, you have to speak it even if it doesn't make sense. But it's not a natural…

It’s optimized for reading and use: the first time you see a query written by another developer, the interesting bit is what data it returns (the select part). Once you know that, you may be interested in the how, where does that data come from? Same for functions or methods, you first see the signature, input and output structures, before you see the implementation.

Good point, I didn't think of it like that and, oddly, I do often advocate for writing things "backwards" from highest level of abstraction to lowest. Maybe there is a good reason SQL has stuck around.
Post reply on HN