v_internal.VS_NODE_DEPENDENCIES

[borka66]

Vertica K=1 cluster could be presented as a circle when each node has its own data, has a copy on a succeeding node and holds a copy from a preceding node.

I would like to write a query that will display this circle.

It is not always 1 -> 2 -> 3.
For example in our 9 node cluster after removals, additions and rebalances the order is 2 -> 3 -> 4 -> 5 -> 9 -> 6 -> 7 -> 1 -> 8 -> 2.

I found v_internal.VS_NODE_DEPENDENCIES view that can give the pairs of dependent nodes, but has no order to arrange them to predecessors and successors.

In 4 node cluster it is

Code: Select all

with X as (
 select d.dependency_id as id,
        n.name
   from v_internal.VS_NODE_DEPENDENCIES d
        join v_internal.VS_NODES n
     on d.node_oid = n.oid
  where d.dependency_id < (select count(*) from v_internal.VS_NODES)
)
select a.id,
       least(a.name, b.name) as node1,
       greatest(a.name, b.name) as node2
  from X a,
       X b
 where a.id = b.id
   and a.name > b.name
order by 1, 2;

Code: Select all

 id |     node1      |     node2
----+----------------+---------------
  0 | v_vdb_node0002 | v_vdb_node0003
  1 | v_vdb_node0003 | v_vdb_node0004
  2 | v_vdb_node0001 | v_vdb_node0002
  3 | v_vdb_node0001 | v_vdb_node0004
(4 rows)

I can create a circle from this information manually, but woukld like vsql to work for me
Ideas?

Thank you!

[borka66]

With a great help from the Russian Oracle community (http://www.sql.ru/forum/1164986/zapros- ... ov-v-kolco) this is a solution for a cluster with up to 10 nodes.

Code: Select all

with G as (
 select d.dependency_id as id,
        min(n.name) as node1,
        max(n.name) as node2
   from v_internal.VS_NODE_DEPENDENCIES d
        join v_internal.VS_NODES n
     on d.node_oid = n.oid
  where d.dependency_id < (select count(*) from v_internal.VS_NODES)
  group by 1
),
L as (
 select node1, node2 from G
 union
 select node2, node1 from G
),
X as (
 select n01.node1 || ' > ' ||
        n02.node1 || ' > ' ||
        n03.node1 || ' > ' ||
        n04.node1 || ' > ' ||
        n05.node1 || ' > ' ||
        n06.node1 || ' > ' ||
        n07.node1 || ' > ' ||
        n08.node1 || ' > ' ||
        n09.node1 || ' > ' ||
        n10.node1 as path
   from L n01,
        L n02,
        L n03,
        L n04,
        L n05,
        L n06,
        L n07,
        L n08,
        L n09,
        L n10
  where n01.node1 like '%node0001'
    and n02.node1 = n01.node2 and n02.node2 != n01.node1
    and n03.node1 = n02.node2 and n03.node2 != n02.node1
    and n04.node1 = n03.node2 and n04.node2 != n03.node1
    and n05.node1 = n04.node2 and n05.node2 != n04.node1
    and n06.node1 = n05.node2 and n06.node2 != n05.node1
    and n07.node1 = n06.node2 and n07.node2 != n06.node1
    and n08.node1 = n07.node2 and n08.node2 != n07.node1
    and n09.node1 = n08.node2 and n09.node2 != n08.node1
    and n10.node1 = n09.node2 and n10.node2 != n09.node1
)
select regexp_replace(path,' > v_[^_]*_node0001.*') as path
  from X;

When Vertica will support recursive queries it could be rewritten more nicely.