[HN Gopher] A Different Type of SQL Recursion with PostgreSQL
       ___________________________________________________________________
        
       A Different Type of SQL Recursion with PostgreSQL
        
       Author : vbilopav
       Score  : 69 points
       Date   : 2023-09-16 14:59 UTC (8 hours ago)
        
 (HTM) web link (github.com)
 (TXT) w3m dump (github.com)
        
       | roenxi wrote:
       | Toy examples struggle here because if things are simple it is
       | much cleverer to use multiple queries from a real programming
       | language that can be debugged. Recursion in SQL is more
       | challenging to debug than a loop in a language with flow control.
       | And if the problem isn't a toy problem, trying to solve it with
       | Postgres as the graph engine is probably going to open up a new
       | can of worms that need to be dealt with.
       | 
       | It takes a strange situation for Postgres' recursion features to
       | be a good idea. All 3 solutions would be red flags to me if I saw
       | them in a real codebase - and accompanied by of a large number of
       | other unmaintainable SQL functions. This is the fast way to turn
       | a small for loop into a week of Senior Dev billable hours.
       | Someday I'll see an exception to that rule of thumb, so far no
       | luck.
        
       | ttfkam wrote:
       | Good comparison but seems to be missing how fast they run. If
       | they're about the same, the skill set and expectations of the
       | devs tips the scales. However if one is noticeably faster than
       | the other, especially on a frequently run query, devs need to
       | adapt to understanding the faster variant despite their initial
       | comfort level.
        
       | convolvatron wrote:
       | this is where you really want to consider something like datalog
       | instead.
       | 
       | but wouldn't an abstraction (I think like 'connect by') that
       | computes the fixed point or transitive closure be really useful
       | in many of these cases and involve less cognitive overhead (even
       | if its less general)
        
       | Rizz wrote:
       | It seems like the first variant is overly complicated. When you
       | write about set theory you should be able to figure out that you
       | don't have to rely on the effects of the union operator, the
       | result you want is a simple difference with previous levels of
       | the recursive cte, or a terminal query that groups by the table
       | and shows the minimum level
        
       ___________________________________________________________________
       (page generated 2023-09-16 23:01 UTC)