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

Show HN: PRQL in PostgreSQL

github.com

Post reply on HN
Print view View original

81–90 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#81

Post by CuriouslyC »

jpgvm wrote: post #61
CuriouslyC wrote: post #60

Earlier quoted context omitted.

If a function is marked stable it should not impact the query plan at all, since stable functions are essentially in-lined before planning. If logic is unstable a view is probably going to be a better abstraction than a function.

Well I thought so but even with the functions marked IMMUTABLE, which is even more stringent than STABLE the in-lining was not successful, this was apparent in the query plan. This might be a special case however as the function called another function internally (also IMMUTABLE) which was essentially memoized using an expression index. This is the index that was no-longer hit when inlining failed. If you think this…

That does sound like a bug, the planner should be inlining all of that. I would mention it on the postgres mailing list so a committer with more experience in how the planner marshals all that stuff together can weigh in.

Re: Show HN: PRQL in PostgreSQL

#82

Post by da_chicken »

jpgvm wrote: post #59
CuriouslyC wrote: post #21

Earlier quoted context omitted.

Your statement about breaking large queries apart is wrong. You can write queries with CTES to improve readability, and extract CTEs into functions that can be selected as queries get too large and unwieldly. SQL is just as composable as any other language. The thing that's lacking right now is the tooling for managing/testing/deploying database code. There are solutions out there and the supabase folks have been wor…

One issue with functions though is that they can change performance in unpredictable ways. For example a colleague of mine recently altered a function I had written that was used in multiple hot-path queries. The change he made accidentally caused the function to no longer be inline-able on PostgreSQL. Once the function couldn't be inlined then the PostgreSQL planner wasn't able to select the appropriate indices and…

I'm not sure how adding another layer of abstraction helps with that.

The problem you encoutered is that the rewritten function was either no longer table-valued, or else it was no longer deterministic (which is what that big list of rules for inlining really means). But that problem doesn't go away by adding a layer of abstraction. The need to understand relational determinism doesn't disappear. The need to understand SARGability doesn't go away. You can't really abstract the problem away.

Re: Show HN: PRQL in PostgreSQL

#83

Post by nightpool »

tyre wrote: post #57
audunw wrote: post #43

Earlier quoted context omitted.

I have a moderate amount of SQL experince, but I could not write that query at the top of my head. Maybe you misunderstand what the PRQL query is doing? Here's the SQL it generates: WITH table_0 AS ( SELECT , ROW_NUMBER() OVER ( PARTITION BY album_id ORDER BY milliseconds DESC ) AS _expr_0 FROM tracks ) SELECT FROM table_0 WHERE _expr_0 If I understand PRQL correctly, it finds the longest song for each album? A simpl…

SELECT DISTINCT ON (album_id) * FROM tracks GROUP BY album_id ORDER BY milliseconds DESC; For those unfamiliar with Postgres, DISTINCT ON takes only one row for each of the groups based on the supplied columns. So in this case, it will return only one row per album_id. Without an ORDER BY, DISTINCT ON chooses a random row (not actually, but you can’t rely on it.) Since we ORDER BY milliseconds DESC, the first row of…

that works for top-1 but breaks completely if you want to extend it to top-2

Re: Show HN: PRQL in PostgreSQL

#84

Post by 6gvONxR4sf7o »

da_chicken wrote: post #63
hans_castorp wrote: post #9

Earlier quoted context omitted.

> How long would it take you to write the SQL for that? select distinct on (album_id) * from tracks order by album_id, milliseconds desc;

Eh, `DISTICT ON` is a custom PostgreSQL extension. A standard* method would be: SELECT * FROM tracks QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1; But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using: SELECT * FROM ( SELECT * ,row_number() over (partition by album_id order by milliseconds desc) rn FROM tracks ) x WHERE…

Damn. Reading your comment, i was about to be really glad that this pain would be a thing of the past before too long. Too bad it didn't make the standard :(

Re: Show HN: PRQL in PostgreSQL

#85

Post by mikpanko »

There are very interesting improvements to SQL, which are much more ergonomic, extend functionality, and provide higher-level abstractions. Also backward compatible. PRQL and Malloy immediately come to mind but there are more. Anybody has good explanations why they struggle to get wide adoption?

Re: Show HN: PRQL in PostgreSQL

#86

Post by zeroimpl »

jpgvm wrote: post #61
CuriouslyC wrote: post #60

Earlier quoted context omitted.

If a function is marked stable it should not impact the query plan at all, since stable functions are essentially in-lined before planning. If logic is unstable a view is probably going to be a better abstraction than a function.

Well I thought so but even with the functions marked IMMUTABLE, which is even more stringent than STABLE the in-lining was not successful, this was apparent in the query plan. This might be a special case however as the function called another function internally (also IMMUTABLE) which was essentially memoized using an expression index. This is the index that was no-longer hit when inlining failed. If you think this…

An immutable function cannot query a table because the table itself isn’t immutable. If your stable/immutable flags don’t match reality, the function can’t be inlined.

Re: Show HN: PRQL in PostgreSQL

#87

Post by da_chicken »

6gvONxR4sf7o wrote: post #84
da_chicken wrote: post #63

Earlier quoted context omitted.

Eh, `DISTICT ON` is a custom PostgreSQL extension. A standard* method would be: SELECT * FROM tracks QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1; But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using: SELECT * FROM ( SELECT * ,row_number() over (partition by album_id order by milliseconds desc) rn FROM tracks ) x WHERE…

Damn. Reading your comment, i was about to be really glad that this pain would be a thing of the past before too long. Too bad it didn't make the standard :(

Well, even when it's part of the standard it will take about 6 years before your vendor chooses to implment it.

And even then, it'll be another 6 years before your application vendor finally upgrades to it.

And even then, it'll be another 6 years before the database feature is allowed to be enabled.

And even then, your reporting software won't support it.

Re: Show HN: PRQL in PostgreSQL

#88

Post by OJFord »

snthpy wrote: post #7
lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

This comes up every time PRQL makes it onto HackerNews and is a fair question: Short answer: DX Slightly longer answer: Developer productivity and experience, especially for EDA and interactively writing complex analytical queries. Most people that have tried PRQL just find it more convenient to write their analytical queries in it. PRQL compiles to SQL so it can't express anything you can't already do in SQL, but yo…

In the real world, probably something like:

    select
      album_id 
      ,first_value(id order by milliseconds desc) as longest_track_id
    from tracks
    group by album_id
I agree the PRQL's pretty nice here, but I think such a generalised example (chances you actually want to `select *`?) overstates the advantage.

Re: Show HN: PRQL in PostgreSQL

#90

Post by dvdkon »

buremba wrote: post #73
dvdkon wrote: post #48

Damn, now my bachelor's thesis will be less unique :) I'm working on a new language that compiles directly to Postgres' post-analysis structs. It's working out pretty well so far, but my chosen "universal set" (aggregation/array/subquery/... as one thing) semantics are sometimes a pain to encode.

Sounds interesting! What's the benefit of compiling directly to Postgres's internal structs over compiling to SQL?

There's little direct benefit, since the internal structs pretty closely model SQL. But having the language compiler a part of the Postgres process does help. It gives you easy access to the database's structure, so you know the type of every identifier, what columns tables have, what functions are available, etc. You can then do your own (better) error reporting and, more importantly, move away from SQL's semantics.

For example, I want to have universal broadcasting of operators on subquery results, array values, and aggregated columns. To do this, I need to know which of these the operand expressions represent, which is slow or impossible with transpilation.

Post reply on HN