I use following command to get a shortest-path query:
with recursive paths( src_id, dest_id, dist) as(
select n1,n2,1
from nodes
union
select src_id, dest_id, min(dist)
from ( select paths.src_id as src_id, nodes.n2 as dest_id, paths.dist+1 as dist
from paths, nodes
where paths.dest_id=nodes.n1
and paths.src_id<>nodes.n2
) as Temp
group by src_id, dest_id
)
select paths.src_id, paths.dest_id, min(dist)
from paths
group by 1,2;
It seems that this query goes into infinite loops and finally run out of disk space. However, I testrf every iteration seperately and found that it will converge after 3-4 iterations. I wonder where is the problem. Could anyone help with it? The attatchment is the test data.
Thank you very much:)