7 ms·

The only problem I have with them is that it takes my brain 30 minutes to digest the peace of SQL in front of me that uses WITH RECURSIVE clause. I do whatever

The only problem I have with them is that it takes my brain 30 minutes to digest the peace of SQL in front of me that uses WITH RECURSIVE clause. I do whatever I'm supposed to do. 3 months later, a bugfix required, I find myself at this piece of SQL that I know works but I cannot digest it for another 30 minutes. And my case is really simple. But yeah they are amazing.

pc86 8y ago
I do think the biggest pro for CTEs is the increased readability, and using recursion inhibits the readability a bit (still better than the alternative). But I'm curious how complicated the SQL you're using is that it takes half an hour to understand what it's doing.
- craigkerstiens 8y ago
  OP here, completely agreed. I have a separate post from some years back about CTEs specifically around readability. While they can make things a bit long and can be an optimization fence in Postgres the building blocks and readability in my eyes tends to win out over those things pretty often - http://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/ http://www.craigkerstiens.com/2013/11/18/best-postgres-featu...
olavk 8y ago
But can you solve the same task in a more straightforward way without CTE?
- maxdemarzi 8y ago
  Yeah, use a graph database. Most trees end up becoming graphs as changes happen over time and the CTEs won't help much anymore.
  - olavk 8y ago
    Doesn't graph databases have similar query facilities? How does shifting to a graph database avoid the complexity of recursive queries?
    - meritt 8y ago
      The primary facility in graph databases is each node explicitly contains a list of relationships it has with other nodes. The relationships are treated as first-class citizens. Neo4j has a great set of docs that introduce Graph DBs to people already familiar with RDBMSes [1] https://neo4j.com/developer/graph-db-vs-rdbms/ https://neo4j.com/developer/graph-db-vs-rdbms/
      - davidcuddeback 8y ago
        Another good resource is Designing Data-Intensive Applications [1]. Chapter 2 does a really good job explaining how different categories of databases relate to different data models, including examples of querying graph-like data models using `WITH RECURSIVE` compared to a query language for graph databases. [1] https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321/ https://www.amazon.com/Designing-Data-Intensive-Applications...
    - wooly_bully 8y ago
      They're similar in complexity, but made easier by the fact that graph databases explicitly exist to query these types of relationships. Where someone may not know how to find their root manager node in an employee table, it would be an ideal thing to query for in a graph database. There are functions like 'shortestPath' in neo4j for example.
  - ris 8y ago
    Though I would propose that in the vast majority of applications, the amount you're going to need to do graph-like operations is significantly outweighed by the amount you're going to need to do regular relational-type operations. Being able to do them "well enough" without adding yet another moving part to your system (one which holds data that probably needs to be synchronized with other data stores) is extremely valuable.
Klathmon 8y ago
People need to comment SQL more! We have a few ~200 line long queries in one of our applications. With comments one of them is 460 lines. When things get hairy, don't be afraid to explain why you needed this CTE, what it's doing from a high level, point out that little gotcha that you ran into a few times while developing the query, why you needed to do X instead of the simpler Y, etc... While there are downsides to excessive commenting (the big one is that the comments can get "out of sync" with the code and just cause confusion), when it comes to big/complicated SQL, I've found that more is better for the most part.
- craigkerstiens 8y ago
  OP here, couldn't agree more regarding commenting[1]. Far too often people don't treat their SQL queries or database the same way they do code. Giving SQL the same attention as you do code with good formatting and comments can make life much easier the next time someone else or even yourself has to come back to some query years later. [1]. http://www.craigkerstiens.com/2013/07/29/documenting-your-postgres-database/ http://www.craigkerstiens.com/2013/07/29/documenting-your-po...
- segmondy 8y ago
  You can say that again! I started a DB review team at my company. Not only queries, but Postgres allows for tables to be commented on, you can place a comment on table, columns, constraints, domain, triggers, view, pretty much anything. Initially a few folks rolled their eyes at that, but now everyone loves it. When you have thousands of tables, comments help, likewise when you have massive amount of queries. I'm also in the camp of let the DB do the work if it can do it faster, and likewise have 100-400 line queries that will turn into 5x LOC programs which take 20x time to execute. Writing tons of comments is important. Usually the query is right and when it works, no one remembers it for a year or two till business rules change. My approach to commenting such queries after modification is to delete every single comment and start afresh. From memory, when I'm done. If I can't then I have no idea what the query was doing which is very bad. Sometimes folks modify query and get a happy result. If I can document it without reference to old one, great! After that, I look at the old one to make sure that I'm not missing anything.
mistercow 8y ago
This is my problem with SQL in general. The facilities for which SQL is an API are incredible, but that API has so many weird design choices behind it that it often feels like I'm being forced to play some kind of perverse minigame in order to get the database to do what I want efficiently. And then, once I've managed to bend the query planner to my will, I often have to load it down with an absurd number comments to explain to future folk what exactly is happening – ostensibly the sort of thing that a declarative language is supposed to prevent.
- amelius 8y ago
  Perhaps someone should design a purely functional language that translates to SQL (?)
  - virtualwhys 8y ago
    See Haskell's Esqueleto, Scala's Slick and Quill, C#/F#'s LINQ to SQL + Type Providers, probably others. In the case of Haskell and Scala the aforementioned query DSLs serve as CTE generators without the performance hit (albeit non-recursive). Being able to compose complex statements based on statically typed query snippets is really, really nice -- strips out the reams of repeated boilerplate that one inevitably is forced to write with string-y SQL.
    - amelius 8y ago
      Interesting. Could these be extended to support recursion using these new recursive CTEs? Or would they miss some fundamental mechanism, such as the ability to pass parameters down the recursive queries?
      - virtualwhys 8y ago
        > Could these be extended to support recursion using these new recursive CTEs? Sure, in the end they generate (prepared) sql statements; CTEs are just another statement with an expected result type. As for supporting parameters in a recursive CTE, I don't think providing runtime values would be an issue. WITH RECURSIVE $name (n) AS ( SELECT $init UNION ALL SELECT n + $incr FROM $name WHERE n + $incr <= $limit ) SELECT n FROM $name; Could use it right now with built-in string interpolator, but not sure how this would look in DSL form since recursive CTEs can support both real and temporary tables (i.e. the DSL has to know the structure of the temporary table in order to statically determine the result type of the query).
jnordwick 8y ago
Dont worry. It takes the database 30 minutes to execute it too.
- recursive 8y ago
  There's no particular reason that a recursive CTE should be that slow. Some are, but it's not too hard to write any kind of query to go as slow as you want to make it go.

pc86 8y ago

I do think the biggest pro for CTEs is the increased readability, and using recursion inhibits the readability a bit (still better than the alternative). But I'm curious how complicated the SQL you're using is that it takes half an hour to understand what it's doing.

craigkerstiens 8y ago
OP here, completely agreed. I have a separate post from some years back about CTEs specifically around readability. While they can make things a bit long and can be an optimization fence in Postgres the building blocks and readability in my eyes tends to win out over those things pretty often - http://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/ http://www.craigkerstiens.com/2013/11/18/best-postgres-featu...

olavk 8y ago

But can you solve the same task in a more straightforward way without CTE?

maxdemarzi 8y ago
Yeah, use a graph database. Most trees end up becoming graphs as changes happen over time and the CTEs won't help much anymore.
- olavk 8y ago
  Doesn't graph databases have similar query facilities? How does shifting to a graph database avoid the complexity of recursive queries?
  - meritt 8y ago
    The primary facility in graph databases is each node explicitly contains a list of relationships it has with other nodes. The relationships are treated as first-class citizens. Neo4j has a great set of docs that introduce Graph DBs to people already familiar with RDBMSes [1] https://neo4j.com/developer/graph-db-vs-rdbms/ https://neo4j.com/developer/graph-db-vs-rdbms/
    - davidcuddeback 8y ago
      Another good resource is Designing Data-Intensive Applications [1]. Chapter 2 does a really good job explaining how different categories of databases relate to different data models, including examples of querying graph-like data models using `WITH RECURSIVE` compared to a query language for graph databases. [1] https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321/ https://www.amazon.com/Designing-Data-Intensive-Applications...
  - wooly_bully 8y ago
    They're similar in complexity, but made easier by the fact that graph databases explicitly exist to query these types of relationships. Where someone may not know how to find their root manager node in an employee table, it would be an ideal thing to query for in a graph database. There are functions like 'shortestPath' in neo4j for example.
- ris 8y ago
  Though I would propose that in the vast majority of applications, the amount you're going to need to do graph-like operations is significantly outweighed by the amount you're going to need to do regular relational-type operations. Being able to do them "well enough" without adding yet another moving part to your system (one which holds data that probably needs to be synchronized with other data stores) is extremely valuable.

Klathmon 8y ago

People need to comment SQL more! We have a few ~200 line long queries in one of our applications. With comments one of them is 460 lines. When things get hairy, don't be afraid to explain why you needed this CTE, what it's doing from a high level, point out that little gotcha that you ran into a few times while developing the query, why you needed to do X instead of the simpler Y, etc... While there are downsides to excessive commenting (the big one is that the comments can get "out of sync" with the code and just cause confusion), when it comes to big/complicated SQL, I've found that more is better for the most part.

craigkerstiens 8y ago
OP here, couldn't agree more regarding commenting[1]. Far too often people don't treat their SQL queries or database the same way they do code. Giving SQL the same attention as you do code with good formatting and comments can make life much easier the next time someone else or even yourself has to come back to some query years later. [1]. http://www.craigkerstiens.com/2013/07/29/documenting-your-postgres-database/ http://www.craigkerstiens.com/2013/07/29/documenting-your-po...
segmondy 8y ago
You can say that again! I started a DB review team at my company. Not only queries, but Postgres allows for tables to be commented on, you can place a comment on table, columns, constraints, domain, triggers, view, pretty much anything. Initially a few folks rolled their eyes at that, but now everyone loves it. When you have thousands of tables, comments help, likewise when you have massive amount of queries. I'm also in the camp of let the DB do the work if it can do it faster, and likewise have 100-400 line queries that will turn into 5x LOC programs which take 20x time to execute. Writing tons of comments is important. Usually the query is right and when it works, no one remembers it for a year or two till business rules change. My approach to commenting such queries after modification is to delete every single comment and start afresh. From memory, when I'm done. If I can't then I have no idea what the query was doing which is very bad. Sometimes folks modify query and get a happy result. If I can document it without reference to old one, great! After that, I look at the old one to make sure that I'm not missing anything.

mistercow 8y ago

This is my problem with SQL in general. The facilities for which SQL is an API are incredible, but that API has so many weird design choices behind it that it often feels like I'm being forced to play some kind of perverse minigame in order to get the database to do what I want efficiently. And then, once I've managed to bend the query planner to my will, I often have to load it down with an absurd number comments to explain to future folk what exactly is happening – ostensibly the sort of thing that a declarative language is supposed to prevent.

amelius 8y ago
Perhaps someone should design a purely functional language that translates to SQL (?)
- virtualwhys 8y ago
  See Haskell's Esqueleto, Scala's Slick and Quill, C#/F#'s LINQ to SQL + Type Providers, probably others. In the case of Haskell and Scala the aforementioned query DSLs serve as CTE generators without the performance hit (albeit non-recursive). Being able to compose complex statements based on statically typed query snippets is really, really nice -- strips out the reams of repeated boilerplate that one inevitably is forced to write with string-y SQL.
  - amelius 8y ago
    Interesting. Could these be extended to support recursion using these new recursive CTEs? Or would they miss some fundamental mechanism, such as the ability to pass parameters down the recursive queries?
    - virtualwhys 8y ago
      > Could these be extended to support recursion using these new recursive CTEs? Sure, in the end they generate (prepared) sql statements; CTEs are just another statement with an expected result type. As for supporting parameters in a recursive CTE, I don't think providing runtime values would be an issue. WITH RECURSIVE $name (n) AS ( SELECT $init UNION ALL SELECT n + $incr FROM $name WHERE n + $incr <= $limit ) SELECT n FROM $name; Could use it right now with built-in string interpolator, but not sure how this would look in DSL form since recursive CTEs can support both real and temporary tables (i.e. the DSL has to know the structure of the temporary table in order to statically determine the result type of the query).

jnordwick 8y ago

Dont worry. It takes the database 30 minutes to execute it too.

recursive 8y ago
There's no particular reason that a recursive CTE should be that slow. Some are, but it's not too hard to write any kind of query to go as slow as you want to make it go.