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

121–130 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#121

Post by ttfkam »

CuriouslyC wrote: post #106
vendiddy wrote: post #92

Earlier quoted context omitted.

Do you have any links to a basic example on using CTEs and functions to keep SQL maintainable? I've used CTEs, but I had not tried breaking up an SQL query into functions. Didn't know that was possible! For whatever reason, I feel like I end up with a giant blob of SQL when writing SQL and it's incredibly frustrating.

You can just use chatgpt to rewrite sql with ctes, and extract functions. It's quite good at it, particularly gpt4. That being said, CTEs are a really good way to write complex queries. They let you tag bits of query with meaningful names, and each thing you tag is accessible to every CTE after it so you can build up an almost imperative data flow by just doing select transforms one after another. That way you're bui…

Agreed. It's so nice to be able to query intermediates to see what their output looks like and just check each step of the CTE during debugging. It was such an improvement over most subselects!

Re: Show HN: PRQL in PostgreSQL

#123

Post by ttfkam »

riku_iki wrote: post #111
CuriouslyC wrote: post #23

Earlier quoted context omitted.

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.

Aren't most of those wire-compatible with Postgres?

Re: Show HN: PRQL in PostgreSQL

#124

Post by ttfkam »

adrian_b wrote: post #58
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…

In my opinion SQL is the sub-optimal language. Whenever I am writing SQL I am not thinking in SQL, but I am thinking in what I consider to be the mathematical sound way, which I translate into SQL while writing. I consider thinking in SQL a much greater mental handicap than having to translate mentally into it. I would prefer to write directly in what I would consider as a good query language and have it translated a…

Can you expand on this? I have always seen SQL as a DSL for set theory. How are you seeing things "mathematically" that do not comport with set manipulation in SQL?

Re: Show HN: PRQL in PostgreSQL

#125

Post by jpgvm »

zeroimpl wrote: post #86
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…

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.

Details in sibling but I dug a bit deeper, new version used to_char, turns out that is STABLE and not IMMUTABLE so because the volatility didn't match the whole way down anymore it broke inlining.

I'm guessing switching the function that calls to_char to STABLE will fix the problem.

Re: Show HN: PRQL in PostgreSQL

#126

Post by riku_iki »

ttfkam wrote: post #123
riku_iki wrote: post #111

Earlier quoted context omitted.

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

Aren't most of those wire-compatible with Postgres?

query language likely not fully supported

Re: Show HN: PRQL in PostgreSQL

#127

Post by zeroimpl »

jpgvm wrote: post #125
zeroimpl wrote: post #86

Earlier quoted context omitted.

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.

Details in sibling but I dug a bit deeper, new version used to_char, turns out that is STABLE and not IMMUTABLE so because the volatility didn't match the whole way down anymore it broke inlining. I'm guessing switching the function that calls to_char to STABLE will fix the problem.

Makes sense. You mentioned index so I thought maybe you were were querying a table.

Would be nice if postgresql could tell you when the flags don’t match. I think anytime you deal with timestamps you can have problems since the expression may depend on the session’s time zone.

Re: Show HN: PRQL in PostgreSQL

#128

Post by gaganyaan »

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.

I like PostgreSQL quite a bit and pushed for using it by default at my company, but SQL pops up in a surprising number of places. You can use it in PowerBI, Snowflake, and pandas just to name a few. You don't always control which DB you're talking to, and it would be nice if the SQL interface that gets presented in those scenarios got an upgrade. PRQL looks like a really promising option for that.

Re: Show HN: PRQL in PostgreSQL

#129

Post by Sammi »

mt_ wrote: post #122

Is PRQL faster than the standard query model?

PRQL compiles to SQL. Why would it be faster?

The intention is rather for it to be simpler, as it uses a linear direction of data handling. SQL jumps back and forth with its order of operations and can be confusing in this way.

PRQL also has a more modern syntax that reuses more universal concepts with fewer keywords to learn. In contrast to SQL which has a unique keyword, syntax, and behavior for everything.

Re: Show HN: PRQL in PostgreSQL

#130

Post by reactivenz »

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

Earlier quoted context omitted.

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.

Snowflake has had it for years, and even then it is trivial to write as a nested SELECT.

The bigger problem, is the "over use of tools to boost developers" the real problem, is putting poor developers into the pipeline. complex analytics is SQL is just simple, and lovely. The fact other struggle is not going to be helped by pretending the "code" looks more C like, they need to learn to think like a performant machine, and then be productive.

Post reply on HN