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

111–120 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#111

Post by riku_iki »

CuriouslyC wrote: post #23
haspok wrote: post #18

Earlier quoted context omitted.

One notable reason is being database-agnostic. Like ORM's, if you can generate SQL you can generate database-specific SQL as well. SQL is also quite verbose in places (JOINs are the most trivial example), and lack a decent amount of abstraction (CTEs are relatively low level). Updating a large set of FK'd tables can be a nightmare (this is what ORMs shine at). Finally, some modern additions are quite unreadable, Post…

Database agnosticism is so 2010. There's very little reason to choose a DB other than postgres, and if you have a reason to choose a specific niche db you're not probably not going to be migrating away from it any time soon. CTEs are a first step in structuring queries to make them decomposable. You can extract CTEs to functions and mark them stable and it's logically equivalent to the original query.

> There's very little reason to choose a DB other than postgres

Postgres is single server OLTP DB with complicated failover story, it is strong enough reason to consider some other contenders e.g. CocroachDB, SpannerDB for distributed OLTP or OLAP specialized ClickHouse, BigQuery, DuckDB.

Re: Show HN: PRQL in PostgreSQL

#112

Post by kaspermarstal »

dataspun wrote: post #108

Why is this PRQL extension for Postgres limited to Mac and Linux? What dependencies on Windows are the obstacles, and is there an expected solution in the near-term?

This extension has been developed on top of pgrx and depends on the platforms that pgrx supports. From the pgrx readme:

> Windows is not supported. It could be, but will require a bit of work with cargo-pgrx and figuring out how to compile pgrx's "cshim" static library.

Re: Show HN: PRQL in PostgreSQL

#113

Post by ttfkam »

andix wrote: post #93
ttfkam wrote: post #65

Earlier quoted context omitted.

1. Notice language is complicated for some tasks 2. Propose newer, simpler language to take care of these 3. Newer, simpler language lacks features of original language 4. Newer language adds features, making it more complicated 5. GOTO 1

That's why we stopped innovation of programming languages at C89? Why use sql at all if you can also do it in C?

Do you think SQL stopped at SQL-92?

Arrays, JSON, CTEs, window functions, booleans, MERGE, temporal tables, regular expressions, foreign tables (aka SQL/MED), etc.

SQL hasn't been sitting still, (though ORMs seem to lead folks to believe it is).

Do you currently write K&R C or modern C (C11 or C17)?

Don't get me wrong, I don't think SQL is perfect. Far from it. But PRQL isn't fixing the defects in SQL I care about. For example in DDL, NOT NULL should be the default rather than nullable. When I declare a column as a foreign key, I shouldn't have to specify the type again when the system already knows what the referenced type is. Then again, PRQL is for querying, not data definition, so it doesn't actually solve my biggest issue at all.

SQL could perhaps be more terse. I agree with a lot of folks that FROM should have been first and SELECT near last. That's pretty uselessly subjective. The argument that it's not composable falls flat for me though. Views, CTEs, foreign tables, set-returning functions, etc. are all forms of composability within SQL. When you think in terms of sets, they all fit quite well together. If you're not thinking in sets, it doesn't belong in the database in my opinion.

The underlying engines themselves have been innovating like gangbusters. Using the same wire protocol, folks can connect to a standard Postgres database, a massive CockroachDB cluster, Supabase, and all points in between without changing a client library. The same is true for MySQL, MariaDB, and PlanetScale.

Time series DB? Just use SQL. Analytics? SQL. Document-oriented data? There's even standard JSON query syntax within SQL.

It works. It doesn't generate huge amounts of CVEs like C has. (DB libraries have the SQL injection attacks, not SQL itself.) And it scales fairly seamlessly from Google Spanner all the way down to embedded SQLite.

But folks assert it's irreparably broken and needs urgent replacement. Having trouble buying it. Perhaps I just haven't seen the right replacement yet. That may indeed be the case. I just don't see PRQL being that replacement. It feels a lot more like a lateral move to me at best, and that's just too disruptive compared to potential benefit.

Re: Show HN: PRQL in PostgreSQL

#114

Post by ttfkam »

jeroen79 wrote: post #102
ttfkam wrote: post #72

Earlier quoted context omitted.

> Actually why limit yourself with SQL...? Because it's everywhere, has extensive documentation and tutorials, all database tools support it, all relational engines support it, some non-relational engines support it, all programming languages have library support for it, it can be accessed through command line tools as well as graphical interfaces, etc. You think an industry is going to give up 50 years of infrastruc…

I agree, sql is well documented and an industry standard, no need to make it more complex by adding preprocessors that do nothing but change the syntax. Just bite in and learn proper sql.

I would love relational database engines to adopt the same syntax when they support a feature. There is no compelling business case for them to do so though. Browsers converged largely in response to IE's dominance. If Firefox, Safari, and Opera had each gone their own way, no single one of them could ever get enough marketshare to get developers to care. Together they made a block significant enough that Microsoft was forced to join in or die. (Actually did effectively die once Edge was moved to the Blink engine. Now it's a satellite of a mostly standards-compliant effort.)

But databases aren't browsers. Devs just have a single target usually: whatever database engine the company decided to use.

Still it would be nice to have greater overlap to reduce the niggling details between them that only seem to exist today due to inertia rather than technical necessity.

Re: Show HN: PRQL in PostgreSQL

#115

Post by jpgvm »

CuriouslyC wrote: post #81
jpgvm wrote: post #61

Earlier quoted context omitted.

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.

I guess I got nerd-sniped. I dug a bit deeper into what was happening and the issue is the new version of the function called to_char() which turns out isn't IMMUTABLE which broke the inlining!

Re: Show HN: PRQL in PostgreSQL

#116

Post by ttfkam »

canadiantim wrote: post #70
fforflo wrote: post #51

Nice work. A few months back, I experimented with having a DSL like PRQL in Postgres, but back then, I found the language a bit cumbersome; however, it was great as an idea. IMHO, the best "data transformation" language is jq and awk is second. PRQL and EdgeQL (EdgeDB) are the most interesting ones to watch how they evolve, though. I've also written a PG extension to make jq available in Postgres [0] I believe Postgr…

Would love to see EdgeQL become adopted beyond EdgeDB. I don't like the vendor lock-in with EdgeDB, but I think they're doing great work

    "A jaw-dropping amount of effort has been spent attempting to bridge the gap between the relational paradigm of SQL and the object-oriented nature of modern programming languages. EdgeDB sidesteps this problem by modeling data in an object-relational way."
All the best to the team. I however truly hope this isn't the direction the industry moves toward. I thought we learned our lesson from MongoDB. I still believe data is best modeled in sets, not objects.

The solution isn't for databases to become more like object stores but for general purpose programming languages to be more amenable to seamless access of set-oriented data.

More stuff like this:

https://github.com/porsager/postgres

https://github.com/launchbadge/sqlx

Re: Show HN: PRQL in PostgreSQL

#117

Post by hans_castorp »

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…

The PRQL extension discussed here is for Postgres, so my solution was Postgres specific.

Since when is QUALIFY part of the SQL standard? So far I have only seen it as a proprietary feature in Terradata.

Re: Show HN: PRQL in PostgreSQL

#119

Post by ttfkam »

nightpool wrote: post #83
tyre wrote: post #57

Earlier quoted context omitted.

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

The example was asking for top 1. Yes, you are correct that you'd have to switch up to a window function to handle top-n+1.

Do you believe these cases are common enough to warrant discarding the tools and training available to SQL? Are you also certain that PRQL doesn't have corner cases where SQL is more concise and/or easier to understand?

Re: Show HN: PRQL in PostgreSQL

#120

Post by ttfkam »

setr wrote: post #101
quesera wrote: post #40

Earlier quoted context omitted.

I think the logic here is: SQL is hard and most people don't know it well. So PRQL is perhaps easier to learn. The same logic is applied to TypeScript vs JavaScript. Or C vs assembly. Or Nano vs vim. Etc etc. It's a quandary though. SQL is clearly difficult for most people to get their heads around. It does require a different way of thinking about data, and you can get by with a minimal SQL knowledge for a long time…

The hard part of SQL is thinking relationally. This obviously doesn’t do anything to affect that — if they actually thought this, they’d be making the same mistake as SQL itself “the reason business users don’t program is because it’s not english enough” The problem this is resolving, if it successfully resolves anything at all, is that the SQL language is a mess of random keywords, inconsistent syntax requirements a…

I prefer "thinking in sets". Either way, folks try to map objects and structs to their databases (I blame ORMs personally) when the analogy just isn't so. It's like translating French to English word by word and wondering why folks have trouble understanding you.

As for SQL being too much like English, making the syntax closer to a general purpose functional programming language isn't necessarily an improvement in my opinion.

Post reply on HN