10 ms·
Recursive Common Table Expressions in Postgres
- skissane 8y agoIt annoys me they have made backward incompatible changes to this in Postgres 10, such that recursive CTEs that worked perfectly fine in 9.6 suddenly get rejected in 10. See in particular the function in this StackOverflow answer – https://stackoverflow.com/a/46761197/2147204 https://stackoverflow.com/a/46761197/2147204 – it works fine on 9.6, 10 rejects it with an error.
- trinitry3 8y agoNothing to do with CTEs. The queries were badly written to begin with and should have used LATERAL.
- skissane 8y agoYes, I forgot the actual issue was not CTEs directly. I agree the query could have written better (I am still getting my head around how to use LATERAL), but it worked fine in 9.6 and stopped working in 10. From a backward compatibility viewpoint, code working in one version should still work in the next (even if it isn't the best code.) Or at least, start issuing deprecation warnings one version before making it not work. Anyway, posting this to HN has triggered someone to go rewrite my code for me (thanks Ants Aasma, whoever you are), so now my Postgres 10 upgrade blocker is solved :)
- ants_a 8y agoYou're welcome. Wanted to see how hard it is to port a query over. Postgres generally tries its best to not break users code. However sometimes it is necessary for making forward progress. In this case the undocumented behavior of set returning functions within select list had some pretty funky, mostly accidental, semantics that were getting in the way of executor improvements. For example try to figure out how to explain the output of these two queries on 9.6: select generate_series(1,2), generate_series(1,4); select generate_series(1,3), generate_series(1,4); That is one example of a silent behavior change between versions that was justified that applications that are seeing that behavior are probably broken anyway. Set returning functions within case expressions had more reasonable behavior so to avoid silent breakage they were made to result in an error. Deprecation warnings are nice in theory, but in practice they would require an unreasonable amount of effort to properly implement, not seeing any warnings still wouldn't be a guarantee that your application works on new version. And it seems most users ignore deprecation warnings anyway. Besides, it's not like you can avoid making the changes, you just have slightly less schedule flexibility on when to implement them.
- whitten 8y agoCommon Table Expressions (CTEs) are sometimes referred to as WITH clauses. This article focuses on WITH RECURSIVE. They are useful ways to deal with tree oriented data.
- shrikant 8y agoFun fact: Recursive CTEs are one of those things where, if you come in relatively new to them, would make you go "who would ever want to use this, and why?!" Funner fact: Once you come across a problem to which the solution is to use a recursive CTE (handling train movement graphs in an RDBMS? yes please!), you'll find yourself having to explain your reasoning for it in a manner that will let you truly grok recursive CTEs. Funnest fact: Both the above will almost certainly happen more than once in your data-wrangling lifetime. (Or maybe that was just me...)
- ccozan 8y agoWe are using this kind of CTEs for handling BoM trees. Everyone thinks we habe some complicated software that generates this tree. Well, we just maintain the correct parent-child relation. Rest is trivial, with recursive CTE.
- esaym 8y ago>BoM trees Bill of material trees? Could you maybe elaborate more?
- ccozan 8y agoYes, Bill of Material. On our business we have sub-sub...-sub components, so we need to keep it like a tree, but manage it in a RMDBS.
- dliff 8y agoVery cool, this was actually my first real-world use case for Postgres CTE's. (BOM part of homemade ERP software I built for my last company). Worked great!
- spacemanmatt 8y agoI thought I was crazy for implementing a dependency resolver in CTE but eventually the performance and database-encapsulation were serious winners.
- Amezarak 8y ago
- dspillett 8y agoBe careful of performance when using CTEs in Postgres: unlike in other DBMSs they are optimisation fences with regard to predicate pushdown so for some queries will result in extra scans for every level of call needed. Doesn't affect all queries of course, and where is does the difference may not be significant compared to what else is going on (i.e. querying a small tree/graph structure to pull out some large/complex data), but it is something to watch out for when working with data of any appreciable size.
- Dowwie 8y agoWhat pattern ought one look for in the explained plan to identify this issue? Is this something that can likely be improved, technically speaking?
- ximeng 8y agoYes - most other DBMS don't do this. If you have a filter on a derived table that uses a CTE, Postgres will not be able to push it down to the CTE. If the CTE is expensive to calculate (a lot of rows, or joins, or complicated functions), but you don't need many rows from it in the final results, performance is likely to be very poor compared to rewriting as normal subqueries. You should be able to see this on the plan if you see an expensive CTE subquery with limited row results being used. This is unfortunate as it can reduce readability significantly. There's resistance to fixing this as it is considered a breaking change apparently, which it may be for CTEs used for data manipulation (INSERT / UPDATE / DELETE can operate using CTEs), but it shouldn't be for plain SELECTs. however no obvious plans for this to change. https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ https://blog.2ndquadrant.com/postgresql-ctes-are-optimizatio... has more.
- lr4444lr 8y agoIs this something that would be evident upon running EXPLAIN over a query?
- dspillett 8y agoExcess index (or full table) scans on the recursively referenced tables, or "wrong" index choices otherwise on those objects, where your filtering/joining clauses would otherwise allow for more efficient options with the indexes that are available. I'm not an expert on postgres (I spend most of my life in MS SQL Server's domain) so I'll not try be more detailed than that for fear of accidentally spreading/creating misinformation. Search for "postgress CTE optimisation fence explain" and you'll hopefully find some good examples as it is a commonly discussed topic once you know the right keywords to search for.
- lr4444lr 8y agoI never cease to be amazed at how well Postgres maintains decades-old ACID SQL standards while churning out these innovative advances to the spec.
- Amezarak 8y agoPostgres is great, but I don't think there is a serious rdms that doesn't have recursive ctes.
- twic 8y agoDoes Oracle have them now? There was a time when it had some janky CONNECT BY syntax of its own.
- Zardoz84 8y agoOracle >= 10 have it. CTEs are inside of SQL standard.
- marzell 8y agoI'm kinda trying to figure out why this is on HN today. Usually articles, even reposts, contain some sort of novel content or ideas. Recursive CTEs in SQL databases are well known and have been for years.
- emilsedgh 8y agoThe 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 agoI 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 agoOP 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 agoBut can you solve the same task in a more straightforward way without CTE?
- maxdemarzi 8y agoYeah, 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 agoDoesn't graph databases have similar query facilities? How does shifting to a graph database avoid the complexity of recursive queries?
- xpil 8y agoWhy use the word RECURSIVE at all? The engine should recognize the "recursiveness" of the CTE automatically, at the compile stage. Some other DB engines have had this for years.
- brlewis 8y agoI think it's for humans. Often humans looking through SQL code are doing so for performance reasons, and a recursive query is fundamentally different from a non-recursive query. It's something you might want to know while skimming, before diving deep.
- xpil 8y agoI see where you are coming from but the idea does not resonate with me. A CTE is a CTE, recursive or not. Sometimes you do not care as long as it generates correct data. I can see an analogue though: some languages differ Sub from Function although they are basically the same idea. On the other hand, Postgres seems to be the only RDBMS that makes the RECURSIVE keyword mandatory for recursive CTEs.
- deleted 8y ago[deleted]
- amelius 8y agoHow do you pass parameters to the recursive function?
- kqr 8y agoI wouldn't call it a function as much as a temporary table defined in terms of itself. It is parametrised the way all CTEs are, by giving arguments in parentheses postfix.
- shiado 8y agoI once built a Reddit clone and used recursive ctes for the comment trees. Imagine each comment has a parent comment foreign key which might be null if at top level. If you use a recursive cte you can simply start with top level comments with null parents and recursively get all of the subtrees and end up with a nice flat list of all your comments in the order you want. The other way to do this is to just fetch all the comments and assemble the tree in memory and then flatten it, which is what I believe many sites do.
- btilly 8y agoA third way to do this is described in https://en.wikipedia.org/wiki/Nested_set_model https://en.wikipedia.org/wiki/Nested_set_model. Its performance for the common case of heavy reads and few writes is better than either of the two that you describe.
- deleted 8y ago[deleted]