Instead of a view on system tables that enables us to traverse table relationship tree, I've decided to create a simple test table:
Right from the start, while doing smoke tests, I noticed something very wrong. The function select_nodes_recursive_cte doesn't return the expected results at all.
Implementation was fine; the fact is that every change in id row doesn't mean automatically that we have the new recursion level. At all.
And after some more investigation, it appears that the only way to implement the recursion level calculation is to add level counter in CTE union queries.
We don't know which recursion depth is appropriate. And increasing the recursion threshold will only slow this function dramatically. We will still not know if we fetched all levels or if the recursive CTE ran in circles when it encountered the circular reference.
Let's try our procedural approach to this problem to see how it performs. Same test table: - 1290 records and 250 nodes - 00.783 seconds.